Unpivot or unstack columns dynamically

[Anonymous]

I get data from outkook emails which are stacked in power query where each day's emails have tables with different headers.

Currently data is like below where first 4 rows represent table 1 from day 1 and last 4 rows is from 2nd table from day 2 and so on.  

Day 1	Value A	Value B	Value C
A	1	2	3
B	4	5	6
C	7	8	9
Day 2	Value A	Value C	Value D
A	10	11	12
B	14	15	16
C	17	18	19

I want this table to unpivot link below:

Day	Category	Type	Value
Day 1	A	Value A	1
Day 1	A	Value B	2
Day 1	A	Value C	3
Day 1	B	Value A	4
Day 1	B	Value B	5
Day 1	B	Value C	6
Day 1	C	Value A	7
Day 1	C	Value B	8
Day 1	C	Value C	9
Day 2	A	Value A	10
Day 2	A	Value C	11
Day 2	A	Value D	12
Day 2	B	Value A	14
Day 2	B	Value C	15
Day 2	B	Value D	16
Day 2	C	Value A	17
Day 2	C	Value C	18
Day 2	C	Value D	19

Thanks in adcance for help.

Regards.

[Anonymous]

Thanks @ronrsnfld . This worked. I need to learn this part.

 #"Grouped Rows" = Table.Group(#"Filled Down", {"Day"}, {
        {"UNPivot", (t)=>
            let 
                #"Promote Headers" = Table.PromoteHeaders(t),
                #"Removed Columns" = Table.RemoveColumns(#"Promote Headers", List.Last(Table.ColumnNames(#"Promote Headers"))),
                #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{List.First(Table.ColumnNames(#"Removed Columns")),"Category"}}),
                #"Unpivot" = Table.UnpivotOtherColumns(#"Renamed Columns",{"Category"},"Type","Value"),
                #"Value to Number" = Table.TransformColumns(#"Unpivot",{"Value", each Number.From(_)})
            in 
                #"Value to Number",
            type table[Category=text, Type=text, Value=number]}
        }),

 Regards.

[ronrsnfld]

If you set up a data sample with just a single day, so you don't have to put those steps within a Table.Group aggregaion, those are the steps (after the #"Filled Down") that would unpivot the single day. It may be easier to follow that way.

[ronrsnfld · Accepted solution]

Try this:

It works on your sample

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY0rDoAwEESv0qxGMOUvoT0DpqmowGER3J6diq1BvGYy3cxLSWJ5HaSTs9zP5XZLh6UguUvCH955ZagNL0ZlUubaBE2Lsipbbbjuf9aDpdjWez50wJsANIAKNAcoAS1QTf4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),

//Add column to group by dates
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Day", each if Text.StartsWith([Column1],"Day") then [Column1] else null, type nullable text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Day"}),

//Unpivot each subgroup
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Day"}, {
        {"UNPivot", (t)=>
            let 
                #"Promote Headers" = Table.PromoteHeaders(t),
                #"Removed Columns" = Table.RemoveColumns(#"Promote Headers", List.Last(Table.ColumnNames(#"Promote Headers"))),
                #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{List.First(Table.ColumnNames(#"Removed Columns")),"Category"}}),
                #"Unpivot" = Table.UnpivotOtherColumns(#"Renamed Columns",{"Category"},"Type","Value"),
                #"Value to Number" = Table.TransformColumns(#"Unpivot",{"Value", each Number.From(_)})
            in 
                #"Value to Number",
            type table[Category=text, Type=text, Value=number]}
        }),
    
    #"Expanded UNPivot" = Table.ExpandTableColumn(#"Grouped Rows", "UNPivot", {"Category", "Type", "Value"})
 
in
    #"Expanded UNPivot"