Promote Double Headers in Power Query | Solution
Vložit
- čas přidán 30. 07. 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
Folks here is the solution to the power query problem that I gave you the last week in which we have to promote double headers in the Power Query.
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Double Headers Blog - www.goodly.co.in/promote-doub...
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== CHAPTERS =====
0:00 Intro
0:21 Describing Double Headers Logic
1:47 Double Headers Solution
15:44 Summary
16:15 My Online Courses
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall CZcams Channel Below
czcams.com/channels/GQE.html...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses - Věda a technologie
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Loved the way you solved the issue and make it reusable. The way I always solve this issue is to transpose the table, merge the first columns and transpose it again. In that solution, you can also make use of the fill option to fill up the blanks if needed. Thanks!
Thank you Chandeep for another brilliant video. You explain and break it down so well. Definitely checking out your courses.
I really can't thank you enough. Keep up with your brilliant work!
Awesome Chandeep! That's some wild Advanced Editor / M code fun. I posted my solution on your blog which was done with just the UI. I was able to get Order Sale Date and Order Due Date correctly, but it would not be dynamic if you were to add another title row, as it relies on the more mortal tricks of Transpose, Promote/Demote First Row as Headers, Fill Down and Merge Columns. Nevertheless, was fun to come up with something that works and great to see the advanced approach you used with all the M functions. Lots to study here :)) Thanks for the challenge and thumbs up!!
Again thank you for this valuable service 🙏
Great stuff! Thank you, Chandeep.
Thanks. I too would have used the "transpose, merge and promote" method, but, this is much more elegant and I bet more processor efficient. Actually has opened my eyes to a different way to write M code, so, thank you.
Great video! One suggestion: take the list you created at the 7:20 mark (Headers) and you can use
ZippedRenameList = List.Zip({Table.ColumnNames(Source), Headers}),
RenameColumns = Table.Skip(Table.RenameColumns(Source, ZippedRenameList), 2)
Thank you for sharing you solutions Michael.
There is something I don't get about it.
By combining the new headers with the previous wouldn't you get the "Column" as prefix?
Ex. {Column1, Product | Code} etc
Hi Chandeep, I've just watched your video and you've probably solved the problem you mentioned at the end. To "fill right" I transpose the table, fill down then transpose to return the table to the correct orientation. I really enjoy your videos - very, very helpful and informative.
Super content...Great Indian Channel for learning Power BI.A great idea would be a video every month summarizing the updates for Power BI from the blog
I'm in love of Chandeep! Amazing way to explain things. Brilliant job and contribution. Thank you very much from Brazil.
Thank you for your inspiring words Jimmy! 💚
You are really good at explaining, I don't use power query or Power BI but your videos are making me try these tools.
Kudos to your efforts
Glad you like them!
Wow... Great tutorial
Following you since the begining, a big thank you for sharing all your knowledge!
My pleasure!
Thank you Chandeep for the M-code approach for merging these two header lines with M-codes. Said that, I would do it another way using only the UI: 1. Transpose the data set 2. Merge the first two columns (which are row headers to be combined). 3. Transpose again 4. Promote the header. 5. Done. Frankly, your method is more flexible and scalable but mine is easier for beginners. With the generated code of my suggested method, I think we can still make it more scalable as well. Happy sharing. Oh! I just saw other audience suggested the same alternative method.
I agree.. The only problem for larger datasets is that transpose is an expensive operation.
He is not goodly but godlike !! Thanks 👍
This is MAGIC!
Without a sample file, it was hard to tell in the blog picture, but it looked like "Product" was in a single cell, and "Order" was either 2 merged cells or text centered horizontally across two columns. Is there any way to determine that in PQ?
Really great solution to a problem I had a importing a PDF which was being done multiple times a day which came from a printout from a web page. The problem was that the table I wanted to import had a variable number of header rows. Luckily the first row of data was consistent ("DAT").
I used Table.Transpose( Data ) and then used if Data{0}["Column4"] and cycle through to "Column1" (always the default column names and never more than 4) to determine which row "DAT" was in to determine how many columns to merge.
mma173's function was great in that it figured out to add "Order" to the "Due Date" column, but I converted your code to a function, and the code is much more concise and readable.
Love your PQ videos. I've been consuming Excel videos since retiring, and your explanations are really clear, especially with complex concepts. Keep up the great work!
Thanks Chandeep.
For your merge cell problem, try to save as html. Then you get Order_1, then you'll probably find a way. As far as I understand, a merged cell is just a format, therefore power bi can't "see" it. I can't clean super-/subscript in Power Query either.
Alternative solution (Imke Feldmann-Chandeep-Mix)
let
Source = Excel.Workbook(File.Contents("c:\BI_Data\goodly\Promoting-Double-Headers-Data-in-Power-Query.xlsx")){[Item="Sheet1",Kind="Sheet"]}[Data],
NewColNames =
List.Transform(
Table.ToColumns(Table.FirstN(Source,2)),
each Text.Combine(_,"|")
),
OldColNames=Table.ColumnNames(Source),
RenameList = List.Zip({OldColNames, NewColNames}),
NewTab = Table.RenameColumns(Table.Skip(Source,2), RenameList)
in
NewTab
Awesome.!
Awesome. Thank you . Looking forward to more complex Mcode problems.
Glad you like it!
It's so easy to follow....Thank you so much.
Glad you like it!
Thank you my friend - another great video.
Glad you like it 💚
Great video, thank you! :) Can i ask have you find solutions for merged cells?
Thanks for this. Please share how can we make multiple header table in Power Bi,that will be very helpful.
This is awesome..thanks Chandeep
Glad you like it!
Hello, Goodly,
please could you tell me if it is possible make the same thing with Headers but to have Original document in Another Workbook and bring data prom the other Workbook or Folder ? Thank you.
Great job..
Video was really useful for me.
Your guidance is requested in following situations.
*Using folder option in get data from in excel
*All excel files have same pattern
* Each workbook have 12 sheets (some times there are more than 20 sheets)
*Each file have first four rows and at 3rd row date is mention.(sys run date: 31-JAN-2022) in one cell
*Below the that date there are transactional columns
Suppose have following pattern:
1row:FBL company limited
2row: blank
3row: Sys run date: 31-JAN-2022
4row: blank
5th row: have column name and in below rows contains transaction info
How can I get the date in a separate column in such situation?
Thank you Chan deep for the video. Insert.row code showing error what could be the issue, could you please advice
Hi Chandeep, easiest approach that I use for multiple headers is I transpose the data , then merge left 02 columns and then transpose back following with promote headers. I have been doing this since 2017 and so far no problem is faced. Learned from Mike Girvin videos.
I agree, just that transposing a large data could be very performance expensive.
@@GoodlyChandeep Yes it is indeed performance costly ... I had it in mind, glad you added something new once again.
Hi Chandeep
I couldn’t find you in messenger ..
I want to show TOP N values in my report but I am using direct query.
My data source is snowflake.
Topn Dax won’t work in direct query so do we have any workaround to achieve this?
Any link for reference ?
Appreciate your help.
Thanks
Hallo Goodly
Regarding the incomplete headers:
Once you reduced the table to the first 2 rows, try to transfer the table rows to columns and use the fill down function. After this is done you switch column and rows back and continue with the functions you show in your video.
This should work if PQ resolved headings which used combined cells in excel.
What do you think?
Cheers Seb
This has always been my approach for this situation, except you don't need to reduce the table to two rows. Transpose the table, Fill Down, Merge using delimiter, Transpose again, then split by delimiter (off the top of my head, but that is the general pattern). All of that can be done in the GUI. This video is a fascinating example of M code manipulation, but I think it is overkill for the problem unless you are a Power Query whiz.
Agree, that's the way I would do it too. Since top row should not be blank unless it will be filled by the Super-Header, I think this would work just fine for most scenarios. I think the benefit of doing just two rows as opposed to entire table - is that you potentially don't need to pivot millions of rows, so it's more memory efficient.
Great stuff, Would you happen to have a solution for the situation when your list of Headers has #'s not just text? My rows to combine are Year "2024", Date "03", Month "Mar". Thanks for all you do.
Hi goody! I am not sure if you have a video about my question, what pquerybcode donintype to filter the results. Like for example to exclude Scheduled Break, Lunch etc froma column? Thank you😃😃
One thing that puzzles me 'list.transform' , why can I use eg. Text.Combine ( list, "") directly sometimes and other times as here I have to wrap in in list transform, is due to htis just being a list, if I were adding a column to a table ; then could I use the function directly? I do like this video, it's like Generate between start and end dates, even if the output isn't that useful to an individual you learn a lot of functions. Thank you.
Brilliant content- really helpful stuff.
To solve the merge issue, could you transpose the small headers table and fill down?
Actually I see now why that wouldn’t always work..
i am interested in dax beginner course. can you explain abt course. is it a video based course or how...
Awesome, chandeep, but please, I think now you are able to share with us the merge issue as it's important for all of us,
In addition, my data, which is in the first row, is founded on 1 column, but I need to divide it based on 3 columns. Could you support.
Plz let me kw.. If column headers are date like 1-10-2022, 2-10-2022 till month end... N value is in percentage for multiple servers... How to do in power bi as next month column header will change means date will change
Hi Chandeep. Thank you for all these amazing vidoes. Could you please let me know that are you using any software for the mouse pointer? or its just an inbuilt. Thanks in advance
Zoomit - from Microsoft.
it's free
I have a problem and it would be great if you can provide the solution. Rows have student ID and columns have courses, more than one. I want to show ID in pink if it is female and in blue if male or else in orange.
Marvelous M code
Great Video! However, I tried to apply in my table and I get an error. The reason is because we use in the section "Headers" the "each Text.Combine(_,"|")". Due to the reason I have also numbers (volumes) in the rows, I get an error. My idea was to combine everything by deliminator and after unpivoting the data to seperate to columns.
Do you have an idea how I could solve the problem by replacing Text.combine in order to collect Text and numbers together?
Thanks in advance!
Best,
Manuel
How to calculate max consecutive negative number in a column in power query?
Isn't it easier to update the headers directly without inserting Rows? Thanks
Great job!. One question please; What software do you use to make this awsome videos?
It's a process more that the software. But here is what I use
> Camtasia for recording
> Adobe Premiere Pro for editing
> Zoom it for screen annotations
> Google and One drive to sync everything
@@GoodlyChandeep Thanks for both, video and making procedure..keep up the great work
my data workbook (Source) has multiple Sheets what is Mcode for this added complexity...also 3 rows of header name(ugh)
Order due date was the one thing I too was unable to figure out...
How can we make the number of rows from being 2 to dynamic. I want user to input the number of rows to be concade
See the function in blog comments from mma173
It can be addressed by doing transpose and then merge the first two columns and then transpose back and we get the same solution?
Its good to know the new M language commands which we can use to do any automation of addressing the double header data.
You are too good at M language and you are addicted to it 🙂. You prefer to solve by using M language than using GUI buttons.
Hi. My attempt to concatenation these two rows is - transpose, then merge the two columns and then transpose back again
Just that transposing and pivoting slows down the query on large data :(
You mentioned turning this into a function, have you done a video on them? I've watched quite a few and still don't get it, every time I watch one they seem to use difference methods, different ways of invoking, sometimes click on Add columns, and invoke from there, other times select the function and invoke also when to add 'as table' , or not, so sure I can follow along but that's not the same as knowing what to do in different situations. Sorry , really long comment.
Invoking a Function via Invoke options gives you a nice UI to fill in which is not available in the custom column option.
In order to create function and declare variable and outputs you need to have a thorough understanding of the M language.
@@GoodlyChandeep i have ;turned this into a function, but it's always trial and error as to whether to make in a 'table function' or whether I need it to act on a column.
Hi Chandeep
My apologies if I missed something here.
But why can't you just Transpose the Table and Merge the 1st 2 columns and then again Transpose the Table and make the 1st row as header.
What have I missed here?
Query Performance :)
Transposing a large table will kill the query
I think you are right!
@goodly but transposing the first two rows, combining columns, transposing then combining with the original table would surely work efficiently? It is an alternate solution, I think.
Better to Remove first two rows . Rename the column .
Hey,
I have faced a similar kind of problem. But a little difference. Anyway, thank you for sharing this amazing way to do this.
I would like to know a solution for this.
Q: -
In your file, Headers looks like this.
Product | null
Code | Customer name
So, the output header will be like
Product.Code | Customer name
But, In my file, the headers look like this.
Product | null
Code | Name
So, I need to get the headers like
Product.Code | Product.Name
How I will do that?
Hope I explained well.
Thank you
You just need to fill down the first 2 rows of data. Rest remains the same
Hi, For merged cells, taking an example, when a cell is merged over three cells, that is, the other two cells take the null value in the power request, so if we use over three steps the problem is solved, Namely:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Headers=
Table.Transpose( --------------------- 03
Table.FillDown( --------------------- 02
Table.Transpose( -------------- 01
Table.FirstN(Source,3)
),{"Column1"}
)
)
in
Headers
tell me what you think about this... because your opinion is important to me.
your friend Moussa from Algeria
Goodly by name, but GODLY by nature. You are lifting the whole PowerBI/PowerQuery community up from our knees! Eternally grateful for your presence and knowledge.
Thanks Man.. I am glad you found the content helpful.
Ist seen