Transpose every n rows into column

[dannytan111289]

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

[dannytan111289]

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)

 https://docs.google.com/spreadsheets/d/137Wog7G70NKxxYkfJ1fJTuMRUSvopTBX/edit?usp=sharing&ouid=10457...

I would be very happy if you help me with it. Thanks

[dannytan111289]

In my case, i will have new 8 rows every day

[mahoneypat · Accepted solution]

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

[var-anaz]

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.