I don't understand why this channel isn't bigger, seeing that the solutions he gives is by far explained in such a way that most people understand. Keep up the good work Sir.
I am sure this is helpful to the excel experts. As a layman, I wanted step by step solution. Like starting right with the mapping tables. Everything was already created earlier, so i could not understand anything.
Good solution, I like it What I can point out as improvement is the stability issue that you pointed out at 16:47 Indeed, you need to redo all of that because the table will store records which are not absolute, but some kind of position oriented based on content of the query behind it. I would say this is generally not a good practice to combine query generated table and manual input. Imagine you have multiple tables like that - it is extremely hard to control and spot the difference between them as at least they would be of the same color. My solution to such stability issues: 1. load the query table, but remain it intact - do not add anything to it manually 2. create another table manually and use different color theme (blue for example) so it is different from the query format 3. name this table and its columns absolutely same way and copy the query table content to your manual table - at this point you will have two tables which will be identical for its content 4. create data validation in manual table columns referring to the query table columns as a dynamic range using =INDIRECT("Table1[Column1]") string - you need to do it column by column 5. extend the manual table with new columns that you need - "rename" column in your example 6. add the controls through LEFT and RIGHT OUTER joins between the original query table and manual blue table to check the consistency - i.e., that you did not forget to include all of the entries to your manual table. Outcome of joins can be count or non-blank count that produces outcome of a check table with a single cell 7. merge the manual table with other query tables inside the query editor to achieve your goal Yes, it is much more than shown but it gives your control and automated ways to check the correctness of a process That's critical for a repeated exercise Thanks again!
Good solution! I was also going to suggest a use of a manual table but didn't think of the data validation idea. Perhaps, your Step 6 can be an "exception" list which would display any items not present in the manual table so that it is clear what item(s) need to be added. Maybe with a dynamic array like this: =FILTER( OrigTbl[Column], ISERROR( XMATCH( OrigTbl[Column], ManualTbl[Column] ) ), "Mapping is complete" )
I have just found this channel, and amazed with how good you explained the logic so that we can understand and apply the solutions better. Two thumbs up plus full of stars ⭐️ ⭐️⭐️⭐️⭐️
Many thanks Chandeep, For mapping list of lists: It will be better to use Table.ToRows only Instead of table.Transpose + Table.ToColumns Regards Mohammed from Algeria.
Agree, Outstanding. NOTE: The Sales Data folder in the Zip file has two temporary files - names starting with ~ that are hidden and causing an error in the M Code. Just delete them to fix it. Also, change the folder path in the FolderLocation query, not the Data Source.
Wow that was hard work!! I used this on a folder full of csv files!! I followed you step by step and....it worked...OMG How do you know this stuff!! i would have never figured this out! thantks
Thank you very much. I was working on such exercise since last 2 weeks and struggle a lot , referred many You tube videos but.... Hats up to you dear ...which helped to proceed further ..Very well explained ....
Wanted to let you know this proved useful to me. My case was far more complex and I had to debug somethings...but it worked! Saves me like 90 minutes of work.
can you explain the step how you open your column headers from PowerBI in Excel? Dont find an option that I can execute the Data-> From TableRange (on min. 9:04) that its loading the data into the same PowerBI instance. thank you and its a great video
You cannot. Instead you can one query running in excel to manage the headers. And the second query can pull that data from excel to transform the headers in Power BI
This really helps me out a lot honestly! I had been checking here and there but nowhere to be found the best way out of my problem till i checked this video out! Amazing❤❤❤
Hi awsome lessons, would you please advise if I have multiple nested tables and each table with 20 plus columns and I need to get only specific columns and also allign colunm names like you did in this video; how to achieve that
6:17 Thank you for the solution. I still have a problem, my file's headers are in the second row of each file, so when I use this method, the column names are Column1, Column2, Column3 and so on. How can I remove the first row of each file in the folder before this step?
Very good! For the mapping table, why not detach the auto listing names with a model link to a static table, the one to be manually updated? It is possible? That way can you bring to power pivots and graphs even more than one name, as translations... What do you think?
Really the videos that you post would be very helpful to those who works in the corporate world especially who into data and makes their job much easier. Thanks for your efforts and wish your dreams come true and achieve heights.
I have applied the learning from this video to the task that I need completing and it works a treat, thank you Chandeep. One quick question. I have no problem doing this when I use Power Query in Excel. How do I do the same when I use Power Query from within Power BI please? Specifically, how do I load the "mapping" list from Power BI into Excel, so I can generate the "rename" column before loading the table back from Excel into Power BI to generate the "Renames" query? Thanks
I like it, small hiccup for me, i am attempting combining 3 files and good until 15:15 in the video after executing Table.Combine (#"Added Custom1"[Data]) and hitting enter. It seems to ignore the renames that I can see in the proceeding step, by clicking beside each table. Any idea of where I need to look? Checked Rename file and no apparent issues there. Thank you in advance.
Your videos are brilliant and you're so inspirational. You make it easy to understand the logic and then the code to apply the logic. This video has really helped me and I'd like to say a big THANK YOU !
This was great as always. I have been doing this another way without Table.Combine and List function.I need to wrap my head around Table.Combine and List as this would make it more dynamic.
I will add this to my favorites. I have seen many techniques to combine inconsistent column names. I like this one. The list accumulate method I do not understand or at least can't remember each time I need it. But this method is memorable.
Hey Chandeep, thanks for the really good videos. It's really fun to listen to you and above all you explain everything really well. Keep up the good work!
Very good, it’s a tricky method! I try it with csv files and it failed because of the excel workbook function. I’ll look for on my own but maybe you’ve already solved this problem. Thanks
hello, I have excel files with secondary sheets containing notes (not needed in the actual data table). @ around 4:30, is there a way to exclude these sheets from the list? Also, would like to learn how to Add a column for each table indicating the source file, so i can put a slicer on the visualization. thank you so much!
You are a MASTER of excel. Glad that I found your channel and is really informative/educational and highly useful. This channel deserves a millions of subscribers 🎉❤😊
I'm going to try this in my next assignment! Thank you! For the potential risk of wrongly aligned mapping table after a new file with new headers getting added, I wonder if a better way of doing it is to create a mapping table to be left joined to the dynamic column header table? It will still shows empty if a new table with new header gets added, but at least it won't map to the wrong thing after refreshed.
This is super and is of big help!! Thank you!! Just wanted to know can we add the files names column at the end as sometimes we have to collate the data of different region, months, companies which will be very difficult to segregate without classifying.
Hi there, thank you for this! What if the Excel sheets/files have different numbers of columns, which of course will have names not common to the other data sources/files?
amazing, thanks for detailed explanation, very easy to understand each step. Can you guide when new excel is added to source folder? How can those data automatically get loaded to Combined File?
This is great, what if I wanted to get the columns from two other sheet side by side to each other. Eg. Sheet 1: col1 | col2 Sheet2: col3 | col4; Sheet3: col5 | col6 I want the output to be col1 | col2 | col3 | col4 | col5 | col6 In power query.. your inputs would be helpful. Looking to hear suggestions from others as well.
Hi. If the records in the columns you are referring to are not related, you can make 3 queries, one for each sheet, and convert each table into a list using table.tocolumns. Here you have 3 lists in 3 queries. Next, you should use list.Combine, combining these 3 queries, and finally use the table.fromcolumns function to convert into a table again. On the other hand if they are related, you should use merge querys in the ribbon of power query editor
Hi, thank you for such a clear explanation. I hope you can help, not many people talk about this, but can we load each table as a separate data table? Or is it possible to horizontally combine all the tables if the date column is the same in all tables loaded here?
Sir, I posted this question previously too. How to deal with filter function applied cells to form a table and sorting values using Power query. Another question is about using using sort function alone for two adjacent columns with filter function applied cells...
Thanks for the great video. It helped solve an challenge I have tried to solve for too long. What is the best way to solve if the table you are querying has headers on the 2 or 10th row vs the 1st row?
Thank you Chandeep for this lovely video. Is there a way you can share a link to the video (if you have made) where we do merge different tables from sharepoint with different headers.
Hi Chandeep, Thanks for the clear session, Can we use this file location & Mapping table as base data and refresh using other files from same location and removing the old files, Can it give the same output.
Thanks a Ton Chandeep!! Great help, awesome explanation. I found a better way to do this as follows courtesy "Excel Off The Grid": =Table.ExpandTableColumn(PreviousStep, "Data", Table.ColumnNames(Table.Combine(PreviousStep[Data])))
This video is super helpful as are all the videos. I've learned so much from this channel. Question I have, in this video you create a custom column to create a table so we can see all the sheets within a file. Is it possible to filter to a particular sheet at this step BEFORE expanding the custom column? I have multiple excel files and each has mutliple sheets. One sheet on each file is over 600,000 rows, and I'd like to avoid loading that sheet when i click expand.Is this possible to do?
When you are merging sources to update main sheet, is there a way to prevent "incoming" duplicates rows from overwriting what I already have? My goal is to be able to update an excel sheet with sources of data that sometimes have additional headers or not in the same order.
wow, generating a list of all distinct column names for mapping is really genius. I also noticed that you faced the same problem of power query inserting rows in partial columns when query is refreshed which messes up the table. I got around this problem by using only VBA to refresh and disabling Explicit refresh. I don't think Microsoft is ever going to fix that.
Great solution Chandeep 👌 Could you please show solution for xls (not xlsx) files ? Looking for ways to automated conversion to xlsx or way to combine and use xls :)
I have a similar problem, but I want to Append data in Table 1 with data from Tables 2 and 3. The data in Tables 2 and 3 is in a different column order than the data in Table 1. I want to write a script so that a person will label each of those columns with consistent header names for the subsequent Power Query. For your example, the "Mapping" column names wouldn't have consistent names... this is mostly because my coworkers hate me.
Goodly, what if instead of taking data from a folder, i want this to be sheets in same excel..how to make it queries from same excel file? Shall I select from table range ?
![ประสบการณ์ จ้าง Developer ที่ผมจะไม่มีวันลืม [ภาค2]](http://i.ytimg.com/vi/pZbsSDRrmXA/mqdefault.jpg)
I don't understand why this channel isn't bigger, seeing that the solutions he gives is by far explained in such a way that most people understand. Keep up the good work Sir.
Glad you think so!
Very true, I learnt about this channel few weeks back only. Great!!
absolutely true, i too believe this.
I can't agree more!! Great work delivered seamlessly
I agree. Goodly is Godly in what is he doing. Helped me many, many times
I am sure this is helpful to the excel experts. As a layman, I wanted step by step solution. Like starting right with the mapping tables. Everything was already created earlier, so i could not understand anything.
Good solution, I like it
What I can point out as improvement is the stability issue that you pointed out at 16:47
Indeed, you need to redo all of that because the table will store records which are not absolute, but some kind of position oriented based on content of the query behind it. I would say this is generally not a good practice to combine query generated table and manual input. Imagine you have multiple tables like that - it is extremely hard to control and spot the difference between them as at least they would be of the same color.
My solution to such stability issues:
1. load the query table, but remain it intact - do not add anything to it manually
2. create another table manually and use different color theme (blue for example) so it is different from the query format
3. name this table and its columns absolutely same way and copy the query table content to your manual table - at this point you will have two tables which will be identical for its content
4. create data validation in manual table columns referring to the query table columns as a dynamic range using =INDIRECT("Table1[Column1]") string - you need to do it column by column
5. extend the manual table with new columns that you need - "rename" column in your example
6. add the controls through LEFT and RIGHT OUTER joins between the original query table and manual blue table to check the consistency - i.e., that you did not forget to include all of the entries to your manual table. Outcome of joins can be count or non-blank count that produces outcome of a check table with a single cell
7. merge the manual table with other query tables inside the query editor to achieve your goal
Yes, it is much more than shown but it gives your control and automated ways to check the correctness of a process
That's critical for a repeated exercise
Thanks again!
Good solution! I was also going to suggest a use of a manual table but didn't think of the data validation idea. Perhaps, your Step 6 can be an "exception" list which would display any items not present in the manual table so that it is clear what item(s) need to be added. Maybe with a dynamic array like this:
=FILTER( OrigTbl[Column], ISERROR( XMATCH( OrigTbl[Column], ManualTbl[Column] ) ), "Mapping is complete" )
I have just found this channel, and amazed with how good you explained the logic so that we can understand and apply the solutions better. Two thumbs up plus full of stars ⭐️ ⭐️⭐️⭐️⭐️
Thanks so much for the kind words! I'm glad you're enjoying the channel.
Thank you for this tutorial...it's the perfect solution to the issue we are having with 100+ columns.
Bravo!!
Yaaahoo!! 😁
Many thanks Chandeep,
For mapping list of lists:
It will be better to use Table.ToRows only
Instead of
table.Transpose + Table.ToColumns
Regards
Mohammed from Algeria.
Thank you Chandeep. This is a common challenge faced while working with different data sets.
Excellent solution to a recurring problem. Greetings from Chile
Thank you!
Agree, Outstanding. NOTE: The Sales Data folder in the Zip file has two temporary files - names starting with ~ that are hidden and causing an error in the M Code. Just delete them to fix it. Also, change the folder path in the FolderLocation query, not the Data Source.
Wow that was hard work!! I used this on a folder full of csv files!! I followed you step by step and....it worked...OMG How do you know this stuff!! i would have never figured this out! thantks
I have the same issue but doesn’t work because of the excel workbook function on csv files. Did you change the code? Thanks
You're a lifesaver, I'm just getting started into Query logic and your channel has been tremendously helpful!
I am a very big fan of this channel. Your teaching skill very good that even newbies can easily understand. Thank you very much for this trick.
Thank you for the inspiring words!
Thank you very much. I was working on such exercise since last 2 weeks and struggle a lot , referred many You tube videos but.... Hats up to you dear ...which helped to proceed further ..Very well explained ....
Wanted to let you know this proved useful to me. My case was far more complex and I had to debug somethings...but it worked! Saves me like 90 minutes of work.
Holy shit, this is the first video I have seen of yours. Absolutely insane work, man!
can you explain the step how you open your column headers from PowerBI in Excel? Dont find an option that I can execute the Data-> From TableRange (on min. 9:04) that its loading the data into the same PowerBI instance. thank you and its a great video
You cannot.
Instead you can one query running in excel to manage the headers.
And the second query can pull that data from excel to transform the headers in Power BI
This really helps me out a lot honestly! I had been checking here and there but nowhere to be found the best way out of my problem till i checked this video out! Amazing❤❤❤
wonderful really, I had a tough time playing around inconsistent columns. A big thankyou 🙂
A godly solution to a problem i have been facing for so long....Super.
Glad it helped
Hi awsome lessons, would you please advise if
I have multiple nested tables and each table with 20 plus columns and I need to get only specific columns and also allign colunm names like you did in this video; how to achieve that
Nice , I m being a regular viewer of your channel. keep the good work.
Thanks, Keep watching!
Thank you for another great view. i am struggling to keep source file name in the final output. is that possible to do ...could you please help
6:17 Thank you for the solution. I still have a problem, my file's headers are in the second row of each file, so when I use this method, the column names are Column1, Column2, Column3 and so on.
How can I remove the first row of each file in the folder before this step?
This rocks! Thank you for a great explanation on this topic!
Very good! For the mapping table, why not detach the auto listing names with a model link to a static table, the one to be manually updated? It is possible? That way can you bring to power pivots and graphs even more than one name, as translations... What do you think?
Really the videos that you post would be very helpful to those who works in the corporate world especially who into data and makes their job much easier. Thanks for your efforts and wish your dreams come true and achieve heights.
Thank you for your inspiring words!
Been looking for this recently!
Saved my life! 🎉
Glad I could help!
Hi, thanks for your video, I was wondering if this method could be applied to combine csv files instead excel files.
Very impressive. YOu have an incredibly deep understanding of the data structure
Thank you!
Your videos are immaculate. Thanks, Goodly.
I have applied the learning from this video to the task that I need completing and it works a treat, thank you Chandeep.
One quick question. I have no problem doing this when I use Power Query in Excel. How do I do the same when I use Power Query from within Power BI please?
Specifically, how do I load the "mapping" list from Power BI into Excel, so I can generate the "rename" column before loading the table back from Excel into Power BI to generate the "Renames" query?
Thanks
Very helpful video. Simple solution for a very common issue. Thank you very much for your effort to teach us.
Glad it helped
This is an exceptional tutorial.
This video is really GREAT. Thank you Chandeep for your outstanding way to explain the solutions.
I like it, small hiccup for me, i am attempting combining 3 files and good until 15:15 in the video after executing Table.Combine (#"Added Custom1"[Data]) and hitting enter. It seems to ignore the renames that I can see in the proceeding step, by clicking beside each table. Any idea of where I need to look? Checked Rename file and no apparent issues there. Thank you in advance.
Your videos are brilliant and you're so inspirational. You make it easy to understand the logic and then the code to apply the logic. This video has really helped me and I'd like to say a big THANK YOU !
Glad you like it!
Your vedios r very very good I basic learner will watch all one by one
This was great as always. I have been doing this another way without Table.Combine and List function.I need to wrap my head around Table.Combine and List as this would make it more dynamic.
This was an EXCELLENT tutorial, thank you so much.
Excellent video Chandeep! Thank you very much!
Glad you like it!
I will add this to my favorites. I have seen many techniques to combine inconsistent column names. I like this one. The list accumulate method I do not understand or at least can't remember each time I need it. But this method is memorable.
Hey Chandeep, thanks for the really good videos. It's really fun to listen to you and above all you explain everything really well. Keep up the good work!
Woow. You coverd every thing 😊 by easiest way
Very good, it’s a tricky method! I try it with csv files and it failed because of the excel workbook function. I’ll look for on my own but maybe you’ve already solved this problem. Thanks
hello, I have excel files with secondary sheets containing notes (not needed in the actual data table). @ around 4:30, is there a way to exclude these sheets from the list?
Also, would like to learn how to Add a column for each table indicating the source file, so i can put a slicer on the visualization.
thank you so much!
You are a MASTER of excel. Glad that I found your channel and is really informative/educational and highly useful.
This channel deserves a millions of subscribers 🎉❤😊
I'm going to try this in my next assignment! Thank you! For the potential risk of wrongly aligned mapping table after a new file with new headers getting added, I wonder if a better way of doing it is to create a mapping table to be left joined to the dynamic column header table? It will still shows empty if a new table with new header gets added, but at least it won't map to the wrong thing after refreshed.
Omgggggggg you just saved my life. Thank you SO much ❤❤❤
This is super and is of big help!! Thank you!!
Just wanted to know can we add the files names column at the end as sometimes we have to collate the data of different region, months, companies which will be very difficult to segregate without classifying.
im trying to do this rn, did you find how?
Thanks for the video!. May I know the name of the mic you use to record the videos?
Yeti
Amazing trick. Exactly what I was looking for. Thank you
Would this work when combining CSV with commas as delimiter please?
Hi there, thank you for this! What if the Excel sheets/files have different numbers of columns, which of course will have names not common to the other data sources/files?
amazing, thanks for detailed explanation, very easy to understand each step. Can you guide when new excel is added to source folder? How can those data automatically get loaded to Combined File?
Hi. If you are getting data from folder option in PQ, it is automatic
Brilliant stuff- the best M channel 👌
Thank you !
This is great, what if I wanted to get the columns from two other sheet side by side to each other.
Eg. Sheet 1: col1 | col2
Sheet2: col3 | col4; Sheet3: col5 | col6
I want the output to be
col1 | col2 | col3 | col4 | col5 | col6
In power query.. your inputs would be helpful. Looking to hear suggestions from others as well.
Hi. If the records in the columns you are referring to are not related, you can make 3 queries, one for each sheet, and convert each table into a list using table.tocolumns. Here you have 3 lists in 3 queries. Next, you should use list.Combine, combining these 3 queries, and finally use the table.fromcolumns function to convert into a table again. On the other hand if they are related, you should use merge querys in the ribbon of power query editor
Loved it ! A very smart solution to a very common issue.
Glad you love it !
Hey Goodly! Can explain it the same with dynamic sharepoint folder path, but I have data in subfolders as well.
…?
Hi, thank you for such a clear explanation. I hope you can help, not many people talk about this, but can we load each table as a separate data table? Or is it possible to horizontally combine all the tables if the date column is the same in all tables loaded here?
This guy is pure genius undiluted
Sir, I posted this question previously too. How to deal with filter function applied cells to form a table and sorting values using Power query. Another question is about using using sort function alone for two adjacent columns with filter function applied cells...
Hi Chandeep Your videos are awsome. I have one question how can I add file name as a column in this example.
Thank you so much! Easy and effective solution .
Thanks for the great video. It helped solve an challenge I have tried to solve for too long. What is the best way to solve if the table you are querying has headers on the 2 or 10th row vs the 1st row?
Thank you Chandeep for this lovely video. Is there a way you can share a link to the video (if you have made) where we do merge different tables from sharepoint with different headers.
Great video! You save my day with this awesome tutorial 😁😁
Glad you like this Alvaro!
That was a lovely solution - do you have a feel for performance at scale (say 500k rows +)
I don't see a performance issue since we're only renaming the columns.
Although excel as a source data can be slow in Power Query
This guy blows my mind every time
Thank You!
Hi Chandeep, Thanks for the clear session, Can we use this file location & Mapping table as base data and refresh using other files from same location and removing the old files, Can it give the same output.
Thanks a Ton Chandeep!!
Great help, awesome explanation.
I found a better way to do this as follows courtesy "Excel Off The Grid":
=Table.ExpandTableColumn(PreviousStep, "Data", Table.ColumnNames(Table.Combine(PreviousStep[Data])))
This video is gold. ❤
one of the best tutorial keep on sharing
Glad you liked it!
This video is super helpful as are all the videos. I've learned so much from this channel. Question I have, in this video you create a custom column to create a table so we can see all the sheets within a file. Is it possible to filter to a particular sheet at this step BEFORE expanding the custom column? I have multiple excel files and each has mutliple sheets. One sheet on each file is over 600,000 rows, and I'd like to avoid loading that sheet when i click expand.Is this possible to do?
Thank Chandeep. It's very helpful :)
It is so clever to transpose and convert to list of lists! :)
Good video
If header names of columns are in random order, does it reorder automatically?
This is soo amazingly helpful! Thank you!
Sir, how to learn all these codes/ syntaxes, video and explanation is awesome.
Could you please suggest book or any material for this ?
Excellent video. I don’t think I could manage to replicate this but I found it a useful example of using PQ for wrangling data.
One of the best explanations
Thanks!
i have watched alot videos and you are the best
Hey Chandeep, how do I use this approach if I am using Power Query within Power BI instead of within Excel please? Thanks
Fantastic!!!! Thanks for sharing! Non about this theme question, what tool do you use to "draw" squares in your screen while presenting? 😅
Zoomit
@@GoodlyChandeep Thanks
When you are merging sources to update main sheet, is there a way to prevent "incoming" duplicates rows from overwriting what I already have? My goal is to be able to update an excel sheet with sources of data that sometimes have additional headers or not in the same order.
Can we apply this technique with Power Query in Power BI? Thank for your reply
I'm trying to combine a couple of sheets (not files) into one table. These sheets are NOT related and have several inconsistent headers.
Another awesome lesson! Thanks for providing the sample files too. Thumbs up!!
My pleasure!
wow, generating a list of all distinct column names for mapping is really genius. I also noticed that you faced the same problem of power query inserting rows in partial columns when query is refreshed which messes up the table. I got around this problem by using only VBA to refresh and disabling Explicit refresh. I don't think Microsoft is ever going to fix that.
Really well explanation.. Just subscribed...
Welcome aboard!
This was helpful, however, what if there are many columns in two sheet which are not needed in the final output?
Great solution Chandeep 👌 Could you please show solution for xls (not xlsx) files ? Looking for ways to automated conversion to xlsx or way to combine and use xls :)
Simply Gold Content as always.
Thank you 😊
Thank you, I really needed this solution! :)
Glad it was useful!
Excellent presentation. Thanks
I have a similar problem, but I want to Append data in Table 1 with data from Tables 2 and 3. The data in Tables 2 and 3 is in a different column order than the data in Table 1. I want to write a script so that a person will label each of those columns with consistent header names for the subsequent Power Query. For your example, the "Mapping" column names wouldn't have consistent names... this is mostly because my coworkers hate me.
thanks tutorial and simple file
to step by step
thank you very much
Its Just Awesome…. Is it possible in Excel 2016 version??
Very good content and very useful.Thank very much
Glad it was useful!
Goodly, what if instead of taking data from a folder, i want this to be sheets in same excel..how to make it queries from same excel file? Shall I select from table range ?