Conditional unpivoting date columns in power query

[Anonymous]

I have multiple Excel files containing unit forecasts for different weeks ahead. For each week, a new Excel file containing new generated weekly forecasts (different number of week column compare to previous one) will be produced. I wish to do conditional unpivoting to those date columns. The "Unpivot Columns" or "Unpivot other columns" do not work as the number of date columns in the first sample file is different with the subsequent files. 

First file:

(17 week columns)

 Second file:

(could be 17 or more week columns )

Is there anyway to do conditional unpivoting? something like Table.Unpivot (if column=date).

I am new in Power Bi. Thank you in advance.

[Vijay_A_Verma]

Use Unvpivot other columns in your sample file. This means selecting those columns which are fixed say Category, regions etc and then right click - Unpivot other columns. This would take care of other columns's variability. 

[Anonymous]

I have tried this, however, it only unpivots the columns with the same attributes names. For example, in the first file, I had highlighted the columns other than date columns and selected 'unpivot other columns'. After I uploaded the second file, the power query only unpivots until 3/21/2022 column, the rest starting from 3/28/2022 were ignored.

[Vijay_A_Verma]

Use this as your Unpivot command (replace #"Changed Type" with your previous step)

= Table.Unpivot(#"Changed Type", List.RemoveNulls(List.Transform(Table.ColumnNames(Source),(i)=> try Text.From(Date.FromText(i)) otherwise null)), "Attribute", "Value")

[Anonymous]

Unfortunately, it comes with this error

[Vijay_A_Verma]

Can you give me your Excel file with 2-3 lines of data? You can change the data if the data is confidential.

Please upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.

[Anonymous]

https://drive.google.com/drive/folders/1fkjomtnrIDO0AFYm_X081mkRTgDvYbPn?usp=sharing

Here is the dataset. I had to manipulate the data since it is confidential. The column names remain the same, only the row data changed. Basically, I need to create a dashboard to calculate the changes of forecasted unit between the same dates for different files generated every week. Really appreciate if you can help. Thank you.

[Vijay_A_Verma · Accepted solution]

This works perfectly well. Download from here...

https://1drv.ms/x/s!Akd5y6ruJhvhuVI8N1IqO6tf0gZK?e=BSor7m