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
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!
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.
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.
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.
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 !
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 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.
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 (:
@@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 (:
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.
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 !
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
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?
Thank you. I refer to this video a lot. Thank you. Unfortunately, Table.Combine sometimes loses the data type. To change the type requires hardcoding the column names. That's what we were trying to avoid in the first place. Any suggestions?
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.
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.
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.
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?
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 :(
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.
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?
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
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.
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.
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.
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
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
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.
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!
@@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.
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 ?
Hello. Excellent tutorial, as always. I would like to ask what to do in case every table has information but for the same dates, i mean, the first one has the sales information sorted by a Timestamp column, the second one has the clients information sorted by a Timestamp column and so on. The technique you souw in this video is awsome, but not suitable for those cases. I think the right way to procced is to use List.Accumulate and combine all using the timestamp column (i did it) but the recursive expansion of the column is a nightmare. I was not able to find a solution. Im asking for help if anybody had the same problem and found a solution.
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
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.
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 is precisely what I've been looking for! Chandeep, you are the man!
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.
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!
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.
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!
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
This was the best thing I've ever seen/read on pq. So helpful! Many thanks!
Glad it was 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
Your videos are fantastic, they solve some really unique problems. Really appreciate your posts, learning a lot. Thank You
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.
My favourite Power Query Channel. Good stuff brother
Glad you like Goodly! 💚
YOU SAVED ME! You got a new subscriber, for sure! Thanks man!
Thanks for the sub 😉
I have visited many channels but I got knowledgeable tutorials only on this channel.
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.
Amazing. You answered all my questions step by step. Great video. Thanks.
Hi Chandeep. Nice one.. very clever! Thanks for sharing these techniques. Will study them with great interest. Thumbs up!!
Thank you! Cheers!
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 :)
Very useful.. Thank you very much you made my life easy ... Appreciated !!
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 💚
Chandeep, thank you! Very well explained. It helped me a lot.
Glad it helped
sir mashallah,,,,,,,,,,,,,try to promote all your effort in urdu........it will be great work for urdu people.........jazakallah
fantastic video on so many levels! Cheers!
Gamechanger! Wish I had found this earlier!
I just had the same issue that was resolved by your solution. Thank you so much for sharing!
Amazing Tricks, thanks chendeep
That was a fantastic tutorial!!! thank you!
Glad you enjoyed it ,Meredith !
In the meantime your tutorials reached out to Bavaria. Thank you so much for sharing 😉
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 nice videos, pls keep uploading!
Absolutely love your videos. You cover really great ideas to constantly improve our skills - thank you!
Glad you like them!
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.
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 (:
Thank you... Fantastic Tuturial ! 😀
Glad you liked it!
Would this work with 'From Folder' and utilizing the 'Date created' from each in a column?
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!
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 !
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
very good knowledge and interactive class
Thank you very much. This video helped me a lot.
You're simply incredible
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?
Thank you. I refer to this video a lot. Thank you. Unfortunately, Table.Combine sometimes loses the data type. To change the type requires hardcoding the column names. That's what we were trying to avoid in the first place. Any suggestions?
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.
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.
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.
Works like charm! Thanks heaps!
I like Table.Combine at 5:45 and the Name of the sheet at 8:00
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?
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 :(
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.
it's very useful thank you so much. How about if we wanna get data from multiple excels, or csv files ?
This is just awesome!
Awesome explanation!
Thanks Michael!
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
I love your tutorials. One question though table.combine vs table.expandtablecolumns would this be different performance wise?
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
All topics are so useful... But dialogue delivery is very fast Paji 🎉🎉🎉🎉🎉
Thank You !
What can be done to remove duplicate values from a list? I can not find anything on the web for it.
Excelente conteúdo!!! Parabéns...
Bravo!! Very interesting
Agreed!
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
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.
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.
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
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
It was really so cool.....
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.
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!
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.
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 ?
Very helpful thank you
Glad it was helpful!
Hello. Excellent tutorial, as always.
I would like to ask what to do in case every table has information but for the same dates, i mean, the first one has the sales information sorted by a Timestamp column, the second one has the clients information sorted by a Timestamp column and so on. The technique you souw in this video is awsome, but not suitable for those cases.
I think the right way to procced is to use List.Accumulate and combine all using the timestamp column (i did it) but the recursive expansion of the column is a nightmare. I was not able to find a solution.
Im asking for help if anybody had the same problem and found a solution.
11:51 magic. I thought "Columnnames" was a list. It's not. It's a formula!
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
💯👍 excellent...
Thank you 👍
What's really looking for so far
Great one
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.
How to apply 2 headers for each column
This sould be the default expand tablet process for power query
Life hack!!!!! Awesome!!!
Superb
I've done this but lost the headers. Wha have I done wrong?
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.
very good
Amazing
how to prevent table. Combine from automatically changing the data type.
Amazing
you are using power service!
This is Excel specific and not powerbi query playlist
genius！
Thanks 😊
It is tough to match your speed but good to follow your trail! 😁😁😁😁