Report Failing due to "Key did not match any rows"

[ElvirBotic]

I have a report where I am using Excel files as the data source. I am using the SharePoint folder connection to get the latest file. I have a few transformations and I have two dimensions. The dimensions are propety information as well as product types. The two dimensions live in SharePoint as lists. When I build my data model in desktop everything works and refreshes perfectly fine. Once I publish to the workspace, set up the connection strings, set refresh schedule, I get an error saying "the key did not match any rows in the table" 

I do not get it because I thought it was due to file names being different from what Power Query is expecting, but I do not see how it is possible if it is refreshing in desktop without that error?

Any guidance would help I am stumped and it is an annoying one. 

[v-jiewu-msft · Accepted solution]

Hi @ElvirBotic ,

If I understand correctly, the issue is that you got an error when publishing the report and setting refresh schedule. Please try the following methods and check if they can solve your problem:

1.Ensure that the data source credentials are correctly set up in the Power BI Service.

2.Verify that the file paths in the Power Query are the same with in SharePoint.

3.Check the relationships between the tables in the data model.

4.Make sure that the gateway settings is correct and that it has the latest updates.

5.Try to manually refresh the dataset in the Power BI Service.

Looking forward to your reply.

Best Regards,

Wisdom Wu

[edhans]

Could there be a permission issue with how you are authenticating for the SharePoint folder vs how you are signing in to Desktop? The "key did not match" most often is the file simply isn't there. A rename or missing due to permissions would give the same error. The service cannot see the file.

When you say "set up the connection strings" what are you doing exactly? When using SP Online, you just log in the data connection area. There is nothing to set up. If you have some parameters set where you are keying in a path or something, that is likely where you are messing something up and it isn't seeing the file(s) properly.

[ElvirBotic]

I have tried all above and nothing worked. I submited an internal ticket to our IT team. I may consider converting the excel file I use as a dimension to a SharePoint list. 

[ElvirBotic]

All I am doing is setting up the data source credentials. I do not have any parameters either in the report. This was all working a few weeks ago and recently started breaking in the service. Again, in desktop I can refresh no issue. Same credentials in desktop as well as the data source credentials in the service.