Merge Header transformation

Power Query
1

Hi Experts,

I have the data like below, which is having a merged cell in excel with the Month name, and below it has a current month and previous year sales.

How to do a transformation to use that in the right way in our modeling.

January February March April May
FAB BRAND Sales Last Year Sales Current Year Sales Last Year Sales Current Year Sales Last Year Sales Current Year Sales Last Year Sales Current Year Sales Last Year Sales Current Year
A1 ABBAYE 96095 5601 26622 26502 55108 25978 107011 81197 100180 72098
A1 ATLA 64233 41935 39974 13760 3600 124712 104006 170474 80350 26360
A1 AUTRES 116935 157935 105034 143498 59172 88343 9579 58294 53861 13248
A1 BADYO 27258 112919 44927 94990 60797 62528 71573 98998 7560 2957
A1 TOW BREWERY 115099 170358 44168 127992 66873 97736 39114 17056 121975 58262
A1 COROY 109239 84253 1480 69293 97402 46062 107820 97966 103957 17375
A1 BANIS 103001 1362 32795 154517 47001 19970 64068 60873 68479 3596
A1 DIUS 76594 127105 101327 114456 61781 162420 113287 21008 62591 28487
A1 GITTE 60320 27871 26848 5075 59793 60730 114089 48054 56052 167564
A1 GOSLAND 56748 112735 96739 138139 47958 158334 99817 88347 50642 25312
A1 HEGAO 124137 48557 85480 16783 75471 139595 576 164125 36792 172506
A1 KEM 79156 32555 112958 16900 76608 20195 70007 67327 77239 32435
A1 BECAE 81819 99248 15373 65913 123178 85034 6606 154952 108385 169816
A1 LEGGERE 47710 46312 89238 92939 9413 4995 93437 24702 125104 173479
A1 STARTOIS 56833 147367 111344 121096 94410 46610 91348 120582 79813 113729
A1 TRIPLIET 100837 127617 57717 170674 23792 77099 104313 91428 125550 153770
B1 BAVARIN 63063 92799 42366 132047 20686 116533 116164 72335 2554 128702
B1 AUTREB 120837 27711 15697 1110 48437 36329 96019 34832 87138 60841
B1 HORNET 74000 130077 76253 99041 40793 131722 94840 149843 68990 155051
B1 OTHER BRANDS 19325 77554 107378 154102 19425 97013 42593 137633 50687 120409
B1 PALM 45852 98902 53434 56056 117072 131840 40096 35876 63553 64919
B1 RODENCH 45883 163506 108343 90751 83005 154700 24750 71039 45759 1918
B1 STEENBRU 110901 138101 56587 96039 87910 80629 85415 31229 108982 141907
B1 URTHEL 78341 138376 110179 20939 46580 143507 107703 117738 47839 107389
G1 1765 36962 159422 80068 78516 95092 103871 100482 47483 108491 114691
G1 TRESSBERG 54333 18138 99143 33843 110800 146952 5738 2094 12560 43305
G1 BROOK 111545 24979 38215 111243 106348 94775 53908 128713 10312 6249

Attaching the excel file for your reference.Data Transfer.xlsx (19.9 KB)

Any quick will be greatly appreciated.

Thanks

2

Hello @putturps,

Thank You for posting your query onto the Forum.

Well this is achievable by following the steps as suggested below -

Step 1: Load the data into the Query Editor and create a “Reference” of that data. And then un-check the option of “Enable Load” for the original data that has been loaded.

Step 2: For every month perform the step as suggested -

  1. Replace the value - Sales Last Year with Sales Last Year and Month Name i.e. in the case of January it will be “Sales Last Year - January”.

  2. Replace the value - Sales Current Year with Sales Current Year and Month Name i.e. in the case of January it will be “Sales Current Year - January”.

So it should like this as per the screenshot provided below -

Step 2 - Result
Step 2 - Result847Ă—400 18.5 KB

Follow this same step till the very end i.e. as of now you’ve the data till November so follow this till November.

Step 3: Use the “First Row as Headers”

Step 4: Now select the columns from “Sales Last Year - January” till “Sales Current Year - November” and then “Unpivot The Columns”. So now your result should somewhat look like this as per the screenshot provided below -

Step 4 - Result
Step 4 - Result1267Ă—825 49.5 KB

Now, you must be wondering that “What If” I now add the data for the December month. Will it throw back the error since new column has been added? And the answer is -

No, it will not throw the error and that’s the reason why we created a reference of the original data. So now, if I add a new data for the December month it will just be added into the transformed data very swiftly without throwing back the error. Let’s see how we can tackle that -

Step 5: Add a new column for the December month into the Excel file as well as add random figure into the first column for checking purpose and save that file. Below is the screenshot provided for the reference -

Step 5 - Result
Step 5 - Result837Ă—286 7.79 KB

Step 6: Refresh the Power BI - Query Editor to see the result. Below is the screenshot provided for the reference -

Step 6 - Result
Step 6 - Result1263Ă—830 49.8 KB

Now you’ll see that under the column “Month Name” there’s a value shown as “Null”. Well this is an easy fix. Just replace the “Null” value with the month name i.e. in this case with the “December”.

Step 7 - Final Step: Replacing the “Null” value with the month name “Decemeber”. Now, the end result should somewhat look like this. Below is the screenshot provided for the reference -

Step 7 - Resultant Output
Step 7 - Resultant Output1269Ă—830 50.2 KB

I’m also attaching the working of the Text, Excel as well as PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Important Note: Well it’s always recommended to have a longer tables than the wider ones and that’s the reason why I unpivoted data in order to optimize the model.

Thanks and Warm Regards,
Harsh

Text File - Harsh.txt (708 Bytes)

Data Transfer - Harsh.xlsx (20.1 KB)

Data Transformation - Harsh.pbix (42.8 KB)

2 Likes
3

Hi @putturps,

Here’s an alternative method to the one already provided by @Harsh

  1. Don’t promote headers (demote them if necessary in your production file)

    image
    image847Ă—327 14.8 KB

  2. Transpose the data

    image
    image837Ă—313 15.9 KB

  3. FillDown the Month names

    image
    image839Ă—313 16.4 KB

  4. Add a suffix and Merge Col1 & 2
    image

  5. Transpose back, Remove all blank rows and Promote Headers

    image
    image941Ă—314 11.6 KB

  6. Unpivot the data
    image

  7. Split the Attribute- and Rename column

    image
    image837Ă—312 9.65 KB

    .

Here’s your sample file. Transform Multiple Headers.pbix (44.7 KB)
I hope this is helpful

4 Likes
4

Thank you Harsha for quick turnaround. Appreciate your help

5

Thank you Melissa for the alternative solution, It was easy to apply

6

Hello @putturps,

You’re Welcome. :slightly_smiling_face:

We’re glad that we’re able to help you and you found the solutions useful.

Thanks and Warm Regards,
Harsh

“Harsha” :joy: - I guess now you converted me from a normal Indian guy to a specific South Indian one. :joy:

1 Like
7

:smiley: :smiley: Harsha means Happiness…

1 Like
8

Thank You @putturps :slightly_smiling_face: