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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors