SOLVED

UnPivot Monthly Data

Brass Contributor

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

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.

@heylookitsme 

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

image.png

Group first 6 columns

image.png

- Reference this query, name new one Headers

- keep only last column and in it only first row

- expand it and convert to list

image.png

- 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

image.png

 

best response confirmed by heylookitsme (Brass Contributor)
Solution

@heylookitsme 

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

image.png

@Sergei BaklanThanks again for your help on this. I couldn't have done it without you.

1 best response

Accepted Solutions
best response confirmed by heylookitsme (Brass Contributor)
Solution

@heylookitsme 

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

image.png

View solution in original post