Forum Discussion
Transpose every n rows into column
Hi there,
I have a data source that appends my SQL by 4 rows every day. How to transpose every 4 rows into columns?
| A | 1 | 2 |
| B | 3 | 4 |
| C | 5 | 6 |
| D | 7 | 8 |
| A | 9 | 10 |
| B | 11 | 12 |
| C | 13 | 14 |
| D | 15 | 16 |
My expected result would be
| A | B | C | D |
| 1 | 3 | 5 | 7 |
| 2 | 4 | 6 | 8 |
| 9 | 11 | 13 | 15 |
| 10 | 12 | 14 | 16 |
Here is what i have been doing
=Table.FromRows(List.Split(data[column1],4))
However, I can only get A B C D rows repeated for 2 rows. Any way to get the rest data inside?
Really appreciate your help
Thanks
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYy5EcAwDMN2Ye0idJyvzLOFzvuvEVKFCtyRBRCBGw0UHbMFHq1VjHyv1ib2fJ/WIc589i67S4l0h71UusRRMt2iYvMH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Columns",{{"Column1", Order.Ascending}})), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 4), type number}}), #"Pivoted Column" = Table.Pivot(#"Calculated Modulo", List.Distinct(#"Calculated Modulo"[Column1]), "Column1", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns1"Pat
4 Replies
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYy5EcAwDMN2Ye0idJyvzLOFzvuvEVKFCtyRBRCBGw0UHbMFHq1VjHyv1ib2fJ/WIc589i67S4l0h71UusRRMt2iYvMH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Columns",{{"Column1", Order.Ascending}})), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 4), type number}}), #"Pivoted Column" = Table.Pivot(#"Calculated Modulo", List.Distinct(#"Calculated Modulo"[Column1]), "Column1", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns1"Pat
Hi,
is it possible to tweak to code for other datasets with different "n" values. I tried altering the dataset to see if it works but unfortunately it does not work.
Hi Sir,
Do appreaciate your help , maybe i am just stupid. It doesnt work on my case. I am stuck with it for 3 days. If you really dont mind, would you help me my case？ i redo all the step, but what i got is error while i tried to pivot it back with value. (2nd last step)I would be very happy if you help me with it. Thanks
In my case, i will have new 8 rows every day
