Combine Data from Multiple Excel Files | Most Dynamic Method (Dynamic Columns & Sheets)

How can I know which line is from which file if I don't have a date column?

What if I need only first sheet of workbook irrespective of name of sheet, please guide

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 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

Please tell how to convert binary into table if file format is cvs

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.

How to retain the filename

Really good!!!

This is really a comprehensive video......

GOATly

How to tackle promote header part if CSV file are the source data?

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

hi
how to updating power query when i used the I cloud ondrive or 3650 ???
thanks

Nice input, whatif we have csv format data in that case how to promote header in default?

What if the headers are in caps in one sheet and lower case in another sheet

Good explanation, the logic so clear

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.😊

Is there to keep the name of the excel files? Because after drilling down the name column disappears. Thank you for the awesome video.

Wow.. amazing

What to do if column header is arrange not in sequence, they are randomly. In different workbook while combine.

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.

Suppose We have csv file then how to take first row as header ?
because we can't use true (for making first row as header) using csv.document()

Nice tip!

= 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 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 🙏

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 modify this query to get the filename and sheetname column?

Excellent pace. No frills and waste of time.

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.

❤❤❤

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

Powerful, clear, direct, controlling all scenarios => Goodly

Very cool idea. Bravo 👏!

You Rock, Chandeep!

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.

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

Sir ,when running power query "intilazation of data sources error contact your administrator" is shown could you please provide a solution for this 😢

While watching the video, I remembered that I had seen this video somewhere else and recalled where it was. Anyway, nice tutorial, Chandeep! 👍

Thank you for sharing

A perfect mentor ❤

Amazing ! 💥💥💥💥

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".

👍

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 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.

Good stuff....thank you.

Thank you sir for bringing this video

Magician of excel 🎉🎉