Forum Discussion
UnPivot Monthly Data
Forgot to sort resulty by months. You may add custom column as = "1 " & [Month], change its type to Date, sort by this column and remove it after that
I also meant to say I created a way of doing this via Macro but would like to see if there is a way to do this via Power Query/Get & Transform whatever the name for it may be this month.
Forgot to sort resulty by months. You may add custom column as = "1 " & [Month], change its type to Date, sort by this column and remove it after that
SergeiBaklanThanks again for your help on this. I couldn't have done it without you.
Without small intermediate steps:
- Let name source as Range and query it
- Unpivot other columns but first 5
- split first unpivoted column on month and name
Group first 6 columns
- Reference this query, name new one Headers
- keep only last column and in it only first row
- expand it and convert to list
- reference first query, let name it Result
- create custom column extracting from last column with table only list of values
- extract values from list with any delimiter
- split result on columns using this delimiter and substitute column names list by Headers list
- remove unused columns and load result to Excel sheet