Forum Discussion
Imran_Isshack
5 years agoFrequent Visitor
Dynamic Unpivoting in Power Query when new dates are added as columns
Hi All, I'm very new to Power Query and DAX. I have a data set - a daily report that I'm trying to unpivot dynamically. The anchor columns A&B will never change. However, from columns C, these ar...
edhans
5 years agoCommunity Champion
Your best bet Imran_Isshack is to use Table.UnpivotOtherColumns, like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUosTkkDUkZAbIqETcA4VidaKQmhyASq0BCKTcE0SFEyqiITJBON4CalgHjlxkVo1hlDTTNWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Item = _t, #"1/1/2021" = _t, #"1/2/2021" = _t, #"1/3/2021" = _t, #"1/4/2021" = _t, #"1/5/2021" = _t, #"1/6/2021" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Segment", "Item"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
You keep the columns you want, like Segment and Item, then all other columns are automatically unpivoted, no matter how many there are.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Imran_Isshack
5 years agoFrequent Visitor
Thanks again for your DAX syntax. I tried it and this was the output.
Am I suppose to edit any part of the DAX?
Imran