Combine Data from Multiple Excel Files in a Single Excel Sheet - Part 2 

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

after spendin I do not know how many hours of reading and watching some videos, in despair I opened that one - and this was brilliant, very easy to understand and to apply for my issue! Thank you so much!

I was searching for merging multiple sheets into one sheet, thanks for your both videos helping me a lot to understand power query function. Amir Saif

Thank you very much, this video is exactly what I needed to properly understand what I've been doing wrong. Thanks!

Awesome it saves my 1hour everyday.

Explained very well. What if there is spelling mistakes sales rep name in two different years your response will be highly appreciated

Very simple English, nicely presented

Pretty much useful and well explained👍

Explanation is fantastic. Content is superb. Thanks a lot.

Thanks so much!!! Your videos are fantastic. I’d like to ask you a few questions, hope you can help. 1. Is there a way to pull data from a workbook with uneven data structure? Imagine a workbook with several bordered groups. Each group represents a job site and the names of staff members assigned to work there. Some job sites have multiple bordered groups due to multiple shift requirements 1st,2nd and 3rd shifts. During the day, all staffing changes are made on this main workbook. For example, if a staff member calls out of work sick it is indicated next to his name where he is assigned to work. The job site bordered groups are in various ranges. Therefore columns are rows are not consistent or even matched. Is there a way to pull data like Sick call outs, vacation, etc on a worksheet like this? Question #2. Is it possible to automate dates on multiple sheets with uneven data structure? I am required to update the same dates on all 3 sheets in excel. All 3 sheets are structured differently and the date locations are in various cell ranges.. can this be automated by entering the date only on the 1st page? Thank you so much.

Very useful and effective for my daily work, thanks.

Sir need one help , I have connected power query with a folder and folder contains multiples files with different dates and I want to filter only specific dates data pls help-out Even I tried to make date filter as a function but don't know how to connect it with editor option Request to please help out..

Thanks for the very informative video, I have following questions; - Can I adjust the column width of the final sheet to stay fixed during refresh operation? - Can i protect the final sheet and still do the refresh?

Thank you! What happened to the empty row you added on the top? I didn't see that you removed the empty row. Let us say that there is data in rows 2 and 3 and you don't want to show them. What would you do? You don't want these 2 and 3 rows to append after every table.

Really really helpful! A BIG THANK YOU!

fantastic Video. thank you.

Thank you for the Video, it is really helpful. Have a question, what is the option if I want to filter based on Sales person in combined file

Hi Sir, is there any formula or format to prepare One View Dashboard Xl without sharing any reference data to others like just enter the one word or one number(which are mentioned in reference files) at any cell then it Comes all the required data at a time .. Please reply for this query it's Very useful to my profession

Hi Mate I'm a fan of your very clear video tutorials, I would like to rely a question and hopefully I get an answer to your magnificent mind :) If I combine 2 excel file (with same data structure), and same data but no date column, I only rely to ID (unique) to remove duplicates. My problem is, when I change something inside one column(update), it does not overwrite and still retain the old data since I'm referring only to a ID. How can I solve this issue in which if theirs an update it will adapt the new data as output, not the old data? Thank you in advance Chandeep, more power!!!All Support

This save my day. Thank you so much!

you are too good, thank you so much

great video. Can you help or point me to the right video that will help me combine the rows in power query like you combined the columns?

Is there any way to eliminate specific rows from all sheet before close and load

Thank you for your video! I have a question. Is there a way to add a row with a name of the file? For example, say I'm getting the data that you're using but it does not have the row with the dates, just the data. The date is only in the title of the file. How can I add a name automatically that matches the name of the file?

Thank you very very very much , it was really helpfull , URGENT Q- can i separate the combined files by inserting each combined file's name before it's data? example : file year - 2005 then data , then cell contains "file name : year 2006" then data ,,,etc ???

thank you

Very informative, thanks, I have a question with data entry forms created in excel. how do we combine these type of excel forms that are filled with data, to create a master data sheet, is there a way to do using power query?

amazing !!!!!

Can we use the procedure for sheets with merged cells and blank rows

Thanks very much for the video. I am having an issue removing he first few rows from my sample file and the bottom two rows from the same file. Removing the first 10 rows is not a problem, but when I go on to remove the bottom two rows, it throws up an Expression Error: The key didn't match any rows in the table. I've been going round in circles for the past 2 hours but can't figure out where I have gone wrong. Please can you help advise what might be causing this? Thanks.

Hi, how to do transform files from shared files dynamically? Do you have any video on that please give the link

Very nicely explained

How to import certain worksheet of every files in the folder as a independent worksheet (not merged into one worksheet)?

If we have made file using power query available in oxide 365, can we access the file on other systems which are operating on Microsoft office 2007?

Hi Chandeep, I got error while doing merging the sheet as the two sheets few columns are identical columns and the rest of the columns i need in the merged data, but the excel is throwing error, Can you please help

really awesome!!

i want to merge multiple excel containing multiple sheets with data like sheet1, sheet2 etc to once existing excel with same sheets accordingly. is it possible

Very clear, precise and to-the-point video, thank you! This is a very quality tutorial. Question: Is this possible with multiple tabs within 1 file + 1 tab in another file, so basically merging data from 2 tabs in 1.xlsx and 1 tab from 2.xlsx, and with completely column layout (one has 8 columns, other has 15, but they are relevant data, just presented differently like Customer vs Client, Last Active vs Last Purchase, Total Due vs Balance, etc..)

Very very helpful. I have a question. I have several workbooks with the same structure and I combine the files manually. I took only the new data. Will power query able to update the file without duplicating the previous data and add only the new ones? Thank you very much to reply to this question? ARC

Hi I wonder if you can help? I work in a school and need to export student data from our MIS to a web system each year. The web system does not use a unique identifier that is exported with their CSV. I would like to combine a CSV from the MIS with a CSV from the online system. I need to match the two files by First name, Surname and DOB. The MIS file is the most accurate and will contain new students and class group changes for all students. Absent from the MIS file will be pupils who have left and these need to be excluded from the final output. Now I am doing this manually and it takes forever and is prone to error. I would be incredibly grateful if you or one of the viewers can help. I have got as far as combine the two sheets, but this just puts one set of data below the other.

Thanks for the clear explanation! 👍I have a question: if i have photos in each row, is it possible to combine them into the master sheet?

will it work for workbooks having data in multiple worksheets amd aslo will it combine the duplicate entries?

That's an amazing video, but can this method handle big thousand rows data as well?

2:45 i'm coming to this video from your other video about creating dynamic paths. i've done that with dynamic files for other data sets. currently trying to do the same with merging other files. I can list the required files, but when i remove other columns and try to epand (at 2:45) i get an "expression.error: we oculdn't find an excel table named 'DynamicPath'. Any idea why this is? or am i watching the wrong video?

Thanks for this video. But how to convert each file table with standard header format. Cz I have data with multiple files which will added dynamically and each files having slightly different headers. So how to merge this type of data.

Hi, Thank you for sharing a very informative video on merging workbooks. I have a workbook with multiple sheets which i have converted to table formats. The data in every sheet is different with uneven column headings and I don't know how to combine these. I have tried converting all the worksheets to table formats and appended them however I feel the data loaded is incorrect. Could you please help with this query?

what if the files have outlines and sort rows with duplicate information but different times. i.e merging opens downloaded by constant contact

I've got an error "DataFormat.Error: The input couldn't be recognized as a valid Excel document."

Wonderful. How can we do reverse of it. Means split the combined file into different files.

Hi thanks for sharing, just have a question: if my files have multiple tabs, will it still work?

Hello Chandeep, Thanks for suchh awesome and simple explanation to such a complex doing. But I am facing a problem. When i click on combine files double arrow button over Content column it gives me error: DataFormat.Error: External table is not in the expected format. Details: gczox3ca.XLS Please tell me what can be done to fix this error. Please!

Hi! Thanks for your interesting video! I have a question, I got an error “the key didn’t match any rows in the table”. Is it because my data is not structured consistently? Thanks!

I have a query. What if I want to pick up data of a particular month from 3 different worksheets containing 3 different subsheets each (for example, total 9 sheets for 9 customers where each company handles 3 different customers) & combine into 1 excel sheet? The worksheets contain yearly data and I need to pick up and project the data in a single sheet for all 9 customers.

After the data gets loaded it shows errors. What should we do

If excel has multiple sheet with uneven data..do we follow same method?

Thanks, thanks a lot for you clear video explanation! Due to certain character(s) used my Power-Query is not recognising a column from the multiple Excel Files. The text used for the column title is “Total [m3]”, with character 3 in superscript. Do you have an idea how I can cure this topic?

Question. How does this work if the workbooks are on a SharePoint? Would you still be able to pull the data in the same manner and merge data into a signal document?

What if we have 2 sheet in excel😂 page 2 is pivot table, can you help me? Thanks🙏🏻

I want to do like u did with customer and client. But the client header is always changing in every file.

While consolidating multiple file, I am getting an error stating " [Expression.Error] The key didn't match any rows in the table". I have to consolidate 12 files and each file has close to 50000 rows and 78 columns

Hey.. Thanks for video. I got this error when I click the combine. " DataFormat.Error: External table is not in the expected format. Details: ywzed51k.XLS " Please suggest.

How to filter Data from one sheet to move Seprate sheet Item wise

Awesome videos! That was really clear and helpful!. I would like to ask something. I have some files where the date is kind of split. From A1 to B3 I have the database info, like report date, report name, report responsible, and my actual table/info starts on A5 (row 4 is empty). Is there a way I can have a clean report with the following characteristics? 1. Take the report name (B2) to a new column. 2. Combine the info (the one that starts on A5 - Kn) from all reports, including the new column with its own report name I would really appreciate your help, I have been dealing with this for a while with no success.

I am trying but error showing while doing

How to convert multiple excel sheet data in a single sheet.

It's asweome

Plase share ur number