Forum Discussion
Conditional unpivoting date columns in power query
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.
This works perfectly well. Download from here...
7 Replies
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.
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.
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")