SOLVED

UnPivot Monthly Data

%3CLINGO-SUB%20id%3D%22lingo-sub-1867836%22%20slang%3D%22en-US%22%3EUnPivot%20Monthly%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867836%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20familiar%20with%20365%20Get%20%26amp%3B%20Transform.%20However%2C%20I%20am%20still%20feeling%20my%20way%20around%20a%20few%20items%20that%20I%20can't%20seem%20to%20wrap%20my%20head%20around%20and%20need%20some%20guidance.%20In%20the%20below%20scenario%20how%20could%20I%20unpivot%20this%20data%20set%20so%20it%20extracts%20the%20first%203%20characters%20from%20each%20column%20header%20(so%20it%20creates%20a%20month%20column%20from%20it%20and%20removes%20it%20from%20the%20field%20name%2C%20hence%20extract).%20Then%20instead%20of%2036%20columns%20of%20data%20it%20refreshes%20to%20only%203%20columns.%20See%20example%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1867836%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867899%22%20slang%3D%22en-US%22%3ERe%3A%20UnPivot%20Monthly%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867899%22%20slang%3D%22en-US%22%3E%3CP%3EI%20also%20meant%20to%20say%20I%20created%20a%20way%20of%20doing%20this%20via%20Macro%20but%20would%20like%20to%20see%20if%20there%20is%20a%20way%20to%20do%20this%20via%20Power%20Query%2FGet%20%26amp%3B%20Transform%20whatever%20the%20name%20for%20it%20may%20be%20this%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1868822%22%20slang%3D%22en-US%22%3ERe%3A%20UnPivot%20Monthly%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1868822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612943%22%20target%3D%22_blank%22%3E%40heylookitsme%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWithout%20small%20intermediate%20steps%3A%3C%2FP%3E%0A%3CP%3E-%20Let%20name%20source%20as%20Range%20and%20query%20it%3C%2FP%3E%0A%3CP%3E-%20Unpivot%20other%20columns%20but%20first%205%3C%2FP%3E%0A%3CP%3E-%20split%20first%20unpivoted%20column%20on%20month%20and%20name%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232406i1B7B6BDE767AE5F2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EGroup%20first%206%20columns%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232407iEC2FE2FEFE70C8F8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20Reference%20this%20query%2C%20name%20new%20one%20Headers%3C%2FP%3E%0A%3CP%3E-%20keep%20only%20last%20column%20and%20in%20it%20only%20first%20row%3C%2FP%3E%0A%3CP%3E-%20expand%20it%20and%20convert%20to%20list%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20127px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232409iC00E715E9D1C6D41%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20reference%20first%20query%2C%20let%20name%20it%20Result%3C%2FP%3E%0A%3CP%3E-%20create%20custom%20column%20extracting%20from%20last%20column%20with%20table%20only%20list%20of%20values%3C%2FP%3E%0A%3CP%3E-%20extract%20values%20from%20list%20with%20any%20delimiter%3C%2FP%3E%0A%3CP%3E-%20split%20result%20on%20columns%20using%20this%20delimiter%20and%20substitute%20column%20names%20list%20by%20Headers%20list%3C%2FP%3E%0A%3CP%3E-%20remove%20unused%20columns%20and%20load%20result%20to%20Excel%20sheet%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20685px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232410iB56877F475EDA319%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

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.

Highlighted

@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

 

Highlighted
Best Response confirmed by heylookitsme (Occasional 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

Highlighted

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