SQL and Excel Failed Schedule Refresh

[Anonymous]

Good Afternoon 

I have a Dashboard with SQL data and Excel data. On BI desktop the PBIX will complete a manual referesh absoulty fine. How ever fails on a Scheuled refresh. All excel docuemts are saved as .xlsx and we have installed the latest Gateway. 

Below is a copy of the failed report...

• Underlying error code-2147467259
• Underlying error messageCould not find a part of the path 'Q:\Resource and Planning\Cameron\U&R Raw.xlsx'.
• DM_ErrorDetailNameCode_UnderlyingHResult-2147467259
• Microsoft.Data.Mashup.ValueError.DetailQ:\Resource and Planning\Cameron\U&R Raw.xlsx
• Microsoft.Data.Mashup.ValueError.ReasonDataSource.Error
• Cluster URIWABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net
• Activity ID57692d3a-73e9-42f8-bcc3-6301ff8621c0
• Request IDf18ffb8a-51bb-4fc8-b063-181ed5da26ea
• Time2021-04-07 14:15:37Z

  Copy

  Again on the BI Websitethe Datasource is connected fine

  

  Im confused. Worked fine in January 

  please help 🙂

  Ian 

[HarishKM]

@Anonymous Hey 
Use below link .

Troubleshoot the issue 

You have update your data credential .

then your gateway will work fine .

[Pragati11]

Hi @Anonymous ,

Can you try modifying your path by removing any spaces in the FOLDER name? In the path you have spaces in folder names and your excel file name.

 'Q:\Resource and Planning\Cameron\U&R Raw.xlsx'

Use "underscores" instead of spaces in filename.

Spaces that I see in the path is:

1. Q:\Resource and Planning\Cameron\U&R Raw.xlsx
2. Q:\Resource and Planning\Cameron\U&R Raw.xlsx

Please correct these and try again.

Thanks,

Pragati

[Anonymous]

many thanks, i have made the adjustments as you recomended. But still failing to refresh 

[Pragati11]

Hi @Anonymous ,

The error you are getting clearly says your filepath is not right.

You need to use forward slash "/" in your filepath. You are currently using backward slash which is not recognised in this form correctly.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 upon looking at my PBIX i am unable to changet the \ to /. The file path was determined when i added the excel file to Power BI

[Pragati11]

Hi @Anonymous ,

Can you share some screesnhot please? It is hard for me to understand why you are not able to change the path.

Also, I realised this path points to a "Q drive" . Does this drive exist somewhere locally on your machine?

Thanks,

Pragati

[Anonymous]

Hello, @Pragati11 So i managed to change the path names through Transform Data on the PBIX 🙂 However one report is still gining me an issue. See below

The Q drive is on our business server along with the Gateway

[Pragati11]

Hi @Anonymous ,

Glad that you were able to fix the other error by changing the path. I suspect same reason for this error as the error is very much similar to your earlier one.

Does this M drive exist?

[Anonymous]

Hi @Pragati11 so all the paths are mapped correctly in the PBIX and gateway. When the system fails a scheduled refresh it shows the error code with a \ even though they have been changed 🙂

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Can you please check-up the credentials set-up for this file in Power BI service for setting up scheduled refresh?

The error points to invalid credentials.

Thanks,

Pragati

[Anonymous]

hi @Pragati11  i have my IT team working on this. I have carried out the actions you have recomneded. But we may have a Gateway credential issue. Thanks for the support