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....
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!
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
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.
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.
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 :)
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?)
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.
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
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!!!
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..
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
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?
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?
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.
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! 😀
And just what I needed also! Great explanation... step by step.. and really practical. Thanks a bundle.
Step by step, with these explanations everything seems to be easy.
thank you! if you have any topic requests just let us know :D
Hi Das, thank you so-so much, this has been very helpful.
it helped me more than everything with dirty unstructured data
Thank you so much brother! This was absolutely important for me to impress my boss😁😁
hope he was impressed 😁
Very useful tips. Please post more videos on power query/data cleansing/transformation.
Thank you! Will do!
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 :):)
Great tricks man....👌👌👌
gracias
de nada
BRAVO! BRAVO! I take my hat off to you Sir =)
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!
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 ;)
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 tip! Thanks for sharing!
Thank you so much
hi bas. i wanted to ask how to promote headers from different rows in power query editor
This was amazing ❤❤❤🎉🎉🎉🎉
☺️ thank u
As usual you came with an interesting thing in pivoting/unpivoting 👌
:) thanks Paluri
It's an amazing tutorial! easy to undestand :)
Thank you so much!
You are AMAZING
absolutely practical guide!
thanks! 😀
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.
Brilliant! Awesome. You Rock!
thank you!!! 😊
Awesome 👌
Thanks 🤗
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.
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 :)
Your videos are very nice!!
Can you help me to give different different headers for group of columns?
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
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.
Nice explanation! Keep on going)
Thanks! 😃
Exactly what I was looking for, and even more! Thanks brother, will check out your other videos as well.
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 ☺️
Thank you is not enough still thank you
thx for watching Fincy!
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.
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
Dan Bilzerian of Power BI. Thanks! Great material
best ever
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!!!
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..
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?
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
Another great explanation!!!
Thanks :):) 😊
brilliant thanks - have to deal with these kind of crap "data" day in and out lol
i feel your pain 😅
Amazing👍👍👍
thanks Seema!
Thank you so much!! Just what I was looking for.
Glad I could help!
Super, this is what I needed
Awesome, glad to hear it is helpful!
Great 👍
Thank you!
Thanks, this helped a lot!!!
Glad it helped! 😃
Thank you :)
You're welcome! :)
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?
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.
Super Cool Trick to resolve the Double Headers... WOW
Great guide - much appreciated!!
Thank you for this. you are awesome
How did you color the different headers for the two different header?
that's in the excel source file not power bi 🙂
Great tutorial!
This video was so useful. Thank you!
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.
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
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.