Forum Discussion
Compile source with multiple columns based on single column
I have been struggling with an issue. I have a source that returns a single column with multiple rows. The first 6 rows are meant to be column headings, and then the subsequent rows need fall under the coulns in the same sequence. (Not sure if that makes sense).
Eample of source:
| Head 1 |
| Head 2 |
| Head 3 |
| Head 4 |
| Val 1 |
| Val 2 |
| Val 3 |
| Val 4 |
| Val 5 |
| Val 6 |
| etc |
What I am trying to have:
| Head 1 | Head 2 | Head 3 | Head 4 |
| Val 1 | Val 2 | Val 3 | Val 4 |
| Val 5 | Val 6 | etc | |
I was thinking of finding a way to add a column to the source that just repeats the headings ovver and over. I can then pivot the table and use that coulm as attribute and the original column as value? (just haven't been able to figure out how to loop the firts couple of records containing the headings.
I found a solution
https://prathy.com/2018/07/list-repeat-in-powerquery-to-duplicate-rows/
let
Source = My example table,
#”Added Index” = Table.AddIndexColumn(Source, “Index”, 0, 1),
#”Inserted Modulo” = Table.AddColumn(#”Added Index”, “Modulo”, each Number.Mod([Index], 4), type number),
#”Integer-Divided Column” = Table.TransformColumns(#”Inserted Modulo”, {{“Index”, each Number.IntegerDivide(_, 4), Int64.Type}}),
#”Pivoted Column” = Table.Pivot(Table.TransformColumnTypes(#”Integer-Divided Column”, {{“Modulo”, type text}}, “en-GB”), List.Distinct(Table.TransformColumnTypes(#”Integer-Divided Column”, {{“Modulo”, type text}}, “en-GB”)[Modulo]), “Modulo”, “Column 1″),
#”Removed Columns” = Table.RemoveColumns(#”Pivoted Column”,{“Index”})
in
#”Removed Columns”
2 Replies
ImkeF , can you help of this
I found a solution
https://prathy.com/2018/07/list-repeat-in-powerquery-to-duplicate-rows/
let
Source = My example table,
#”Added Index” = Table.AddIndexColumn(Source, “Index”, 0, 1),
#”Inserted Modulo” = Table.AddColumn(#”Added Index”, “Modulo”, each Number.Mod([Index], 4), type number),
#”Integer-Divided Column” = Table.TransformColumns(#”Inserted Modulo”, {{“Index”, each Number.IntegerDivide(_, 4), Int64.Type}}),
#”Pivoted Column” = Table.Pivot(Table.TransformColumnTypes(#”Integer-Divided Column”, {{“Modulo”, type text}}, “en-GB”), List.Distinct(Table.TransformColumnTypes(#”Integer-Divided Column”, {{“Modulo”, type text}}, “en-GB”)[Modulo]), “Modulo”, “Column 1″),
#”Removed Columns” = Table.RemoveColumns(#”Pivoted Column”,{“Index”})
in
#”Removed Columns”
