Transform 1 column of data into 5

S

shellz

New member
Joined
May 26, 2016
Messages
10
Reaction score
0
Points
0
Excel Version(s)
2016
Reading in an XML file and all the data appears in one column. I've tried several different options but none appear to work.

Code
Color
Percent
Cost
Ext
1234
Red
2.25
14.19
55.96
5678
Yellow
15.5
1.64
25.17
9876


Need to change it to be 5 columns
Code Color Percent Cost Ext
1234 Red 2.25 14.19 55.96
5678 Yellow 15.5 1.64 25.17
 
This requires some advanced pivoting:
1. As a basis you add 2 temporary Index columns starting with 0.
2. From one Index column you calculate the modulo 5 (so you get 0,1,2,3,4,0,1,2,3,4 etc.) and convert these to text to become temporary column headers when pivoting.
3. Integer-Divide the other Index column by 5 (so you get 0,0,0,0,0,1,1,1,1,1 etc.). This will make your data be split on rows with 5 fields when pivoting.
4. Now pivot on the column with the index column with 0,1,2,3,4 etc. Use Advanced option "Don't aggregrate".
5. Remove the other Index column.
6. Move first row to headers.
7. Adjust data types.


Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Text.From(Number.Mod(_, 5)), type text}}),
    #"Added Index1" = Table.AddIndexColumn(#"Calculated Modulo", "Index.1", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index.1", each Number.IntegerDivide(_, 5), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Index]), "Index", "Column1"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code", Int64.Type}, {" Color", type text}, {" Percent", type number}, {" Cost", type number}, {" Ext", type number}})
in
    #"Changed Type1"
 
MarcelBeug -
That's pretty slick with the added Integer Divided Column... I've learned to use the modulo / pivot, but then you have to use the Fill Up function and remove all the extra rows. Your method eliminates that. I'll have to tuck this away in my little toolbox.:loco:
 
Thank you very much!:) That worked perfectly. I had gotten as far as adding the index but didn't know about the Integer Divided Column.
 
M Is for (Data) Monkey 2nd Edition!
 
Hi Marcel,
I have been using this technique for years :)
But...... if I want to make my code unreadable then sometimes i use this below ;-) (joke of course)
Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    Result = Table.PromoteHeaders(Table.FromColumns(List.Transform(Table.Partition(#"Added Index", "Index", 5, each Number.Mod(_, 5)), each _[Data])))
in
    Result

Regards :wave:
 
Hi Bill,

Great! I was still looking for useful examples with Table.Partition, so I'll take a closer look at your solution (and make it readable for myself :smile: ).

Cheers
 
Hi Bill,

Playing around with Table.Partition, it looks like an interesting alternative for Table.Group.

1 Remark: Table.Partition will do the modulo calculation internally, so there is no need to specify it yourself. The Result step can be shortened to:

Code: 
Result = Table.PromoteHeaders(Table.FromColumns(List.Transform(Table.Partition(#"Added Index", "Index", 5, each _), each _[Data])))

Regards,
Marcel
 
Those are great as well... the nice thing with Marcel's first solution above is that it can be done via the UI.
 
You must log in or register to reply here.
Back
Top