Handling nested tables/transpose every nth row

[var-anaz]

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

[BA_Pete · Accepted solution]

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:

Pete

[BA_Pete · Accepted solution]

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:

Pete

[var-anaz]

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

[BA_Pete · Accepted solution]

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:

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

[var-anaz]

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! 🙂

[BA_Pete]

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

Glad it's worked for you though.

Pete