Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
dannytan111289
Frequent Visitor

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?

A12
B34
C56
D78
A910
B1112
C1314
D1516

 

My expected result would be

ABCD
1357
2468
9111315
10121416

 

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
dannytan111289
Frequent Visitor

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

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

mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors