Combine Files With Inconsistent Columns In Power Query 

Check out my full courses and ebooks here 👉 www.howtoexcel.org/courses/

Unbelievably good video. This is what the internet was invented for.

Thanks so much. I was searching for how to combine files with unmatched headers and sat through a few videos before I found yours. what a life saver.

Hi, thanks. Question: At the end, we have our data in two different columns... Can you teach us how to fix them inside query editor?

This was amazing. So helpful for my project. Thank you

How do you apply custom function to each tables 8:18 before expanding them? e.g , demote headers & transpose.

Thanks for the video. what about if you add another new table with a column lets say named C? it did not refresh correctly.

Is this possible to use this trick but I also want to remove few top rows because I have a expected columns name in row 3 in each files I want to combine.

this is awesome. I gotta ask, is this possible when files are in sharepoint or using the Sharepoint.Contents syntax?

Is it possible to make an append with columns that have different names? For example: Table1 [Item] ~ Table2 [Id]. As a kind of stacked rows

I wanted to migrate data from one excel file to another. There is data in 1st file but no data in the 2nd file other than column names. The column names are not the same as well. For example the 1st file has 20 columns out of which the 5th to 10th column names are First Name, Last Name, Address, City, State and Pin Code where as the 2nd file has only 5 columns namely Last Name, First Name, Address, City, State and Postal Code (in the same order as mentioned) How do I do this?

I wanted to migrate data from one excel file to another. There is data in 1st file but no data in the 2nd file other than column names. The column names are not the same as well. For example the 1st file has 20 columns out of which the 5th to 10th column names are First Name, Last Name, Address, City, State and Pin Code where as the 2nd file has only 5 columns namely Last Name, First Name, Address, City, State and Postal Code (in the same order as mentioned) How do I do this?

Had to combine almost 3000 .csv files with varying headers. I only cared about a few of the common headers in each file and helped me get what I needed - thank you!

I am needing some help excel files that have locked cells and a mass array of information. I have tried a macro but keep running into issues and I just need to pull data from each of these files that are a specific cell. I think my issue may be a bit more complex but I honestly do not know. Please any help would be amazing.

Hi, my files have to promote header. How to combine files with promote header while add column?

What an amazing solution. Works like a dream and has solved my problem. As you add additional files, will need to ensure that the filter is amended to include the table from the new file each time.

I would have use add custom column=> =workbook.worksheet({content}) and expand the new column

Is there a way to do this if the files you are trying to combine are CSVs?

Amazing, I searched everywhere to get a solution on fixing this. None of it works or not to this issue at all, until I saw this video and it solved my problem. Thank you so much. However, what I don't understand is each of the workbook are exactly the same in structure, however Power query still throws error, unless I do this way. weird.

LIFE SAVER

Your getting a super like for this. Even though its a old video.

Impressive Sir, I like the approach, I'm subscribing for sure

Hello Power Query users… I Have this problem: Every month I export from SAP a lot of reports. I need to consolidate this reports in one big report. First 5 columns always is the same. But the problem is that the other columns every month have a different names and different numbers according how many days and hours there is in the respective month. If the month have a 30 days I have a 720 columns, if 31 - 744 columns. The headers look like this: 01.10.2020 01:00, 01.10.2020 02:00,… …,01.11.2020 00:00. Is it possible to make a query that can do this… Thanks! 😊

I love you, thanks for you help. I needed this for Power BI and couldn't find solutions :)

excellent video, was having problems now its fixed!

Wow!! This was a life saver. Thank you!!!

I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).

Excelente!!!

Hi, what if I'm interested in certain data in each tab of the file. Furthermore, the data is not set in a table format. Would it be possible to consolidate multiple reports like this?

@howtoexcel From your final output, how would you get the A and B column data to line up into a single column?

Thank you for this video. This is very helpful

It works well with two files but once you'll include a third file, it doesn't work

Boom! Thanks Buddy, just solved hours of work

Nice trick. I would merge the data columns as a final step before loading to a table.

Lol skip the first 5:45 if you want to get closer to the answer.

great content, thanks for sharing

Thanks, that was really useful and easy to follow.

Great sir

nice!

You are a genius

This got me out of a huge bind - THANKS!!!

there is a fast forward

Thanks! This useful trick I learn some days ago. From Your side clearly explained.

didn't work for me.