Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@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?
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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.
Great topic, even greater answer. Thank you guys.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |