Dynamically Expand All Columns from Multiple Tables in Power Query
HTML-код
- Опубликовано: 28 июн 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
- - - - My Other 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...
- - - - Blog + Download - - - -
www.goodly.co.in/expand-all-c... 
Наука
This is precisely what I've been looking for! Chandeep, you are the man!
My god, this is really awesome! I never thought there is such an elegant method to retain all columns w/o actually knowing them. Thanks so much for sharing this.
Your videos are fantastic, they solve some really unique problems. Really appreciate your posts, learning a lot. Thank You
Amazing. You answered all my questions step by step. Great video. Thanks.
I just had the same issue that was resolved by your solution. Thank you so much for sharing!
This video should be a ready reference for all those starting to use Power Query almost everyday. Thank you!!
Because the UI of PowerQuery is so good, some of us normally don't go beyond the functions mentioned in Home, Transform & Add Column Tab.
Agree!.
For a very long time to most power query users, the UI stays more than sufficient. But as one progresses, these little limitations creep in. M just takes power query to the next level!
Nice video. In my case, I would usually insert the [Name] into the [Data] before using Table.Combine like below
InsertName =Table.AddColumn(PrevStep, "Data2", each Table.AddColumn([Data], "Name", (x) => [Name]))
Combined =Table.Combine( InsertName[Data2] )
Using this technique, you can even insert multiple columns beforehand like below
InsertNameKind =Table.AddColumn(PrevStep, "Data2", each Table.AddColumn([Data], "Name", (x) => [[Name],[Kind]]))
This time, a list of record will be inserted and after using Table.Combine, you'll have to expand the record to pull both [Name] and [Kind].
Cheers
Make a video or blog for this comment. Will be super helpful.
Wow. That is so cool. Power Query is saving our trial teams at work so much time, but giving me more work setting them up LOL. I can see myself using this some time in the future. Thanks so much
Thank you so much! I've been struggling for months trying to figure out how to do what you showed us starting at the 2:50 mark within your video. Who knew it was as simple as clicking on the gear icon! I was trying to write in the query and it was failing each time I refresh my data.
Excelente conteúdo!!! Parabéns...
This was the best thing I've ever seen/read on pq. So helpful! Many thanks!
Glad it was helpful 💚
Absolutely love your videos. You cover really great ideas to constantly improve our skills - thank you!
Glad you like them!
Chandeep, your videos are amazing; your teachings are excellent; you are doing an awesome job . . . thank you for sharing your knowledge in such an impactful way.
Thanks a ton
fantastic video on so many levels! Cheers!
Very useful.. Thank you very much you made my life easy ... Appreciated !!
Works like charm! Thanks heaps!
Your videos have been immensely beneficial. In this particular video, you've provided valuable insights on how to include all columns by default in Power Query. I'm curious to know if there is a method for customizing the default column data types individually?
very good knowledge and interactive class
Thank you very much. This video helped me a lot.
Love your videos, very logic and easy to understand, will absolutely watch all the related videos one by one
Every video has a downloadable file. Don't forget to download and practice :)
I have visited many channels but I got knowledgeable tutorials only on this channel.
Hi Chandeep - Excellent, this is really a very useful trick and I was able to use this in my work. - Keep doing good work ! thank you.
Awesome ! Very useful one when loading data from folder or files as well.
Table.Combine step should be followed after promoted headers even in case of single data source.
Thank you Chandeep @Goodly for sharing your knowledge for the benefit of everyone here !
Glad it was helpful 💚
You're simply incredible
Gamechanger! Wish I had found this earlier!
YOU SAVED ME! You got a new subscriber, for sure! Thanks man!
Thanks for the sub 😉
Very nice.
Could you pls tell if there is a way to fix the colum width in the query itself? IN other words, if a column header is 25 pixels wide in the source data, can I change it to resize to 33 pixels wide? Or make its contents wrap/or not wrap? Is there any such option?
Note: I already know that I can make a setting so that my original/source column width settings remain in effect and force PQ 'refresh' command to not allow to resize the columns width.
I spent weeks searching for this as my cols change often. all this while and solution right in front of me.. thank you very much. you saved me :)
Glad it helped!
Hi Chandeep. Nice one.. very clever! Thanks for sharing these techniques. Will study them with great interest. Thumbs up!!
Thank you! Cheers!
Bro! I love all your videos. You are very methodical and your topics are always relevant.
I am knew to PQ-PBI and what I love is how it is so logical . The only reason I am not good is not for lack of understanding but I don’t do a lot of exercise.
Glad you like them!
Very nice videos, pls keep uploading!
I love your tutorials. One question though table.combine vs table.expandtablecolumns would this be different performance wise?
In the meantime your tutorials reached out to Bavaria. Thank you so much for sharing 😉
That was a fantastic tutorial!!! thank you!
Glad you enjoyed it ,Meredith !
it's very useful thank you so much. How about if we wanna get data from multiple excels, or csv files ?
Chandeep, thank you! Very well explained. It helped me a lot.
Glad it helped
Thank you... Fantastic Tuturial ! 😀
Glad you liked it!
Amazing video Chandeep.. Need more advanced videos on this extracting tables, lists, records, etc. dynamic col names, functions etc.
Have planned a video on maneuvering tables in Power Query. Should be out soon!
Thanks for the suggestion :D
See these videos until then
Dynamic Column Names - ruclips.net/video/kwf-E3a3aOE/видео.html
Dynamic Data Types - ruclips.net/video/lD-7Tt8-oOk/видео.html
Very informative! I have one small question...If we remove one column from the source data and refresh the query it will throw an error. Is there any dynamic way to solve this issue?
Hi Chandeep ! Amazing video!!
I have 2 queries .
1. Deleting a column from the source throws an error
2. Change Type hardcodes all the columns
Appreciate your help !
My favourite Power Query Channel. Good stuff brother
Glad you like Goodly! 💚
Hello Chandeep, hope you are in good health. in this tutorial you've provided the accurate solution for the problem that has engulfed me for few days, so thank you very much, and keep making such amazing videos to share your knowledge and skills to broaden the prospect of excel for people like me. thankyou and lots of prayers and good wishes for you and your family (:
Thanks Bro!
I am glad it helped:)
@@GoodlyChandeep Hello Brother, hope you are in good health. do you have a DAX course for beginners on your channel? actually brother I've just started to learn DAX and your way of teaching is very easy to understand because you elaborate the concepts very brilliantly. thank you brother (:
Hi Chandeep.
Thanks for your sharing.
It's very useful for me.
Would you mind helping me the solution when expanding all columns but number of columns is a variable?
I'm looking forward to hearing from you.
شكرا جزيلا
Life hack!!!!! Awesome!!!
Arrange power query videos in an ascending or descending order ... I want to study them from oldest to newest....
And upload more videos on power query m langauge as well as focus on dynamic areas in power query.
Awesome explanation!
Thanks Michael!
It was really so cool.....
Very helpful thank you
Glad it was helpful!
Hi Sir,
I have an XML file with multiple tables and multiple columns in those table. Could you please create a video on expanding entire data in one shot
Bravo!! Very interesting
Agreed!
This is great!! thanks Chandeep. Can I have a question: what if I need t remove all columns not mapped i.e all columns names that start by "Column". I was able to dele only one :(
What if we first create a new List column = Table.ColumnNames(). Then List.Distinct(List.Combine(NewColumn)). This also will give us the full list of all column names.
Will it work faster then Table.Combine?
Power Query has lazy evaluation - I am suspecting that Power Query won't execute Table.Combine (even though we have written it) because it only needs to extract the column names eventually. But I really need to check this.. thanks for suggesting.
It's useful. I tried to change my query for this dynamic expand. I do not use your step to promote headers. My file for conversion is PDF statement. I did over 35 steps of transformation to get only tax code, amount and category of payment. I tried to apply the solution to expand dynamically. But I found that the next step to merge all the colums into one stopped to work. 1st problem is that column names are hard coded and the second problem is that it says I'm referencing to the column that is not existing. I don't understand how to correctly merge dynamically expanded columns and how to adjust my other steps in the query to stop giving this errors.
Hello Sir, this is the good one.
Sir I have taken your DAX course.
And planning to enroll for power query.
Do also give training on M Language in same Power Query training
Hope you found the DAX Course helpful.
Power Query Course - goodly.podia.com/power-query-course
The link to pay in INR is at the bottom of the page. Let me know if you have any questions.
Thanks
Awesome videos thank you,
Small query
I have two files ,column names has date value as March 1 to March 31 and other file has April 1 to 30,
when I do promote headers ,one file getting headers are promoted and other file headers are showing are column1 2 3.. so on
Great one
Would this work with 'From Folder' and utilizing the 'Date created' from each in a column?
This sould be the default expand tablet process for power query
Hello Goodly, I enjoy your videos. I need some help with what I am trying to do. I am trying to create a power query with 2 tables to merge them and see the change between the 2 files. I don't know how to do it. I have been with this for hours.
Amazing
very good
All topics are so useful... But dialogue delivery is very fast Paji 🎉🎉🎉🎉🎉
Thank You !
Can you please explain how to merge and add multiple columns in power query dynamically. Means adding data should reflect beside the main data with matched criteria.
Good morning sir , Calculate visual showing number of trips made by a vehicle during morning, noon,
evening and night time using tripID field and Datetime column
Thanks for the Great Video! Now trying to merge all Columns dynamically, with a delimiter, but ignoring null values.
I Tried different methods but the one that i am working right now is :
= Table.AddColumn(#"Colunas Removidas2", "Personalizar", each Text.Combine(List.Select(NomesColunas, each _""),"|"))
But what happens is that I get the names of the Columns merge ... and I want the values of each row.!
Thanks from Brasil!
💯👍 excellent...
Thank you 👍
Perfect explanation, How do I append data from multiple excel sheets but with changing column names and order. Some of the column names are shifting to other columns in different sheets, more so the number of columns ain't the same in all sheets
ruclips.net/video/0ZOY8is-bgY/видео.html
I am assuming that the table.combine function matches the column headers in all the tables and combines the date in columns where there is an exact match in the headers. However, if for some reason some of the column headers in the different tables were not an exact match, for example the the date column has "DATE" header in Sheet 3 while the date columns in the other two sheets have "Dated" as header, the combined table would have two columns called "DATE" and "Dated". Is there a function that lets us map the various headers in each table?
You'll have to fix the column names first and then combine the tables.
here is a video to watch - ruclips.net/video/kwf-E3a3aOE/видео.html
What's really looking for so far
I like Table.Combine at 5:45 and the Name of the sheet at 8:00
What can be done to remove duplicate values from a list? I can not find anything on the web for it.
Hi Chandeep,
It is nice video and I want to know how you get list of name of function i.e arrtibute like Table.PromoteHeader , Table.AddColumnwhile coding in Power query. In my case of I wont get such attribute.
Create a new step with the following code =#shared
I want to remove blank rows, but adding the sheet name means the row is no longer blank, so the normal step doesn't work. How can we change it to make it work? Thanks for the tutorial.
This technique seems slightly different than the ones you used before. For example, you used to not use UI drilldown to get a column as a list . Why the change ?
Hi sir, could you please let me know your Excel version? Because there is no functions like code coloring or code auto filling in my version, thank you.
how to prevent table. Combine from automatically changing the data type.
11:51 magic. I thought "Columnnames" was a list. It's not. It's a formula!
I've done this but lost the headers. Wha have I done wrong?
Hi, I'm not getting the Function intellisense when typing function in formula bar. Can you please help me out?
You need to update your Excel version
@@GoodlyChandeep I have updated my Excel version to 2019. The intellisense is showing in Power BI desktop but not in excel. Please help me out from this problem. as without the intellisense this is very hard to work with power query.
genius！
Thanks 😊
I am importing data from the particular folder data . when i am doing above steps. it is giving error message "Expression.Error: We cannot convert a value of type Table to type List.". Please help this .
This does not work for me, combining the columns gives an error.
This is Excel specific and not powerbi query playlist
It is tough to match your speed but good to follow your trail! 😁😁😁😁
Amazing