Getting Expression.Error: after removing 3 columns from excel data source

[baloo]

I have had to do two changes to my PowerBI reports im working on that are causing errors.

Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=Table1
Kind=Table
Table=[Table]

I use an Excel Spreadsheet as my data source. I had to update my data source spreadsheet by removing three columns as the information was no longer needed. I saved my new spreadsheet with a new name and proceeded to update the PowerBI report data source and hook it up to the new spreadsheet. The three columns removed where somewhere in the middle of the spreadsheet.

I have updated the advance query editor to no longer reference the 3 removed columns in each section of each line of code but continue to error out on "Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data]"

Multiple tables have the same error.

The applied steps include: (nothing complicated, none of the removed columns were being split)

Source

Navigation <- Error occurs here

Changed Type

Split Column By Delimiter

Unpivoted Columns

I have used Refresh Preview, Close and Apply, and Apply. Same error.

I have tried to delete the navigation applied step. I get a new error that is even more confusing. It is saying a column is missing (and not one of the ones I removed). The one it says is missing is still there. Verified in Query, excel file, etc.

Expression.Error: The column 'myColumn' of the table wasn't found.
Details:
myColumn

I have also tried to remove/delete the columns from the relationship table view and the table/data view. This doesnt work as it just adds an applied step at the end of the query and not impact the query itself so I am back to where I started.

Why am I getting the Key error? What are the potential causes? Am I not refreshing the right thing? I always "apply changes".

[baloo · Accepted solution]

I figured out the problem. Previous versions of excel sheet were being imported as a table. The excel sheet had been converted to a table prior to being prepared as a data source.

That is why I was getting key row errors and table1 errors.

Ctrl + A on entire Excel spreadsheet, convert to table. Save spreadsheet.

[v-xicai]

Hi @baloo ,

Yu can refer to the cases below:

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table...

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table...

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table...

https://community.powerbi.com/t5/Power-Query/Expression-Error-The-key-did-not-match-any-rows-in-the-...

https://forum.enterprisedna.co/t/error-the-key-didnt-match-any-rows-in-the-table/289

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[baloo]

Thank you but I have already reviewed those forum posts.

[parry2k]

Can you post the code from advanced editor

[baloo]

I'm sorry no, I can't.