Forum Discussion
Unpivot or unstack columns dynamically
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.
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"
3 Replies
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"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.
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.