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
var-anaz
New Member

Handling nested tables/transpose every nth row

I have a dataset with nested tables like the example below.

 

Date10.01.202211.01.202212.01.202213.01.202214.01.202215.01.202216.01.2022
A450233137255285420329
B87113225408440467326
C1471091751587655357
D4863989726225293444
Date17.01.202218.01.202219.01.202220.01.202221.01.202222.01.202223.01.2022
A9028116640787387478
B47131141426029235671
C13213036091260410325
D35427947966294229383

 

How do I handle such tables in power query. I have been trying to transpose every n rows for the desired output, but to no avail. Would appreciate your help. Thanks

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @var-anaz ,

 

Assuming that you always have the same number of rows in each table, then you can do this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZE7bgMxDETvotowJJISxdKOb7HYIkVu4PvDmtlEIJBmoNE+8TN7HOX1/f4pt9Lqvba7VBGYlo1ko9lYNj2bsc15O8pjXVmvS0WVVRzn3qETaoKvKkH+uc7TOQhwESJ1Qg2gDSc+iH9xFvI1oN45EHgfS9hIu5N+4f3EtQaI4CxDOBE0lH3son/j8bzdzCaSkZyi5BQlpyj6L55gOrMxvMF1/S8FpZrPHY45QG1Qa8YFWCCEm6LAQnY4ev07Zkww2n5j7Yq+73S0s6IHO0E5kQSvJTiSlvP8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    splitTable = Table.Split(Source, 5),
    pivotNestedTables = List.Transform(splitTable, each Table.UnpivotOtherColumns(Table.PromoteHeaders(_), {"Date"}, "Attrib", "Value")),
    convertListToTable = Table.FromList(pivotNestedTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandNestedTables = Table.ExpandTableColumn(convertListToTable, "Column1", {"Date", "Attrib", "Value"}, {"Date", "Attrib", "Value"})
in
    expandNestedTables

 

Summary:

1) Split the table by the number of rows in each.

2) Promote headers and unpivot nested tables.

3) Convert list to table and expand back out again.

 

Output:

BA_Pete_0-1666103638420.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

 

You'll need to manually add the steps in the Advanced Editor in PQ.

For the source section that you've provided, the updated code would look like this:

let
    Source = Excel.Workbook(File.Contents("Path\Data.xlsx"), null, true),
    Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
    DeleteColumns = Table.Select.Columns("Sheet",{"Column5", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Columns23"}),

    splitTable = Table.Split(DeleteColumns, 5),
    pivotNestedTables = List.Transform(splitTable, each Table.UnpivotOtherColumns(Table.PromoteHeaders(_), {"Date"}, "Attrib", "Value")),
    convertListToTable = Table.FromList(pivotNestedTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandNestedTables = Table.ExpandTableColumn(convertListToTable, "Column1", {"Date", "Attrib", "Value"}, {"Date", "Attrib", "Value"})
in
    expandNestedTables

 

After pasting my steps to the bottom of your source, the only change I've made is here:

 

BA_Pete_2-1666163370072.png

 

Power Query uses the name of the previous step in the next one so the M compiler knows in what order to perform the steps.

 

And you'll also need to adjust the number of rows in each table segment here:

 

BA_Pete_3-1666163398478.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
BA_Pete
Super User
Super User

Hi @var-anaz ,

 

Assuming that you always have the same number of rows in each table, then you can do this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZE7bgMxDETvotowJJISxdKOb7HYIkVu4PvDmtlEIJBmoNE+8TN7HOX1/f4pt9Lqvba7VBGYlo1ko9lYNj2bsc15O8pjXVmvS0WVVRzn3qETaoKvKkH+uc7TOQhwESJ1Qg2gDSc+iH9xFvI1oN45EHgfS9hIu5N+4f3EtQaI4CxDOBE0lH3son/j8bzdzCaSkZyi5BQlpyj6L55gOrMxvMF1/S8FpZrPHY45QG1Qa8YFWCCEm6LAQnY4ev07Zkww2n5j7Yq+73S0s6IHO0E5kQSvJTiSlvP8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    splitTable = Table.Split(Source, 5),
    pivotNestedTables = List.Transform(splitTable, each Table.UnpivotOtherColumns(Table.PromoteHeaders(_), {"Date"}, "Attrib", "Value")),
    convertListToTable = Table.FromList(pivotNestedTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandNestedTables = Table.ExpandTableColumn(convertListToTable, "Column1", {"Date", "Attrib", "Value"}, {"Date", "Attrib", "Value"})
in
    expandNestedTables

 

Summary:

1) Split the table by the number of rows in each.

2) Promote headers and unpivot nested tables.

3) Convert list to table and expand back out again.

 

Output:

BA_Pete_0-1666103638420.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi @BA_Pete 

 

Thank you for the solution. 
1.) I have a few Datasets in this form, where the number of rows "n" varies from 5-8. To include the other datasets with different n's, I'll have to tweak the "splitTable" step to include the other n values right?

2.) My source comes from different Excel Workbooks with different Sheets.
Your code works like a gem for the json text that you have used but however doesn't work from my source.
 My Code looks like this:

let
Source = Excel.Workbook(File.Contents("Path\Data.xlsx"), null, true),
Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
DeleteColumns = Table.Select.Columns("Sheet",{"Column5", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Columns23"})
in
DeleteColumns

I have to perform these basic steps to get the result I have posed above.
How do I integrate your Logic/Code to achieve my desired result? I am not getting the desired result if I just copy the three steps you mentioned. I am new to PQ and would really appreciate your help.
Thank you.

var-anaz

 

You'll need to manually add the steps in the Advanced Editor in PQ.

For the source section that you've provided, the updated code would look like this:

let
    Source = Excel.Workbook(File.Contents("Path\Data.xlsx"), null, true),
    Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
    DeleteColumns = Table.Select.Columns("Sheet",{"Column5", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Columns23"}),

    splitTable = Table.Split(DeleteColumns, 5),
    pivotNestedTables = List.Transform(splitTable, each Table.UnpivotOtherColumns(Table.PromoteHeaders(_), {"Date"}, "Attrib", "Value")),
    convertListToTable = Table.FromList(pivotNestedTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandNestedTables = Table.ExpandTableColumn(convertListToTable, "Column1", {"Date", "Attrib", "Value"}, {"Date", "Attrib", "Value"})
in
    expandNestedTables

 

After pasting my steps to the bottom of your source, the only change I've made is here:

 

BA_Pete_2-1666163370072.png

 

Power Query uses the name of the previous step in the next one so the M compiler knows in what order to perform the steps.

 

And you'll also need to adjust the number of rows in each table segment here:

 

BA_Pete_3-1666163398478.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thank you for the assistance. 
I had a few issues with the "pivotNestedTables" step, so I had to break it down to solve the problem. The solution works now.
Thanks & Cheers! 🙂

 

 

Yeah, I kinda snuck a 'Table.PromoteHeaders(_)' in there too. Sorry 🙂

 

Glad it's worked for you though.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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