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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors