Forum Discussion
Handling nested tables/transpose every nth row
I have a dataset with nested tables like the example below.
| Date | 10.01.2022 | 11.01.2022 | 12.01.2022 | 13.01.2022 | 14.01.2022 | 15.01.2022 | 16.01.2022 |
| A | 450 | 233 | 137 | 255 | 285 | 420 | 329 |
| B | 87 | 113 | 225 | 408 | 440 | 467 | 326 |
| C | 147 | 109 | 175 | 158 | 76 | 55 | 357 |
| D | 486 | 398 | 97 | 262 | 252 | 93 | 444 |
| Date | 17.01.2022 | 18.01.2022 | 19.01.2022 | 20.01.2022 | 21.01.2022 | 22.01.2022 | 23.01.2022 |
| A | 90 | 281 | 166 | 407 | 87 | 387 | 478 |
| B | 471 | 311 | 414 | 260 | 292 | 356 | 71 |
| C | 132 | 130 | 360 | 91 | 260 | 410 | 325 |
| D | 354 | 279 | 479 | 66 | 294 | 229 | 383 |
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
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 expandNestedTablesSummary:
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:
Pete
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 expandNestedTablesAfter pasting my steps to the bottom of your source, the only change I've made is here:
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:
Pete
5 Replies
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 expandNestedTablesSummary:
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:
Pete
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-anazYou'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 expandNestedTablesAfter pasting my steps to the bottom of your source, the only change I've made is here:
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:
Pete
