Combine Data from Multiple Excel Files | Most Dynamic Method (Dynamic Columns & Sheets)
ฝัง
- เผยแพร่เมื่อ 14 ส.ค. 2023
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
Combining Data from Multiple Excel Files: A Dynamic Approach for Data Cleaning.
In this video, we will explore the process of combining data from multiple Excel files, which is a common data cleaning task in Excel. we will address the challenge of dynamic queries.
What if the sheet names or column names in the datasets change? How can we still efficiently combine the data? Join us as we dive into this issue and provide a dynamic solution for combining data from multiple Excel files.
#datacleaning #powerquery #dataanalysis
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/combine-data-fro...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers! - วิทยาศาสตร์และเทคโนโลยี
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.
Excellent pace. No frills and waste of time.
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
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.
Powerful, clear, direct, controlling all scenarios => Goodly
This is really a comprehensive video......
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
Very cool idea. Bravo 👏!
Thank you for sharing
Good explanation, the logic so clear
Super cool video, thanks Sir
Good stuff....thank you.
Clear explanation.
A perfect mentor ❤
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 :)
Nice tip!
Wow.. amazing
You Rock, Chandeep!
Really good!!!
Thank you sir for bringing this video
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 :)
Thanks!
Woww this is a life saver! First on youtube
Amazing ! 💥💥💥💥
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!
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 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?
While watching the video, I remembered that I had seen this video somewhere else and recalled where it was. Anyway, nice tutorial, Chandeep! 👍
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
Nice input, whatif we have csv format data in that case how to promote header in default?
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
Thankyou soo much.
How can i skip columns in only selected sheets using M query
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.
Magician of excel 🎉🎉
Thanks 😉
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
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
One challenge could be to add the filename and sheet name in the combined result
How to tackle promote header part if CSV file are the source data?
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
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 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 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
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
Please tell how to convert binary into table if file format is cvs
How to append multiple excel formats like xls,xlsx csv and tab delimited files together
What if I need only first sheet of workbook irrespective of name of sheet, please guide
How can I modify this query to get the filename and sheetname 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 to retain the filename
👍
What if the headers are in caps in one sheet and lower case in another sheet
How can I get specific sheets from a single Excel files ?
How can I know which line is from which file if I don't have a date column?
Sir ,when running power query "intilazation of data sources error contact your administrator" is shown could you please provide a solution for this 😢
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!
What's this you are doing all days?? I don't know anything but I want to learn it. How can I learn it.
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
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 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
thank you but how to do this with CSV files? it doesn't work when i put the (_,)
GOATly
Try it but it limited to 3.6k+ lines. It can’t fully combine all data 😢
How to solve it?
= 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 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?