Power Query Convert Repeating Rows to Columns

[Greg_Deckler]

@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?

[ImkeF · Accepted solution]

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.

[Anonymous]

Great topic, even greater answer. Thank you guys.