Promote Double Headers in Power Query | Solution

  Рет қаралды 24,753

Goodly

Goodly

Күн бұрын

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 KZbin Channel Below
kzbin.info/door/GQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

Пікірлер: 84
@GoodlyChandeep
@GoodlyChandeep 8 ай бұрын
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
@Maartenmarsje
@Maartenmarsje Жыл бұрын
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!
@DancingZeliha
@DancingZeliha 2 жыл бұрын
Thank you Chandeep for another brilliant video. You explain and break it down so well. Definitely checking out your courses.
@raimundojs9547
@raimundojs9547 Жыл бұрын
I really can't thank you enough. Keep up with your brilliant work!
@dirkstaszak4838
@dirkstaszak4838 2 жыл бұрын
Again thank you for this valuable service 🙏
@GosCee
@GosCee 2 жыл бұрын
Great stuff! Thank you, Chandeep.
@jimmy3862
@jimmy3862 2 жыл бұрын
I'm in love of Chandeep! Amazing way to explain things. Brilliant job and contribution. Thank you very much from Brazil.
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
Thank you for your inspiring words Jimmy! 💚
@androo235
@androo235 Жыл бұрын
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.
@wayneedmondson1065
@wayneedmondson1065 2 жыл бұрын
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!!
@ValerioParini
@ValerioParini 2 жыл бұрын
Following you since the begining, a big thank you for sharing all your knowledge!
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
My pleasure!
@ride4food
@ride4food 2 жыл бұрын
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
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
Glad you like them!
@harmeetchawla3708
@harmeetchawla3708 Ай бұрын
Thanks for this. Please share how can we make multiple header table in Power Bi,that will be very helpful.
@andrewwalker5974
@andrewwalker5974 2 ай бұрын
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.
@Xploit_GG
@Xploit_GG 2 жыл бұрын
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
@sebastienschoonjans9727
@sebastienschoonjans9727 2 жыл бұрын
He is not goodly but godlike !! Thanks 👍
@santoshpv321
@santoshpv321 Жыл бұрын
It's so easy to follow....Thank you so much.
@GoodlyChandeep
@GoodlyChandeep Жыл бұрын
Glad you like it!
@michaelmays9415
@michaelmays9415 2 жыл бұрын
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)
@SeneXeL
@SeneXeL 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
@mmbcampus
@mmbcampus Жыл бұрын
Wow... Great tutorial
@user-gy2md6dc9p
@user-gy2md6dc9p Жыл бұрын
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.
@GoodlyChandeep
@GoodlyChandeep Жыл бұрын
I agree.. The only problem for larger datasets is that transpose is an expensive operation.
@husseinmahmoud5210
@husseinmahmoud5210 2 жыл бұрын
This is MAGIC!
@tlsrinivasan
@tlsrinivasan 2 жыл бұрын
Awesome. Thank you . Looking forward to more complex Mcode problems.
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
Glad you like it!
@sajalnagar2128
@sajalnagar2128 Жыл бұрын
This is awesome..thanks Chandeep
@GoodlyChandeep
@GoodlyChandeep Жыл бұрын
Glad you like it!
@pravinshingadia7337
@pravinshingadia7337 2 жыл бұрын
Thank you my friend - another great video.
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
Glad you like it 💚
@phoolkhan87
@phoolkhan87 2 жыл бұрын
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?
@juja2819
@juja2819 Жыл бұрын
Great video, thank you! :) Can i ask have you find solutions for merged cells?
@frankschadler9407
@frankschadler9407 2 жыл бұрын
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
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
Awesome.!
@thomaseckersley5026
@thomaseckersley5026 5 ай бұрын
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.
@martyc5674
@martyc5674 2 жыл бұрын
Brilliant content- really helpful stuff. To solve the merge issue, could you transpose the small headers table and fill down?
@martyc5674
@martyc5674 2 жыл бұрын
Actually I see now why that wouldn’t always work..
@leosaghathan2895
@leosaghathan2895 Жыл бұрын
Thank you Chan deep for the video. Insert.row code showing error what could be the issue, could you please advice
@Everyonelovesyou
@Everyonelovesyou 2 жыл бұрын
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.
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
I agree, just that transposing a large data could be very performance expensive.
@Everyonelovesyou
@Everyonelovesyou 2 жыл бұрын
@@GoodlyChandeep Yes it is indeed performance costly ... I had it in mind, glad you added something new once again.
@SamehRSameh
@SamehRSameh Жыл бұрын
Marvelous M code
@eslamfahmy87
@eslamfahmy87 9 ай бұрын
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.
@jerrydellasala7643
@jerrydellasala7643 Жыл бұрын
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!
@williamarthur4801
@williamarthur4801 Жыл бұрын
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.
@hojatalaii8360
@hojatalaii8360 6 ай бұрын
How to calculate max consecutive negative number in a column in power query?
@juanpaolo21yt
@juanpaolo21yt Жыл бұрын
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😃😃
@srigadamanoj7487
@srigadamanoj7487 2 жыл бұрын
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
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
Zoomit - from Microsoft. it's free
@sebfromgermany3819
@sebfromgermany3819 2 жыл бұрын
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
@larmondoflairallen4705
@larmondoflairallen4705 2 жыл бұрын
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.
@emilmubarakshin49
@emilmubarakshin49 9 ай бұрын
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.
@aka3741
@aka3741 Жыл бұрын
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.
@ing.luisfernandoortizmaldo1029
@ing.luisfernandoortizmaldo1029 2 жыл бұрын
Great job!. One question please; What software do you use to make this awsome videos?
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
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
@rajnishrajput2367
@rajnishrajput2367 2 жыл бұрын
@@GoodlyChandeep Thanks for both, video and making procedure..keep up the great work
@suyashsharma5988
@suyashsharma5988 2 жыл бұрын
i am interested in dax beginner course. can you explain abt course. is it a video based course or how...
@hemamaleni
@hemamaleni 2 жыл бұрын
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.
@manuelcarvalho3232
@manuelcarvalho3232 10 ай бұрын
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
@UlyssesHaq
@UlyssesHaq 2 жыл бұрын
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.
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
Thanks Man.. I am glad you found the content helpful.
@seemachavan3790
@seemachavan3790 Жыл бұрын
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
@thierryprigent2225
@thierryprigent2225 11 ай бұрын
Isn't it easier to update the headers directly without inserting Rows? Thanks
@carolshipley7903
@carolshipley7903 Жыл бұрын
Hi. My attempt to concatenation these two rows is - transpose, then merge the two columns and then transpose back again
@GoodlyChandeep
@GoodlyChandeep Жыл бұрын
Just that transposing and pivoting slows down the query on large data :(
@sattimama
@sattimama 2 жыл бұрын
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.
@MrKalivaradhan
@MrKalivaradhan 2 жыл бұрын
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
@musthakhahammed6535
@musthakhahammed6535 2 жыл бұрын
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
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
You just need to fill down the first 2 rows of data. Rest remains the same
@vashisht1
@vashisht1 2 жыл бұрын
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
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
See the function in blog comments from mma173
@ninamattoon747
@ninamattoon747 2 жыл бұрын
my data workbook (Source) has multiple Sheets what is Mcode for this added complexity...also 3 rows of header name(ugh)
@m.bouguerra
@m.bouguerra Жыл бұрын
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
@vishwanathmadham9272
@vishwanathmadham9272 2 жыл бұрын
Better to Remove first two rows . Rename the column .
@pakipower
@pakipower 2 жыл бұрын
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?
@GoodlyChandeep
@GoodlyChandeep 2 жыл бұрын
Query Performance :) Transposing a large table will kill the query
@saratoffice
@saratoffice 2 жыл бұрын
I think you are right!
@gavonak2841
@gavonak2841 2 жыл бұрын
@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.
@williamarthur4801
@williamarthur4801 Жыл бұрын
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.
@GoodlyChandeep
@GoodlyChandeep Жыл бұрын
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.
@williamarthur4801
@williamarthur4801 Жыл бұрын
@@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.
@pardawala_Bhiwandi
@pardawala_Bhiwandi 2 жыл бұрын
Ist seen
Expand Columns to Multiple Rows - Power Query Challenge
2:40
Convert Multiple Column Groups to Rows in Power Query
17:18
Useful gadget for styling hair 🤩💖 #gadgets #hairstyle
00:20
FLIP FLOP Hacks
Рет қаралды 9 МЛН
How Many Balloons Does It Take To Fly?
00:18
MrBeast
Рет қаралды 197 МЛН
НРАВИТСЯ ЭТОТ ФОРМАТ??
00:37
МЯТНАЯ ФАНТА
Рет қаралды 8 МЛН
Split by Variable Columns in Power Query
13:38
Goodly
Рет қаралды 28 М.
5 Tricks to Reduce Steps in Power Query
16:41
Goodly
Рет қаралды 33 М.
How to Rename Column Headings with Power Query - the quick automated way
11:31
Handling two header rows with Power Query - also applies to Power BI
9:50
Копия iPhone с WildBerries
1:00
Wylsacom
Рет қаралды 8 МЛН
low battery 🪫
0:10
dednahype
Рет қаралды 1,4 МЛН
Проверил, как вам?
0:58
Коннор
Рет қаралды 189 М.