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

Подписаться 223 тыс.

Просмотров 39 тыс.

50% 1

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 support@datatraining.io
---------------------------------
😍 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 RU-vid: 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

Наука

Опубликовано:

29 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 99

@rokaskrisciunas6015 4 дня назад

Dan Bilzerian of Power BI. Thanks! Great material

@leewarps 3 года назад

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

@EricaDyson 3 года назад

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

@rewinwilson6021 3 года назад

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 3 года назад

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

@carlavernooy3530 3 года назад

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

@HowtoPowerBI 2 года назад

Glad I could help!

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

@Mamaditalia 4 месяца назад

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 4 месяца назад

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

@davidtong7926 2 года назад

This video was so useful. Thank you!

@dynrambo Год назад

Great guide - much appreciated!!

@JorgeSanchez-um7io 2 года назад

Great tip! Thanks for sharing!

@cahardikpishavadiya9463 4 месяца назад

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

@narminhumbatli7329 6 месяцев назад

it helped me more than everything with dirty unstructured data

@hazelness7 3 года назад

Thank you for this. you are awesome

@pankajkumarit04 2 года назад

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

@tcorey22 3 года назад

Thanks, this helped a lot!!!

@HowtoPowerBI 3 года назад

Glad it helped! 😃

@barttitulaerexcelbart9400 4 года назад

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 4 года назад

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.

@rlei327 3 года назад

absolutely practical guide!

@HowtoPowerBI 3 года назад

thanks! 😀

@vicomantoan3271 3 года назад

Thank you so much

@totvabe1 4 года назад

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

@HowtoPowerBI 4 года назад

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

@shimaagomaa2074 2 года назад

You are AMAZING

@kingsfordgokah6240 2 года назад

Super, this is what I needed

@HowtoPowerBI 2 года назад

Awesome, glad to hear it is helpful!

@anthonyalessi74 3 года назад

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 3 года назад

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

@anandnagarkatti 2 года назад

Great tutorial!

@ramonillarramendi3191 2 года назад

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.

@milagrosjaraquispe789 2 года назад

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

@HowtoPowerBI 2 года назад

Thank you so much!

@shravanrao5008 6 месяцев назад

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

@HowtoPowerBI 6 месяцев назад

hope he was impressed 😁

@vardhilpanchal5402 Год назад

Great tricks man....👌👌👌

@MIGAHU 3 года назад

gracias

@HowtoPowerBI 3 года назад

de nada

@EyiBillion 3 года назад

Brilliant! Awesome. You Rock!

@HowtoPowerBI 3 года назад

thank you!!! 😊

@jitendratrivedi7889 4 года назад

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

@HowtoPowerBI 4 года назад

Thank you! Will do!

@balrajsanthosh1942 2 года назад

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

@HowtoPowerBI 2 года назад

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

@ChrisHanson-wd7bw Год назад

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

@lenzypetty9371 Год назад

Another great explanation!!!

@HowtoPowerBI Год назад

Thanks :):) 😊

@chistony123 3 года назад

Thank you :)

@HowtoPowerBI 2 года назад

You're welcome! :)

@sattimama 2 года назад

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

@HowtoPowerBI 2 года назад

:) thanks Paluri

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

@Cuborubix-2018 2 года назад

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

@pahadianalyst 11 месяцев назад

This was amazing ❤❤❤🎉🎉🎉🎉

@HowtoPowerBI 11 месяцев назад

☺️ thank u

@dramaticreviews6278 Год назад

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

@emilybone2437 2 года назад

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

@sravankumar1767 3 года назад

Awesome 👌

@HowtoPowerBI 3 года назад

Thanks 🤗

@surajbatra8004 2 года назад

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?

@fazekaslaszlo 3 года назад

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

@HowtoPowerBI 3 года назад

i feel your pain 😅

@andreibulatov2395 4 года назад

Nice explanation! Keep on going)

@HowtoPowerBI 4 года назад

Thanks! 😃

@seemayadav9005 2 года назад

Amazing👍👍👍

@HowtoPowerBI 2 года назад

thanks Seema!

@arbazahmad7177 2 года назад

Great 👍

@HowtoPowerBI 2 года назад

Thank you!

@vishnurao2901 4 года назад

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

@HowtoPowerBI 4 года назад

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

@mon_rich5366 Год назад

best ever

@aigultulegenova9530 3 года назад

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 3 года назад

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

@marekbona4987 10 дней назад

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

@MLFranklin 3 года назад

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 3 года назад

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

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

@AbhishekSharma-zu2ev 3 года назад

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

@HowtoPowerBI 3 года назад

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 4 года назад

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

@HowtoPowerBI 4 года назад

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.

@AsianGardenAndFood 3 года назад

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

@HowtoPowerBI 3 года назад

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

@lexh7714 3 года назад

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 3 года назад

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.

@chistony123 3 года назад

Thank you is not enough still thank you

@HowtoPowerBI 3 года назад

thx for watching Fincy!

@SamehRSameh 3 года назад

How can i use excel formula in power query

@HowtoPowerBI 3 года назад

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 3 года назад

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

@techipradeepa7398 3 года назад

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

@HowtoPowerBI 3 года назад

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

@anrilombard2595 3 года назад

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 3 года назад

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