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

[@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! 😀

[@leewarps]

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

[@andysmudge4849]

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

[@EricaDyson]

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

[@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.

[@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

[@rokaskrisciunas6015]

Dan Bilzerian of Power BI. Thanks! Great material

[@gopalmahajan4140]

Excellent sir, Highly appreciated

[@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

[@carlavernooy3530]

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

[@HowtoPowerBI]

Glad I could help!

[@cahardikpishavadiya9463]

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

[@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!!!

[@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.

[@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 :):)

[@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?

[@GatorAce]

This video was so useful. Thank you!

[@maheshbabu2523]

Could you please let me know how to do if my values are measures

[@shravanrao5008]

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

[@HowtoPowerBI]

hope he was impressed 😁

[@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!

[@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

[@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..

[@dynrambo]

Great guide - much appreciated!!

[@narminhumbatli7329]

it helped me more than everything with dirty unstructured data

[@pankajkumarit04]

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

[@dramaticreviews6278]

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

[@kingsfordgokah6240]

Super, this is what I needed

[@HowtoPowerBI]

Awesome, glad to hear it is helpful!

[@vardhilpanchal5402]

Great tricks man....👌👌👌

[@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

[@JorgeSanchez-um7io]

Great tip! Thanks for sharing!

[@AsianGardenAndFood]

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

[@HowtoPowerBI]

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

[@milagrosjaraquispe789]

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

[@HowtoPowerBI]

Thank you so much!

[@lenzypetty9371]

Another great explanation!!!

[@HowtoPowerBI]

Thanks :):) 😊

[@sattimama]

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

[@HowtoPowerBI]

:) thanks Paluri

[@anandnagarkatti]

Great tutorial!

[@jitendratrivedi7889]

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

[@HowtoPowerBI]

Thank you! Will do!

[@shimaagomaa2074]

You are AMAZING

[@tcorey22]

Thanks, this helped a lot!!!

[@HowtoPowerBI]

Glad it helped! 😃

[@pahadianalyst]

This was amazing ❤❤❤🎉🎉🎉🎉

[@HowtoPowerBI]

☺️ thank u

[@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

[@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.

[@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.

[@rlei327]

absolutely practical guide!

[@HowtoPowerBI]

thanks! 😀

[@hazelness7]

Thank you for this. you are awesome

[@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.

[@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.

[@EyiBillion]

Brilliant! Awesome. You Rock!

[@HowtoPowerBI]

thank you!!! 😊

[@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?

[@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 ;)

[@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

[@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 :)

[@fazekaslaszlo]

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

[@HowtoPowerBI]

i feel your pain 😅

[@Cuborubix-2018]

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

[@vicomantoan3271]

Thank you so much

[@andreibulatov2395]

Nice explanation! Keep on going)

[@HowtoPowerBI]

Thanks! 😃

[@seemayadav9005]

Amazing👍👍👍

[@HowtoPowerBI]

thanks Seema!

[@chistony123]

Thank you :)

[@HowtoPowerBI]

You're welcome! :)

[@MIGAHU]

gracias

[@HowtoPowerBI]

de nada

[@sravankumar1767]

Awesome 👌

[@HowtoPowerBI]

Thanks 🤗

[@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 ☺️

[@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.

[@chistony123]

Thank you is not enough still thank you

[@HowtoPowerBI]

thx for watching Fincy!