Hi I have tens of Excel files with similar table (means: same name, same headers, different content). I'd like to merge all data from these tables to one big table for future processing. I did it many times before using these steps (I have non-English version, so strings could be slightly different):
Now I usually get one big merged table. But for one set I get In Query editor window there is a few files loaded OK and then it ends on whole row filled with This could be easily walked around by using checkbox "Skip files with errors" in
How to find it (them)? Attempting to pull data into Excel from a UDT. Raw data will pull, but, when I try to edit Query, receive "Expression.Error: The key didn't match any rows in the table." in Power Query. See screen shot : Any Thoughts? I have used "syzcon" running the Data Dictionary utility ("DD") to set table keys. I need to pull specific data in a specific order into Excel.
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. How do you fix the key didn't match any rows in the table Power Query?There are two ways to fix the error:. Go to the Excel workbook and change the name of the sheet called “Hello” back to “Sheet1” or.. In either the Advanced Editor or the Formula Bar edit the M code and replace the reference to “Sheet1” with “Hello” in the second step of the query:. How do I get rid of expression error in Power Query?Error - Microsoft Power BI Community.. Get Data from 2 different folders, "Price Reports" and "Product Reports".. Clean and transform data from the files in each folder.. Merge the files from both folders.. Load to Table and Pivot Chart.. What does Expression error mean in Excel?Error: The key did not match any rows in the table.
How do I remove query errors?Remove rows with errors. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.. Decide which columns for which you want to remove errors. ... . Select Home > Remove Rows > Remove Errors.. |