Dynamic Unpivoting in Power Query when new dates are added as columns

[Imran_Isshack]

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 are daily reports a day (New Date will be added as Column Headers Plus the data (values) for those dates) will be added every time I add new data. How do I dynamically capture these new dates that will be added daily? I found this DAX online, but it's giving an error where there is the word "attribute". Can someone please help me to solve this?

let Source = Excel.CurrentWorkbook(){[Name=„ProductPlan“]}[Content],

Cols = Table.ColumnNames(Source),

ColsUnPivot=List.Skip(Cols, 1),

Unpivot = Table.Unpivot(Source,ColsUnPivot,„Attribute“,„Value“)

in

Unpivot

Imran

[edhans]

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]

@edhans 

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

[edhans]

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.

[Imran_Isshack]

@ Super User III,

Thanks very much. I'll test it and let you know how it goes.

Imran