cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DonovanJack
New Member

Expression.Error When refreshing an excel file from Sharepoint

DonovanJack_2-1653459405174.png

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.

 

DonovanJack_1-1653459280547.png

 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 

 

1 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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

12 REPLIES 12
MFelix
Super User
Super User

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:

 

MFelix_0-1653467263885.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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?

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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_0-1653579977774.png

 

@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!

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix @danextian   @amitchandak @DataInsights 

This is the Source step:

DonovanJack_0-1653511661283.png

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
Community Champion
Community Champion

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-... 




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

@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)

 

DonovanJack_0-1653492512500.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors