Dealing with DOUBLE HEADERS in Power BI using UNPIVOT, TRANSPOSE, and FILL DOWN

Learn how to restructure data sets that have double headers in Power BI. See how to do it using unpivot, transpose, and fill down. Enjoy!
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly [e-mail]
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO​
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZbin: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI​ #PowerBI​ #DataTraining​
#powerbidesktop​ #powerbitraining​ #powerbideveloper​ #DAX

[@leewarps]

Exactly what I was looking for, and even more! Thanks brother, will check out your other videos as well.

[@EricaDyson]

And just what I needed also! Great explanation... step by step.. and really practical. Thanks a bundle.

[@rewinwilson6021]

Can I just say "Thank you"... You've covered all the scenarios that are usually made up in the corporate world of "Table making".... Thank you sooooo much... Very informative and detailed....

[@HowtoPowerBI]

thank you so much for watching! I am glad it was helpfull! 😀

[@andysmudge4849]

Wow!! I never knew these features or I can say, I have never explored them. Kudos brother. That's so helpful. 😊

[@hotookhatri7074]

I was facing problem, because of three headers. last part of the video helped a lot, thanks you for positing it is very helpful.

[@narminhumbatli7329]

it helped me more than everything with dirty unstructured data

[@pankajkumarit04]

Super Cool Trick to resolve the Double Headers... WOW

[@carlavernooy3530]

Thank you so much!! Just what I was looking for.

[@HowtoPowerBI]

Glad I could help!

[@Mamaditalia]

You should make educational videos for Microsoft. I started learning with the learning material provided by Microsoft website, but i find it too hard to learn how to use power bi by reading and following their tutorials. I really learnt a lot from your videos. Thanks

[@HowtoPowerBI]

thank you!! 😁 happy to hear that otherwise nobody would watch my videos lol

[@shravanrao5008]

Thank you so much brother! This was absolutely important for me to impress my boss😁😁

[@HowtoPowerBI]

hope he was impressed 😁

[@davidtong7926]

This video was so useful. Thank you!

[@JorgeSanchez-um7io]

Great tip! Thanks for sharing!

[@cahardikpishavadiya9463]

Hi Das, thank you so-so much, this has been very helpful.

[@barttitulaerexcelbart9400]

Very good explained. Thank you. Good that you increase the level of complexity. One remark: I did use your example in Excel, then comes an extra step, because in the region row, I have not null but Column2, column3 etc.

[@HowtoPowerBI]

Thanks for pointing out :) When connecting to the excel file it automatically promotes the headers and changes the data type. If you delete those steps then you can follow as in the video.

[@kishoredubbasi9780]

Hey Great thanks! But I have scenario where I have to show calculated measures which is not visible in power query how to fix this it will be a great help thanks in advance!!!

[@dynrambo]

Great guide - much appreciated!!

[@anthonyalessi74]

This is an amazing tutorial! One problem I've noticed is when transposing if the query has a lot of rows it runs out of memory to perform the action. Hopefully row-wise functions come at some point. Thank you for posting this, really helped me understand pivot better!

[@HowtoPowerBI]

thanks Anthony! if you have a lot of rows this will be quite slow indeed. Maybe it is possible to put in a filter before you do all the transformations and then bring back the data after you fixed the double header problem.

[@StefanoVerugi]

when you have many rows you can do: 1 use Table.FirstN(Source,2) you select the first rows containing multi-row header, name it as such, you can transpose that only to fill null values and transpose it back to get your new single-row header after you create a column combining the two first ones 2 use Table.Skip(Source,2) to get your 'data' by skipping the first two where the double headers are 3 use Table.Combine({header,data}) to append data to the header 4 promote your first row as header hope it helps

[@totvabe1]

Step by step, with these explanations everything seems to be easy.

[@HowtoPowerBI]

thank you! if you have any topic requests just let us know :D

[@ramonillarramendi3191]

Hi Bas, thank you very much for your video, it works like a charm and it is explained wonderfully as usual. But how could I transform a data that has 3 top levels, as Group / Category / Product (which has to be the header) then details, and in the same column after several products I have a different category. Transposing and filling down, only gets the first Group/ category, but the second or other categories are missed since they are not filled down. These additional categories may be on different columns (which may vary). Any advice? The data comes from PDF.

[@kingsfordgokah6240]

Super, this is what I needed

[@HowtoPowerBI]

Awesome, glad to hear it is helpful!

[@shimaagomaa2074]

You are AMAZING

[@hazelness7]

Thank you for this. you are awesome

[@dramaticreviews6278]

hi bas. i wanted to ask how to promote headers from different rows in power query editor

[@milagrosjaraquispe789]

It's an amazing tutorial! easy to undestand :)

[@HowtoPowerBI]

Thank you so much!

[@balrajsanthosh1942]

I'm speechless with the way of explanation for the conecpt, its more than awesome bro 😍❤

[@HowtoPowerBI]

Super happy to hear this feedback from you! Thanks :):)

[@emilybone2437]

I think this is what I'm looking for to solve my problem, each of my rows are projects, and each project has a summary of equipment column with entries like: "1xSM, 6xEH, 3xMF". If I want to get a count of how many EH for projects, is there something you could suggest?

[@sky-vn4mx]

Will it going to take right value and total beacuse when i tried it in matrix visual after unpivoting it is showing wrong data and total so what can i do for it..

[@jitendratrivedi7889]

Very useful tips. Please post more videos on power query/data cleansing/transformation.

[@HowtoPowerBI]

Thank you! Will do!

[@surajbatra8004]

The refresh would throw up an error if new products are added or product names are changed, any idea on how to handle that situation?

[@vardhilpanchal5402]

Great tricks man....👌👌👌

[@Cuborubix-2018]

Your videos are very nice!! Can you help me to give different different headers for group of columns?

[@ChrisHanson-wd7bw]

BRAVO! BRAVO! I take my hat off to you Sir =)

[@fazekaslaszlo]

brilliant thanks - have to deal with these kind of crap "data" day in and out lol

[@HowtoPowerBI]

i feel your pain 😅

[@anandnagarkatti]

Great tutorial!

[@tcorey22]

Thanks, this helped a lot!!!

[@HowtoPowerBI]

Glad it helped! 😃

[@marekbona4987]

It works perfectly but when i update excel files which is source for my powerBI then while refreshing preview it shows me error that The column which i merged and used to get rid of double headers with decimeter couldnt find and i do need to do it again with all the steps

[@sattimama]

As usual you came with an interesting thing in pivoting/unpivoting 👌

[@HowtoPowerBI]

:) thanks Paluri

[@rlei327]

absolutely practical guide!

[@HowtoPowerBI]

thanks! 😀

[@vicomantoan3271]

Thank you so much

[@EyiBillion]

Brilliant! Awesome. You Rock!

[@HowtoPowerBI]

thank you!!! 😊

[@lenzypetty9371]

Another great explanation!!!

[@HowtoPowerBI]

Thanks :):) 😊

[@disneypatterson8667]

Hi I'm not getting null values like you do. Those null values are displaying as Column1, Column 2 etc. Therefore, the technique isn't working. Any ideas? Thank you!

[@pahadianalyst]

This was amazing ❤❤❤🎉🎉🎉🎉

[@HowtoPowerBI]

☺️ thank u

[@vishnurao2901]

Great information 👍 Please make a video on " Dashboard sharing and collaboration on Power BI Services".

[@HowtoPowerBI]

Thsnks for idea, will put it on my todo list ;)

[@anrilombard2595]

Hello, thank you for the video - it is amazing. I am just not sure how to make this so that when another file gets added (when the source is a folder), the query breaks when refreshed? Is there a way to deal with this please?

[@HowtoPowerBI]

thx Anri 🙂 ! works the same when you connect to a folder. Not sure why it breaks without having more details

[@MLFranklin]

This is really good. This solves a big part of my problem. The other part of my problem is that I'm pulling similar Excel files from a folder. Power BI combines them by adding them vertically rather than horizontally, so I get extra headers from that. I have a workaround that preprocess the files and combines them into an unpivoted table, but I was trying to do it all with Power BI and skip the macro step. Any advice?

[@HowtoPowerBI]

thank you! You could try the following: (1) create a sample file that contains all the columns from the files that you want to merge, but has no data in it (2) perform the combine files process as you already do now (3) use group by to "match" the data at the right level. Example: we have one file for cost by product, one for sales by product then (1) Create a sample files with the headers cost, sales, product (2) perform the combine files process (3) group by product with as aggregations the sum of cost and sum of sales (4) remove the empty row. Let me know if that was clear and solves your problem :)

[@andreibulatov2395]

Nice explanation! Keep on going)

[@HowtoPowerBI]

Thanks! 😃

[@AbhishekSharma-zu2ev]

Thanks. do you know how to make the double header view in power bi ?

[@HowtoPowerBI]

you can go for a matrix visual, place the two fields you want to use for you column headers on the columns drop zone. Then expand down.

[@sravankumar1767]

Awesome 👌

[@HowtoPowerBI]

Thanks 🤗

[@chistony123]

Thank you :)

[@HowtoPowerBI]

You're welcome! :)

[@seemayadav9005]

Amazing👍👍👍

[@HowtoPowerBI]

thanks Seema!

[@MIGAHU]

gracias

[@HowtoPowerBI]

de nada

[@nikahafiz1]

may i kindly ask why wud u do a pivot column as in the last example in the vdo?

[@HowtoPowerBI]

You could also have left it unpivoted. It is just that this requires you to use the calculate function when you for example want to return the sum of revenues.

[@lexh7714]

but what to do ? if for example, if instead of having null values i have dates, so i have category 1 and then i have dates that belong to category 1 and then i have category 2 and then again dates that belong to category 2 and so on

[@HowtoPowerBI]

Hi Alex, I would demote the headers, transpose the data set to have the dates in a column instead of having them on one row. Then pivot the kpi column to have separate columns for each kpi.

[@AsianGardenAndFood]

How did you color the different headers for the two different header?

[@HowtoPowerBI]

that's in the excel source file not power bi 🙂

[@arbazahmad7177]

Great 👍

[@HowtoPowerBI]

Thank you!

[@mon_rich5366]

best ever

[@techipradeepa7398]

I am.having query ,I need to bring projection, outturn as column header For. Ex Items proj outirn today tomorrow date1 date2 date3

[@HowtoPowerBI]

Not sure if I understand your question, but I think what you need here is the pivot functionality which you find under transform.

[@SamehRSameh]

How can i use excel formula in power query

[@HowtoPowerBI]

Power query uses a language called M, so you cant. For calculations or transformation you can use the power query interface or write it in M.

[@SamehRSameh]

@@HowtoPowerBI So, we need more and more from M language Plz ☺️

[@chistony123]

Thank you is not enough still thank you

[@HowtoPowerBI]

thx for watching Fincy!

[@aigultulegenova9530]

Thank you so much for such a useful video! I got a question, I want to add a calculated measure which will be after the value however when I add it into values in the matrix, I got not exactly what I wanted, do you know how to solve this?)

[@HowtoPowerBI]

Thanks for watching Aigul! 😀 happy to have a look, but could you give me some more info