Handling nested tables/transpose every nth row

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

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