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.
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.
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])))
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".
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.
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
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!
@ 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?
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?
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')
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
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.
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])))
Hi Sir Hope you doing Good!! This New update On Power Bi on 24th April has remove the Copy Data table option or it's not working in the latest version Due to what ch unable to copy the Data Table in excel Can you please help on small video ant the same pls.. I beleive no one has done that after updation
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
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 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.😊
= 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.
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
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
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!
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.
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!
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?
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 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 :)
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.
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.
Magician of excel 🎉🎉
Thanks 😉
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
This is really a comprehensive video......
This is great! Thank you. Is there any way to keep the File and Sheet names in the final data?
Powerful, clear, direct, controlling all scenarios => Goodly
Woww this is a life saver! First on youtube
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".
Good explanation, the logic so clear
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.
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
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!
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
Clear explanation.
@ 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?
Thankyou soo much.
How can i skip columns in only selected sheets using M query
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
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 to retain the filename
What to do if I don't just need the combined tables, bat also an information sitting in an other column?
Very cool idea. Bravo 👏!
What if I need only first sheet of workbook irrespective of name of sheet, please guide
Thank you for sharing
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')
One challenge could be to add the filename and sheet name in the combined result
Nice tip!
Good stuff....thank you.
Nice input, whatif we have csv format data in that case how to promote header in default?
Please tell how to convert binary into table if file format is cvs
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
Wow.. amazing
Thanks!
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 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.
How to also add file name in the table in this steps
hi
how to updating power query when i used the I cloud ondrive or 3650 ???
thanks
Hi Sir
Hope you doing Good!!
This New update On Power Bi on 24th April has remove the Copy Data table option or it's not working in the latest version
Due to what ch unable to copy the Data Table in excel
Can you please help on small video ant the same pls.. I beleive no one has done that after updation
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
How can I get specific sheets from a single Excel files ?
What if the headers are in caps in one sheet and lower case in another sheet
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
How can I modify this query to get the filename and sheetname column?
Try it but it limited to 3.6k+ lines. It can’t fully combine all data 😢
How to solve it?
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!
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 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: ruclips.net/video/kwf-E3a3aOE/видео.htmlsi=9vJdjFr3WF0b-BaG
Sir ,when running power query "intilazation of data sources error contact your administrator" is shown could you please provide a solution for this 😢
❤❤❤
= 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()?
I especially appreciate the promoteheaders step on 06:05
How to append multiple excel formats like xls,xlsx csv and tab delimited files together
What's this you are doing all days?? I don't know anything but I want to learn it. How can I learn it.
thank you but how to do this with CSV files? it doesn't work when i put the (_,)
How can I know which line is from which file if I don't have a date column?
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
ruclips.net/video/0ZOY8is-bgY/видео.htmlsi=BDbKvEyw2sphXqaN
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
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!
Thank you so much . this helped me a lot bro .. well explained
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 :)
GOATly
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.
While watching the video, I remembered that I had seen this video somewhere else and recalled where it was. Anyway, nice tutorial, Chandeep! 👍
How to tackle promote header part if CSV file are the source data?
Super cool video, thanks Sir
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.
ruclips.net/video/0ZOY8is-bgY/видео.html
Thank you sir for bringing this video
Excellent pace. No frills and waste of time.
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
Amazing ! 💥💥💥💥
👍
Really good!!!
A perfect mentor ❤
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?