Forum Discussion
UnPivot Monthly Data
I am familiar with 365 Get & Transform. However, I am still feeling my way around a few items that I can't seem to wrap my head around and need some guidance. In the below scenario how could I unpivot this data set so it extracts the first 3 characters from each column header (so it creates a month column from it and removes it from the field name, hence extract). Then instead of 36 columns of data it refreshes to only 3 columns. See example attached
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
4 Replies
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
