Expression.Error When refreshing an excel file from Sharepoint

[DonovanJack]

Hello,

I get this ExpressionError when refreshing a Dashboard, that pulls data from an excel file, linked to a very robust SharePoint.

The dashboard was able to refresh data before, but now it does not even though new data has been added to the excel file (receive the 'Key didn't match any rows in the table' error).

*I would just attempt to point to another data source to access the data, but we have formulas and columns that I would like to keep in the dashboard that may be time consuming to recreate.

 Please help, I am not sure were we have gone wrong and have been trying to solve this issue for a very long time!

Thank you 🙂

@amitchandak @Ashish_Mathur @SpartaBI @MFelix 

[MFelix]

Hi @DonovanJack ,

Has @danextian  refered believe this is an issue about the name of the file being changed.

Just did a small test on a local folder and renamed my file and has you can see I have the same type of error:

[DonovanJack]

@MFelix ,

Yes, I think it is a path issue but what is strange is that my collegue can refresh/update the data with no problem. The file is in the same location and path since we are using the same Power BI file.

I guess the current issue is....

Why can't I or other Users refresh and update the data in the Dashboard and only he can using the same Sharepoint URL root address?

[MFelix · Accepted solution]

Hi @DonovanJack ,

Do you have access to the Sharepoint folder where that specific file is saved?

What you can do is to replaced your third step where it refers the Folder by the following code:

= Excel.Workbook(#"Filtered Rows"{[Name="EXcelFILENAME.xlsx"]}[Content])

Then also delete the 4th step, this should work in the same way.

[DonovanJack]

@MFelix 

That worked to get the old data pulled from the orginal excel file, when I first saved the report to my computer, but it does not pull in the updated/current data in the sharepoint.lists excel file.

Is there something wrong with the code?

[DonovanJack]

@MFelix ,

Yep, you were right it has to be an access issue regarding who has access to the excel file (which I'm listed as an editor so weird)>>I created and put a copy of the same excel file on the shrepoint and refresded the workbook.... and it works. 😀

Thank you!

[MFelix]

Has I refered do you have acces to the sharepoint folder? Can you go to sharepoint and open the file?

[DonovanJack]

Yes, I have full access and am listed as an owner.

[DonovanJack]

@MFelix .... the file name has not been changed in the SharePoint

[MFelix]

Hi @DonovanJack ,

Believe that something may have change in your path or file since you are having the error in the 3rd step that is before entering the file itself.

Did you by any chance changed the file from folder?

That specific step uses the path has a reference if you change the location of the file altough it's in the list because the source is at the highest level of the sharepoint folder the specific step does not work.

[DonovanJack]

@MFelix @danextian   @amitchandak @DataInsights 

This is the Source step:

It doesn't point to the particular file it just pulls up all the files/types on the Sharepoint 😕 
Not sure how to fix this issue, when I try to direct the source through the sharepoint list option (how the dashboard was set up) it says I can only use the root URL.

*It is looking like I will have to redo this dashboard*

[danextian]

Hi @DonovanJack ,

Many possible reasons:

• a missing or renamed worksheet (trailing spaces, text case changed)
• a missing or renamed column (trailing spaces, text case changed)

Please read this article for your reference - https://blog.crossjoin.co.uk/2020/04/06/understanding-the-the-key-didnt-match-any-rows-in-the-table-... 

[DonovanJack]

@danextian .... there were columns added in the Power BI dataset that are not im the orginial source data. Could this be the issue? (Bucket Order and Week columns were added)