Forum Discussion

var-anaz's avatar
var-anaz
New Member
3 years ago
Solved

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

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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's avatar
    BA_Pete
    3 years ago

     

    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

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      var-anaz
      New Member
      3 years ago

      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's avatar
        BA_Pete
        Icon for Super User rankSuper User
        3 years ago

         

        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