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 ...
This works perfectly well. Download from here...
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")Unfortunately, it comes with this error