I’ve seen this in various places including Ken Pul’s blog. I wasn’t able to create a solution without rereading it so I need to practice this in detail.

Files: Excel & PBIX

Requirement

We have stacked pivoted data sets that have different amounts of rows:

Sets have dates for column headers to be included in the transformed layout:

Note:

• in Ken’s demo column headers are the same. In the comments he clarifies that column headers contain specific data (eg Dates)
• I added more rows to his dataset (a 4th set that includes Tequila!)

Details

Identifying details helps me focus on what the final query/table should look like.

Entities

Final query/table should have 3 entities or columns:

• Products
• Units
• Dates

Expected Row Count

Final query/table should have 60 rows. Why? Each number inside red boxes will be stacked in column Units.

Final M code

Applied Steps

Circle to the right of most steps displays comments I added in the advanced editor. Hover over them to read.

M Code

Comments make it longer but it’s easier to read the concepts/steps.

let
    Source = Excel.Workbook(File.Contents("C:\PBI local files\0027 Unpivot Stacked Sets\0027 unpivot stacked sets v4.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],

    ////INITIAL CLEANUP////
      //REMOVE EMPTY GAPS BETWEEN TABLES
    #"Filtered Rows" = Table.SelectRows(Table1_Table, each [Column1] <> null and [Column1] <> ""),

      //REMOVED CALCULATED TOTAL COLUMN
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column6"}),


    ////CREATE GROUPS////
      //INDEX/COUNTER COLUMN
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),

      //CONDITIONAL COLUMN TO FLAG START OF EACH GROUP
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "GroupCounter", each if [Column1] = "Products" then [Index] else null),

      //REMOVE INDEX/COUNTER COLUMN
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index"}),

      //FILL DOWN GROUP COUNTER NUMBER
    #"Filled Down" = Table.FillDown(#"Removed Columns1",{"GroupCounter"}),


    ////WORKING WITH NESTED TABLES
      //GROUP ROWS BASED ON GROUP NUMBER
    #"Grouped Rows" = Table.Group(#"Filled Down", {"GroupCounter"}, {{"Nested Tables", each _, type table [Column1=text, Column2=any, Column3=any, Column4=any, Column5=any, Index=number, GroupCounter=number]}}),

      //WITHIN EACH NESTED TABLE REMOVE COLUMN GROUPCOUNTER
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Remove Group Counter", each Table.RemoveColumns([Nested Tables],"GroupCounter")),

      //PROMOTE COLUMN HEADERS FOR EACH NESTED TABLE
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Promote each 1st row to header", each Table.PromoteHeaders([Remove Group Counter],[PromoteAllScalars=true])),

      //REMOVE OTHER COLUMNS AS WE ONLY NEED THE FINAL NESTED TABLE
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Promote each 1st row to header"}),

      //ADD A COLUMN TO UNPIVOT EACH NESTED TABLE
    #"Added Custom2" = Table.AddColumn(#"Removed Other Columns", "Unpivot Nested Tables", each Table.UnpivotOtherColumns([Promote each 1st row to header],{"Products"},"Date","Units")),

      //REMOVE PREVIOUS UNPIVOTED NESTED TABLE
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Unpivot Nested Tables"}),

      //EXPAND THE NESTED TABLES INTO A FINAL SINGLE TABLE
    #"Expanded Unpivot Nested Tables" = Table.ExpandTableColumn(#"Removed Other Columns1", "Unpivot Nested Tables", {"Products", "Date", "Units"}, {"Products", "Date", "Units"}),

    ////FINAL CLEANUP
    #"Added Custom3" = Table.AddColumn(#"Expanded Unpivot Nested Tables", "DateFixed", each Date.From(Text.Start([Date],10))),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"DateFixed", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Units", Int64.Type}, {"Products", type text}})
in
    #"Changed Type"

Solution

Initial steps were easy to remember. Later on I forgot some steps in the ‘Working with nested tables’ stage. Afterwards, I added comments in the M code. I included M code for several key steps below.

Four Stages

1. Initial cleanup
2. Create groups
3. Working with nested tables
4. Final cleanup

Initial Cleanup

Steps Source and Navigation are system generated. Initial cleanup steps are:

• Filtered Rows: removed null values (blanks between sets)
• Removed Columns: removed Total column

Create Groups

I remembered Ken’s steps that put each row into a group (reusable concept!)

• Added Index: simple counter column adds ID to each row
• Added Conditional Column: only keep Index when Column1 = “Products”

#"Added Conditional Column" = Table.AddColumn(#"Added Index", "GroupCounter", each if [Column1] = "Products" then [Index] else null)

• Removed Columns1: remove Added Index as it’s no longer required
• Filled Down: all rows now have a Group Counter number

Working with Nested Tables

I struggled with nested table concepts & syntax. I’m glad I practiced this!

• Group Rows: take individual rows and create 4 groups

#"Grouped Rows" = Table.Group(#"Filled Down", {"GroupCounter"}, {{"Nested Tables", each _, type table [Column1=text, Column2=any, Column3=any, Column4=any, Column5=any, Index=number, GroupCounter=number]}})

• Added Custom: column added to remove GroupCounter (no longer need)

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Remove Group Counter", each Table.RemoveColumns([Nested Tables],"GroupCounter"))

• Added Custom1: column added to promote headers in each nested table

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Promote each 1st row to header", each Table.PromoteHeaders([Remove Group Counter],[PromoteAllScalars=true]))

• Removed Other Columns: keep newest column only

• Added Custom2: column added to unpivot each nested table!

#"Added Custom2" = Table.AddColumn(#"Removed Other Columns", "Unpivot Nested Tables", each Table.UnpivotOtherColumns([Promote each 1st row to header],{"Products"},"Date","Units"))

• Removed Other Columns1: once again, keep newest column only

• Expanded Unpivot Nested Tables: Expand nested tables out into rows!

Tricky concepts & syntax here. Download pbix file to see how several steps create a new column/step to change the nested tables in previous step.

Final Cleanup

I forgot some final cleanup steps so I went back into Power Query to add these:

• Added Custom3: added a column to convert date values to date datatype. Note: syntax is very different depending on whether it’s done before or after expanding nested tables into rows.

= Table.AddColumn(#"Expanded Unpivot Nested Tables", "DateFixed", each Date.From(Text.Start([Date],10)))

• RemovedColumns2: remove original Date column
• RenamedColumns: rename DateFixed to Date
• ChangedType: define datatypes for the three columns

In the top left I clicked “Close & Apply” to send query to data model as a table:

I didn’t add comments as the last steps are easy. Converting date values to date datatype is easier after nested tables have been expanded into rows.

M Syntax Help

For M syntax help in this post I referenced Microsoft’s Power Query M function reference several times!

Recap

I saw Ken’s post last year but didn’t it carefully. I learned so much recreating his solution. Thanks Ken! Ken blogs at excelguru.ca and has a training site here.