Forum Discussion
Dynamic Unpivoting in Power Query when new dates are added as columns
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.
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
- edhans5 years ago
Community Champion
It isn't DAX, that is Power Query M code. the only change would be to rename columns, convert those dates and values to actual date types and numerical types.
The point is, you don't need to dynamically figure out the new columns and unpivot those. You just say "I want to keep columns X, Y, and Z, and all other columns unpivot." Table.UnpivotOtherColumns does this.
If you need more help, give us some sample data and expected output via the info below.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.