Excel Refresh Fails

[sarmbrecht]

When I try to refresh an Excel spreadsheet that is in a SharePoint library through Power BI the refresh takes a long time then errors out. Here is the most recent error (it's not very helpful):

Activity ID: 669bef00-d345-4b59-a62d-26ddbda3474c
Request ID: 8f9caf56-2ee8-415f-5683-4595852b79e1
Correlation ID: e05a813a-475d-a691-8c1c-3cb92fc97e0a
Time: Mon Jul 11 2022 15:00:02 GMT-0400 (Eastern Daylight Time)
Service version: 13.0.18578.38
Client version: 2206.4.12175-train
Cluster URI: https://wabi-us-east-a-primary-redirect.analysis.windows.net/

[Anonymous]

Hi @sarmbrecht 

Has this happened before ? If you refresh an Excel spreadsheet in Desktop , will there be such an error ? 

Best Regards,
Community Support Team _ Ailsa Tao

[sarmbrecht]

Yes this happens with every time I run the spreadsheet.  It runs for 1 hour then fails. 

See the first screenshot below.

I was able to create a Data Flow that ran the same queries successfully.  See the second screenshot below. I then copied the M back into the spreadsheet, set the primary query to load to the data model in the spreadsheet, uploaded the spreadsheet to the same environment as the data flow, updated the connection credentials, then refreshed the spreadsheet and it failed.  See the third screenshot.

Is there any way to get more detail on why the third screenshot below failed?  I have copied in the text version of the error details.

Failed Connections:	Query - Solutions Env Summary
Cluster URI:	WABI-US-EAST-A-PRIMARY-redirect.analysis.windows.net
Activity ID:	d6d28225-c641-4a0e-88b8-d63891bfbd33
Request ID:	74c409d1-69aa-aaba-5b71-7c770416e84c
Time:	2022-07-12 11:24:50Z

[sarmbrecht]

Also, when I refresh the same spreadsheet in the Excel desktop app I do not get the error.

[Anonymous]

Hi @sarmbrecht 

So you create report in Desktop with data from SharePoint library , then publish the report to Service , then refresh the dataset failed , right ? If yes , did you configure gateway in Service ? After all, to get data from local, you need to configure gateway .

Best Regards,
Community Support Team _ Ailsa Tao

[sarmbrecht]

No.  I am using Power Query in Excel Desktop to pull data from an API and perform some transformations to the data before presenting it as a table in Excel.  I then upload the spreadsheet to power bi and set it to refresh the spreadsheet on a schedule using the following steps:

1. Get Data

2. Choose Files

Choose SharePoint Teams Site

Enter URL to Teams site that has the excel file:

Select the Excel File.

SElect Connect

Then select Connect again

Then go to the file and select Schedule Refresh

Update the Credentials

Then click Refresh Now.  It always fails.

[sarmbrecht]

This is still an issue.  Is there some other place I should post this to get some support?

[sarmbrecht]

@Anonymous I do not know why this is marked as Needs Info.  Am I looking for help in the wrong place?  I still need help with this.  I am unable to automate any spreadsheets that we use to query data from Jira or Zendesk.  This should work.  Can you escalate this?

[sarmbrecht]

Can someone please respond to this ticket??  I have provided a LOT of detailed information above and it is still sitting in the NEEDS INFO status that is incorrect.  We are blocked from using power bi in the way we need to: We want to refresh an excel spreadsheet on a nightly basis.  It refreshes fine when I execute the refresh manually but power bi is failing on the refresh. @Anonymous @v-yadongf-msft @Anonymous @Anonymous 

[sarmbrecht]

I think I figured it out.  It seems that while you can refresh locally, Power BI will fail if you do not explicitly set the data type for every field.  I have done so and it seems to be working now. Hope this helps others.