Forum Discussion
Community ChampionPower Query Convert Repeating Rows to Columns
ImkeF - Or whoever else wants to chime in. I have data which for each entry consists of a repeating set of 11 rows in a single column. So, think:
Column1
Company
Title
Description
Description2
Description3
Data1
Location
Time
Data2
Data3
Data4
Company
Title
Description
Description2
Description3
Data1
Location
Time
Data2
Data3
Data4
So, I added an Index column starting at 1 and a Custom column, Number.Mod([Index],11). My thinking is that I could then Pivot on this Custom column and not aggregate and end up with:
1 2 3 4 5
Company Title Description Description2 Description3
Company Title Description Description2 Description3
You get the idea. Unfortunately I get errors "There were too many elements in the enumeration to complete the operation".
Sadness. Any way to accomplish this?
Instead of deleting your index-column, you have to run an Integer-Divide (by 11) over it to generate a row-ID. There must always remain 1 column from the original table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1SK1YlWCsksyUkFs1xSi5OLMgtKMvPz0PlG6ALGEIHEkkRDMMsnPzkRrjEkMzcVLm8EZyH0mIBZg8AVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 11), type number), #"Integer-Divided Column" = Table.TransformColumns(#"Inserted Modulo", {{"Index", each Number.IntegerDivide(_, 11), 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", "Column1") in #"Pivoted Column"If you run into performance problems, you can use this approach instead:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1SK1YlWCsksyUkFs1xSi5OLMgtKMvPz0PlG6ALGEIHEkkRDMMsnPzkRrjEkMzcVLm8EZyH0mIBZg8AVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 11), Int64.Type}}), #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each _[Column1], type table}}, GroupKind.Local), Custom1 = Table.FromRows(#"Grouped Rows"[All]) in Custom1
3 Replies
- ImkeF
Instead of deleting your index-column, you have to run an Integer-Divide (by 11) over it to generate a row-ID. There must always remain 1 column from the original table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1SK1YlWCsksyUkFs1xSi5OLMgtKMvPz0PlG6ALGEIHEkkRDMMsnPzkRrjEkMzcVLm8EZyH0mIBZg8AVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 11), type number), #"Integer-Divided Column" = Table.TransformColumns(#"Inserted Modulo", {{"Index", each Number.IntegerDivide(_, 11), 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", "Column1") in #"Pivoted Column"If you run into performance problems, you can use this approach instead:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1SK1YlWCsksyUkFs1xSi5OLMgtKMvPz0PlG6ALGEIHEkkRDMMsnPzkRrjEkMzcVLm8EZyH0mIBZg8AVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 11), Int64.Type}}), #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each _[Column1], type table}}, GroupKind.Local), Custom1 = Table.FromRows(#"Grouped Rows"[All]) in Custom1- Greg_Deckler
ImkeF - Hooray!!! Thanks, that worked like a champ. I knew I'd seen you solve these kinds of things before but couldn't for the life of me find it or remember how you did it!. Thanks!
One other question if you have the time. What if you don't have 11 rows each time but have 11 rows, 10 rows, 9 rows but always an "end" row. So, for example, the end row that ends a record is always just "X". But each record might have 9, 10 or 11 rows.
