I love your solution. Combined with your Remove junk rows video you would be able to do some transformations before promoting the headers if they were not in the first row. Great stuff.
@ 4:29 what do I use for csv files? If I do excel.workbook it lists all the csv files I have in each table, instead of giving me the data in the csv files?
Sir, Your Power query tutorials are excellent and always eagerly looking forward to new PQ tutorials. One clarification : Please clarify: You are using Table.Transform Columns and Excel.Workbook)(_) for converting binary into excel tables . In another situation you use Add Column and in the Dialog Box, type Excel.Workbook(). When do we have to use Table.TransformColumns and When do we have to use Add Column for converting Binary into table. Is there any difference . Looking forward to receiving your answer.
Very detailed and concise explanations! Thank you! Question: I noticed that when expanding the tables their corresponding sheet (Step 3) shows the sheet name (under the Name.1 field but underneath each sheet also the following sheet name appears _xlnm_FilterDatabase which is not a sheet in the Excel file. Under the 'Kind' column it reads 'DefinedName' with the same .xlsx extension and it is hidden. What is this other row about? Also, each Excel sheet has a drop-down menu option for some cells. The menu options is located in another non-data sheet. But, these sheets also show as a sheet name when expanding the tables which reads 'Validation List.' How do I avoid getting data from the validation sheets? I appreciate your help!
Another interesting video. On the caveat - a very good reason to always familiarise yourself with the data before even starting the ETL and reporting process. I would also try to ascertain ahead of time if the content type or layout of the data is likely to change in the future and provide plenty of rem statements against anything being added or just as bad taken away add to that some kind of documentation to explain how the visuals were produced and how to edit them to include extra data or remove fields etc. that have been deprecated.
Your videos are awesome... is there any way you can list the keystrokes you do as you do them, like the important ones, but also the others, every once in a while for those jumping in a bit later to the excel party. Thanks for making great content!
Thanks for sharing your knowledge. Could you post a video for this scenario extract text multiple text files into excel, remove first 2 top rows and then split into columns and final step merge all the files to create pivot chart. Thanks
great tutotial and an interesting pattern. Can this be applied in the folowing case: an excel file with 12 tabs each respresenting a month and each tab contains 9 tables ordered in rows table 1 is from row 10 to 50 and column A to K ; table 2 is from row 60 to 100 and column A to K and so on to table 9 then tab feb with the same structure. how to tackle this problem , thanks in advance Roger
Great tutorial! I am wondering... would you recommend to append a live table/tracker. I have one file per year but current year is in a table where my colleague inputs data daily. Would it be ok to combine this table with the rest?
How do you bring together the [Name] column when you drill down? I wanted to use this method instead of drilling down ng binary files since I need the column headers already promoted and dynamic. I understand that it's possible to add column on the Table record, but I can't quite figure out how. Any help would be appreciated or just a tip on how to do it. Thank you.
Per my other comment- I think I’ve worked out how to do this so thought I’d share: You do need to use expand columns and then edit that step so the third argument is dynamic. It should look like this in place of step 4 in the video =Table.ExpandTableColumn(#”previousstepname”, “Data”, Table.ColumnNames(Table.Combine(#”previousstepname”[Data])))
I have an issue. When I do the Table.ExpandTableColumn (approx at 5:30 in the video) I get a "MASSIVE" Error that starts with this: Error Message:Object reference not set to an instance of an object. Stack Trace: at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message) at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel) at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing) at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_TableSource() at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource() at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled) Any suggestions?
It is good way to include all the sheets but I think, it will be always good to know what sheet you are including and not hardcode the formula. The important point was to include the data header as LIST so that it can accommodate any addition or change of the header, which is most important while combining any table from different sheet. I always use this. Can you make a video,to combine data which is in one sheet, have different data sets (not in excel table format) with some common headers and only the differentiator is at the end of each data set there is a row which start with word "Total".
What a greaat video man, but how do i activate the formula suggestion in power query excel, i use excel 2016 and also how about the origin file is not excel workbook, but it's CSV file ? Because the formula isn't work with csv file
Is there any way to combine data in which the data from the file #2 is added below the data of file #1 and so on? Also after loading data to a table, I need to add a Comment column on the right side. Is there any way to keep my comments still aligned with their rows after Refresh? Thanks
I think I’ve worked out how to do this so thought I’d share: You do need to use expand columns and then edit that step so the third argument is dynamic. It should look like this =Table.ExpandTableColumn(#”previousstepname”, “Data”, Table.ColumnNames(Table.Combine(#”previousstepname”[Data])))
hi, i have one question,i had created multiple pages in Power view in tabular format and i need to get export all pages and to be in workbook format as like Excel work book,please suggest
Would this be live or dynamic? Example; At work we have several workbooks for different departments. I want to pull or push this data to a single master workbook. THEN if someone adds a new row to the department workbook, will that update to the master too? (i don't mind if i have to refresh rather than being 'live')
Great Video!! I have a special issue with my data...my boss has requested I calculate the dwell time a material has spent in a location. I am having trouble calculating that. So if a material goes from the warehouse rack to a production line how do I calculate that in DAX? I have a confirmation and creation date for the pallet going both ways. I have missing confirmation dates for pallets that have not been confirmed to the destination location and it is causing errors in my calculation using datediff. Please help if you can and let me know if u require some sample data.
Thanks for the good info...can you please make a video on the problem where we have to combine multiple files or sheet having inconsistant header to map with the updated header names and then combine.
Great as usual ! :-) However when changing some header names, the combine breaks So I asked ChatGPT how to solve, and that 's what I made up from the explanations : = Table.TransformColumns( Source, { "Content", each Table.TransformColumns(Excel.Workbook(_, false), {"Data", each Table.Skip(_, 1)}) } ) Now header names can be changed... If ever you know a better method .... -:)
I love your videos ❤ I have a. Excel sheet with dynamic columns is there a query that extracts only the column names i mention irrespective of new columns that get added.😊
Good video. In the end though, all it takes is 1 other sheet that should NOT be combined to break this. After all, how could you know which sheets to combine and which sheets not to combine? What works for me: After cleaning, load all the data into an MS Access database. For go-forward, query only the latest workbook and set up an error message if the sheet name or expected columns have changed and then just change that manually. Then append that cleaned data into the Access db. Then you can query all your data with no transformations needed. I find it interesting that no content creators talk about MS Access as it's a much more scalable option than combining workbooks which can easily error.
Absolutely! My goal is to empower students to solve problems independently. I'll explain you through each Power Query concept. And yes, there's a comment section for any questions you have!
When i am combining data from multiple excel workbooks which extracted from zip format It consolidating only first 8 line items Can you please help me sir
I edited the 2006 file and changed customer header to client and when I refreshed the query it just created a new column at the end called client. How do I stop it creating new column and just put the client column details into the customer column
= Table.TransformColumnNames(#"Filtered Rows1", {"Content", each Excel.Workbook(_)}) Then I am getting an error below- Expression.Error: We cannot convert a value of type List to type Function. Details: Value=[List] Type=[Type] Any help, please.
Download File ⬇ - goodly.co.in/combine-data-from-multiple-excel-files
I'm a data analyst. Your videos are helping me a lot in real time projects. Thanks is a very small word. Love u bro
I love your solution. Combined with your Remove junk rows video you would be able to do some transformations before promoting the headers if they were not in the first row. Great stuff.
I have been scouring the various information sources to find a possible solution to my problem. This is exactly what I needed. Thank you thank you 🙏
Glad it helped :)
@ 4:29 what do I use for csv files? If I do excel.workbook it lists all the csv files I have in each table, instead of giving me the data in the csv files?
There have been numerous tutorials on this topic but not at the extent to the valuable hacks you have provided in this session. Thanks a lot 🙏
Thanks Alex :)
Excellent pace. No frills and waste of time.
Sir, Your Power query tutorials are excellent and always eagerly looking forward to new PQ tutorials. One clarification : Please clarify: You are using Table.Transform Columns and Excel.Workbook)(_) for converting binary into excel tables . In another situation you use Add Column and in the Dialog Box, type Excel.Workbook(). When do we have to use Table.TransformColumns and When do we have to use Add Column for converting Binary into table. Is there any difference . Looking forward to receiving your answer.
This is really a comprehensive video......
Powerful, clear, direct, controlling all scenarios => Goodly
Good explanation, the logic so clear
Very detailed and concise explanations! Thank you!
Question: I noticed that when expanding the tables their corresponding sheet (Step 3) shows the sheet name (under the Name.1 field but underneath each sheet also the following sheet name appears _xlnm_FilterDatabase which is not a sheet in the Excel file. Under the 'Kind' column it reads 'DefinedName' with the same .xlsx extension and it is hidden.
What is this other row about?
Also, each Excel sheet has a drop-down menu option for some cells. The menu options is located in another non-data sheet. But, these sheets also show as a sheet name when expanding the tables which reads 'Validation List.'
How do I avoid getting data from the validation sheets?
I appreciate your help!
Another interesting video. On the caveat - a very good reason to always familiarise yourself with the data before even starting the ETL and reporting process. I would also try to ascertain ahead of time if the content type or layout of the data is likely to change in the future and provide plenty of rem statements against anything being added or just as bad taken away add to that some kind of documentation to explain how the visuals were produced and how to edit them to include extra data or remove fields etc. that have been deprecated.
Your videos are awesome... is there any way you can list the keystrokes you do as you do them, like the important ones, but also the others, every once in a while for those jumping in a bit later to the excel party. Thanks for making great content!
Thanks for sharing your knowledge. Could you post a video for this scenario extract text multiple text files into excel, remove first 2 top rows and then split into columns and final step merge all the files to create pivot chart. Thanks
great tutotial and an interesting pattern.
Can this be applied in the folowing case:
an excel file with 12 tabs each respresenting a month and each tab contains 9 tables ordered in rows
table 1 is from row 10 to 50 and column A to K ;
table 2 is from row 60 to 100 and column A to K
and so on to table 9
then tab feb with the same structure. how to tackle this problem ,
thanks in advance
Roger
Magician of excel 🎉🎉
Thanks 😉
While watching the video, I remembered that I had seen this video somewhere else and recalled where it was. Anyway, nice tutorial, Chandeep! 👍
Woww this is a life saver! First on youtube
This is great! Thank you. Is there any way to keep the File and Sheet names in the final data?
Nice input, whatif we have csv format data in that case how to promote header in default?
Thank you so much . this helped me a lot bro .. well explained
Great tutorial! I am wondering... would you recommend to append a live table/tracker. I have one file per year but current year is in a table where my colleague inputs data daily. Would it be ok to combine this table with the rest?
How do you bring together the [Name] column when you drill down? I wanted to use this method instead of drilling down ng binary files since I need the column headers already promoted and dynamic. I understand that it's possible to add column on the Table record, but I can't quite figure out how. Any help would be appreciated or just a tip on how to do it. Thank you.
Per my other comment- I think I’ve worked out how to do this so thought I’d share:
You do need to use expand columns and then edit that step so the third argument is dynamic. It should look like this in place of step 4 in the video
=Table.ExpandTableColumn(#”previousstepname”, “Data”, Table.ColumnNames(Table.Combine(#”previousstepname”[Data])))
@@georgefox8458 Thanks. I'll try to test that although I already found a different solution to this.
Very cool idea. Bravo 👏!
Great. What if the files are in different folders?
I have an issue. When I do the Table.ExpandTableColumn (approx at 5:30 in the video) I get a "MASSIVE" Error that starts with this:
Error Message:Object reference not set to an instance of an object.
Stack Trace:
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_TableSource()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)
Any suggestions?
Super cool video, thanks Sir
I especially appreciate the promoteheaders step on 06:05
It is good way to include all the sheets but I think, it will be always good to know what sheet you are including and not hardcode the formula. The important point was to include the data header as LIST so that it can accommodate any addition or change of the header, which is most important while combining any table from different sheet. I always use this.
Can you make a video,to combine data which is in one sheet, have different data sets (not in excel table format) with some common headers and only the differentiator is at the end of each data set there is a row which start with word "Total".
You Rock, Chandeep!
What a greaat video man, but how do i activate the formula suggestion in power query excel, i use excel 2016 and also how about the origin file is not excel workbook, but it's CSV file ? Because the formula isn't work with csv file
Is there any way to combine data in which the data from the file #2 is added below the data of file #1 and so on? Also after loading data to a table, I need to add a Comment column on the right side. Is there any way to keep my comments still aligned with their rows after Refresh? Thanks
Thank you sir for bringing this video
Can we do it the same to .csv as well?
Is there to keep the name of the excel files? Because after drilling down the name column disappears. Thank you for the awesome video.
I was trying to keep the file names also, but was not able to figure out how to accomplish this.
i have the same issue, has anyone found a solution?
I think I’ve worked out how to do this so thought I’d share:
You do need to use expand columns and then edit that step so the third argument is dynamic. It should look like this
=Table.ExpandTableColumn(#”previousstepname”, “Data”, Table.ColumnNames(Table.Combine(#”previousstepname”[Data])))
Use this in place of the step 4 in the video, good luck 😊
Thank you @georgefox8458
hi,
i have one question,i had created multiple pages in Power view in tabular format and i need to get export all pages and to be in workbook format as like Excel work book,please suggest
Good stuff....thank you.
Clear explanation.
Nice tip!
Thankyou soo much.
How can i skip columns in only selected sheets using M query
Would this be live or dynamic?
Example;
At work we have several workbooks for different departments. I want to pull or push this data to a single master workbook.
THEN if someone adds a new row to the department workbook, will that update to the master too?
(i don't mind if i have to refresh rather than being 'live')
Great Video!!
I have a special issue with my data...my boss has requested I calculate the dwell time a material has spent in a location. I am having trouble calculating that. So if a material goes from the warehouse rack to a production line how do I calculate that in DAX? I have a confirmation and creation date for the pallet going both ways. I have missing confirmation dates for pallets that have not been confirmed to the destination location and it is causing errors in my calculation using datediff. Please help if you can and let me know if u require some sample data.
A perfect mentor ❤
Really good!!!
How to tackle promote header part if CSV file are the source data?
Thanks for the good info...can you please make a video on the problem where we have to combine multiple files or sheet having inconsistant header to map with the updated header names and then combine.
th-cam.com/video/0ZOY8is-bgY/w-d-xo.html
hi
how to updating power query when i used the I cloud ondrive or 3650 ???
thanks
Thank you for sharing
Great as usual ! :-) However when changing some header names, the combine breaks
So I asked ChatGPT how to solve, and that 's what I made up from the explanations :
= Table.TransformColumns(
Source,
{
"Content",
each Table.TransformColumns(Excel.Workbook(_, false), {"Data", each Table.Skip(_, 1)})
}
)
Now header names can be changed...
If ever you know a better method .... -:)
What if my headers are in 10th Row and I want keep first 9 row as those row contains data which I need to insert in columns for every single table?
Thanks, I am trying to make all my PQs unbreakable and your videos are amazing, easy to follow and live your explanation...making it simple
Wow.. amazing
Amazing ! 💥💥💥💥
What to do if column header is arrange not in sequence, they are randomly. In different workbook while combine.
The order doesn't matter
@@GoodlyChandeep ok thanks
I love your videos ❤
I have a. Excel sheet with dynamic columns is there a query that extracts only the column names i mention irrespective of new columns that get added.😊
This video might help: th-cam.com/video/kwf-E3a3aOE/w-d-xo.htmlsi=9vJdjFr3WF0b-BaG
What if I need only first sheet of workbook irrespective of name of sheet, please guide
Please tell how to convert binary into table if file format is cvs
What to do if I don't just need the combined tables, bat also an information sitting in an other column?
Good video. In the end though, all it takes is 1 other sheet that should NOT be combined to break this. After all, how could you know which sheets to combine and which sheets not to combine?
What works for me: After cleaning, load all the data into an MS Access database. For go-forward, query only the latest workbook and set up an error message if the sheet name or expected columns have changed and then just change that manually. Then append that cleaned data into the Access db. Then you can query all your data with no transformations needed. I find it interesting that no content creators talk about MS Access as it's a much more scalable option than combining workbooks which can easily error.
I agree!
How can I modify this query to get the filename and sheetname column?
How to append multiple excel formats like xls,xlsx csv and tab delimited files together
How to retain the filename
Insightful as always! Just to ask, in your courses, do you provide customized solutions to work challenges? or if there is a forum to post issues
Absolutely! My goal is to empower students to solve problems independently. I'll explain you through each Power Query concept. And yes, there's a comment section for any questions you have!
This is well noted and I will consider it. Thanks
How can I know which line is from which file if I don't have a date column?
What if the headers are in caps in one sheet and lower case in another sheet
How to also add file name in the table in this steps
Sir ,when running power query "intilazation of data sources error contact your administrator" is shown could you please provide a solution for this 😢
When i am combining data from multiple excel workbooks which extracted from zip format
It consolidating only first 8 line items
Can you please help me sir
Thanks!
thank you but how to do this with CSV files? it doesn't work when i put the (_,)
How can I get specific sheets from a single Excel files ?
I edited the 2006 file and changed customer header to client and when I refreshed the query it just created a new column at the end called client. How do I stop it creating new column and just put the client column details into the customer column
th-cam.com/video/0ZOY8is-bgY/w-d-xo.htmlsi=BDbKvEyw2sphXqaN
Try it but it limited to 3.6k+ lines. It can’t fully combine all data 😢
How to solve it?
What's this you are doing all days?? I don't know anything but I want to learn it. How can I learn it.
Is not work in CSV file. So please guide.
Great
= Table.TransformColumnNames(#"Filtered Rows1", {"Content", each Excel.Workbook(_)})
Then I am getting an error below-
Expression.Error: We cannot convert a value of type List to type Function.
Details:
Value=[List]
Type=[Type]
Any help, please.
Why are you using Table.TransformColumnNames() instead of Table.TransformColumns()?
One challenge could be to add the filename and sheet name in the combined result
❤❤❤
Thank you so much for all the content you provide!
If only you could spare us those fashion marks on your clothes, that would be just perfect!
👍
GOATly