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....
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!
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.
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
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.
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.
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!!!
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
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!
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..
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?
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.
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
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.
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?)
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?
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?
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 :)
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
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.
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....
thank you so much for watching! I am glad it was helpfull! 😀
Exactly what I was looking for, and even more! Thanks brother, will check out your other videos as well.
And just what I needed also! Great explanation... step by step.. and really practical. Thanks a bundle.
Excellent sir, Highly appreciated
Dan Bilzerian of Power BI. Thanks! Great material
Wow!! I never knew these features or I can say, I have never explored them. Kudos brother. That's so helpful. 😊
I was facing problem, because of three headers. last part of the video helped a lot, thanks you for positing it is very helpful.
Thank you so much!! Just what I was looking for.
Glad I could help!
Great guide - much appreciated!!
Thanks, this helped a lot!!!
Glad it helped! 😃
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!
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.
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
This video was so useful. Thank you!
Hi Das, thank you so-so much, this has been very helpful.
Great tip! Thanks for sharing!
Super Cool Trick to resolve the Double Headers... WOW
Super, this is what I needed
Awesome, glad to hear it is helpful!
Great tricks man....👌👌👌
Step by step, with these explanations everything seems to be easy.
thank you! if you have any topic requests just let us know :D
Great tutorial!
Another great explanation!!!
Thanks :):) 😊
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.
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.
You are AMAZING
Thank you for this. you are awesome
it helped me more than everything with dirty unstructured data
gracias
de nada
absolutely practical guide!
thanks! 😀
This was amazing ❤❤❤🎉🎉🎉🎉
☺️ thank u
It's an amazing tutorial! easy to undestand :)
Thank you so much!
I'm speechless with the way of explanation for the conecpt, its more than awesome bro 😍❤
Super happy to hear this feedback from you! Thanks :):)
Thank you so much brother! This was absolutely important for me to impress my boss😁😁
hope he was impressed 😁
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!!!
Very useful tips. Please post more videos on power query/data cleansing/transformation.
Thank you! Will do!
Thank you :)
You're welcome! :)
Thank you so much
Brilliant! Awesome. You Rock!
thank you!!! 😊
As usual you came with an interesting thing in pivoting/unpivoting 👌
:) thanks Paluri
hi bas. i wanted to ask how to promote headers from different rows in power query editor
How did you color the different headers for the two different header?
that's in the excel source file not power bi 🙂
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?
Thanks. do you know how to make the double header view in power bi ?
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.
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
thank you!! 😁 happy to hear that otherwise nobody would watch my videos lol
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!
brilliant thanks - have to deal with these kind of crap "data" day in and out lol
i feel your pain 😅
Amazing👍👍👍
thanks Seema!
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..
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?
thx Anri 🙂 ! works the same when you connect to a folder. Not sure why it breaks without having more details
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.
Nice explanation! Keep on going)
Thanks! 😃
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
Great information 👍 Please make a video on " Dashboard sharing and collaboration on Power BI Services".
Thsnks for idea, will put it on my todo list ;)
Awesome 👌
Thanks 🤗
may i kindly ask why wud u do a pivot column as in the last example in the vdo?
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.
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?)
Thanks for watching Aigul! 😀 happy to have a look, but could you give me some more info
Your videos are very nice!!
Can you help me to give different different headers for group of columns?
Great 👍
Thank you!
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?
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?
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 :)
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
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.
best ever
How can i use excel formula in power query
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.
@@HowtoPowerBI
So, we need more and more from M language Plz ☺️
I am.having query ,I need to bring projection, outturn as column header
For. Ex
Items proj outirn today tomorrow date1 date2 date3
Not sure if I understand your question, but I think what you need here is the pivot functionality which you find under transform.
Thank you is not enough still thank you
thx for watching Fincy!