Combining data when templates have changed.

[winds]

Hello. So I need help regarding combining excel files. I think I've got the hang of using power query after much trial & error. (I still don't fully understand it but the important thing is I seem to be able to get the output that I'm looking for... so far)

So recently, I tried to combine excel files using power query, but I kept getting the error that says, "key that didn't match any rows in the table".

Basically, all the excel files I intend to merge SHOULD have the same template, but in this case, I think some have changed.

So I trialed about three files with power query which seems like the template should be about the same, and those seem to work.. which is great! But now the problem arises with the other files where the template has changed.

So my question is.. is there a way I can Combine and Transform Data even if some of the other file templates have changed? As of right now, it would seem I have to manually check the files one by one that has changed. So once I've checked one by one only then I can properly use Power Query.. which will be too tedious...

 

[jdellasala]

It's unclear whether you're doing a Merge or an Append. It sounds like you're trying to do a Merge which can be thought of as a Lookup. In its simplest form, that means you have one table that has a column of unique values and other related data such as a unique Part Number, and then a Name, Price, etc. This is sometimes called the Dimension table. Then you have what's called a Fact table like a list of Invoices with one column for Part Numbers but no price, name, etc. When you Merge the Invoice (Fact) table with the Parts (Dimension) table and tell PQ that the Part Number in the Invoice table is the same (index or key) and should match Part Numbers in the Parts (Dimension) table.
An Append is pretty much what it sounds like - you're joining two tables in a stack. The typical problem here is that the column names MUST be IDENTICAL - same case, no leading or trailing spaces in one. When columns don't match, the column in the second file is added to the resulting table, but the values are null for every row of the first table.
All that said, there are plenty of places a Query can break. Experience will teach you how to avoid the ones that are preventable, but sometimes can require some acrobatics to resolve.
One really common and annoying problem is the Change Type step. That action hard codes column names, and if you go back and change a column name before that step, it will break. It's best to use it as little as possible until you're done with other transformations.
Post the code you're using and specific problems you're having. If there's Data involved, use XL2BB to post it. And don't give up. Power Query's M Language isn't simple, but the UI can do much of the work, and in the long run it's well worth the time to learn.

 

[winds]

It's unclear whether you're doing a Merge or an Append. It sounds like you're trying to do a Merge which can be thought of as a Lookup. In its simplest form, that means you have one table that has a column of unique values and other related data such as a unique Part Number, and then a Name, Price, etc. This is sometimes called the Dimension table. Then you have what's called a Fact table like a list of Invoices with one column for Part Numbers but no price, name, etc. When you Merge the Invoice (Fact) table with the Parts (Dimension) table and tell PQ that the Part Number in the Invoice table is the same (index or key) and should match Part Numbers in the Parts (Dimension) table.
An Append is pretty much what it sounds like - you're joining two tables in a stack. The typical problem here is that the column names MUST be IDENTICAL - same case, no leading or trailing spaces in one. When columns don't match, the column in the second file is added to the resulting table, but the values are null for every row of the first table.
All that said, there are plenty of places a Query can break. Experience will teach you how to avoid the ones that are preventable, but sometimes can require some acrobatics to resolve.
One really common and annoying problem is the Change Type step. That action hard codes column names, and if you go back and change a column name before that step, it will break. It's best to use it as little as possible until you're done with other transformations.
Post the code you're using and specific problems you're having. If there's Data involved, use XL2BB to post it. And don't give up. Power Query's M Language isn't simple, but the UI can do much of the work, and in the long run it's well worth the time to learn.

It's definitely Merge.

So far I have seen that the power query doesn't seem to work if there are files that are slightly different formats. For example one excel file is one version and another is an older version. I find I had to update the older version to a newer one only then can power query work.

What if there are more files formats that are not the same. Do I have to manually change every single file format so they all match?

 

[jdellasala]

It's definitely Merge.

So far I have seen that the power query doesn't seem to work if there are files that are slightly different formats. For example one excel file is one version and another is an older version. I find I had to update the older version to a newer one only then can power query work.

What if there are more files formats that are not the same. Do I have to manually change every single file format so they all match?

If you're trying to merge "on the fly" without bringing the file into PQ first, all bets are off, especially if you're talking about an XLS file which was dropped because there were security issues with it. Bring the files into Power Query first, and then do the merge. Also, assuming you're talking about an XLS file, the database I used to work with exported to an "XLS" file, but that was just the file extension. The file was actually a tab delimited text file. PQ might not like that, but I don't know.