Lỗi the key didnt match any rows in the table
I figured out the cause of my problem and the solution. The issue is that the row in my template query was being referenced incorrectly (i.e., the primary key between the template query and the regular query is wrong, and it has hard-coding of sheet names). To fix that, I had to remove all other columns in the template query table except the Data column, as described here. (It's odd that no MS documentation on combining multiple Excel files discusses this very important step.) For comparison, here is my former (incorrect) M code: Transform Sample File:
test:
And here is my new (correct) code: Transform Sample File:
test:
Notice the 'Removed Columns' step in the new template query. This is the "secret sauce" to the key problem. Also notice that I kept all default steps after my 'Data' step (i.e., 'Promoted Headers' and 'Changed Type') in my template query. This is because all of my sheets have the same schema. If this weren't true, then I would need to move those steps to the regular query. I connected to a folder that currently has two files. The files are structured exactly the same way. Each file has a table and same exact columns. When I connected, I clicked on Combine & Transform Data. I made all my transformations on the Transform Sample File, I created connection only and added to data model and then came across this error. What step am I forgetting? Any assistance would be greatly appreciated. JG Mynda Treacy Admin Forum Posts: 4652 Member Since: July 16, 2010 Offline2 May 9, 2022 - 8:53 am Hi Jose, Welcome to our forum! This error is caused by a name being referred to in your query for specific table or column names that are no longer present in your source data. e.g. when you originally built the query a table might have been named TableA, but now it's named TableB and therefore the query can't find TableA. Of course it could also be renamed columns. |