Unable to connect to Sharepoint Excel file for data refresh - (500) Internal Server Error

[mugdhadesai]

I have been using Power BI for many months now. I am a Pro trial user now. Our data is updated in a Sharepoint Excel workbook every week and the Power BI service connects to it and the reports get refreshed as per the set schedule. But this is the first time that I am getting the below error-

 Nothing has been done differently this week. 

Please help as to what is the issue and how to resolve it. 

Thank you

Mugdha

[mugdhadesai]

I have been using Power BI for many months now. I am a Pro trial user now. Our data is updated in a Sharepoint Excel workbook every week and the Power BI service connects to it and the reports get refreshed as per the set schedule. But this is the first time that I am getting the below error-

Nothing has been done differently this week. 

Please help as to what is the issue and how to resolve it. 

Thank you

Mugdha

[v-haibl-msft]

@mugdhadesai

Does it refresh well in Power BI Desktop? If you are getting data from the excel workbook stored in SharePoint, you can try to modify your query as below in Query Editor. Reference document: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-onedrive-business-links/

let
    Source = Excel.Workbook(Web.Contents("https://***.sharepoint.com/***/Shared%20Documents/Lines%20in%20column%20chart%20staggered.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Branch", type text}, {"Sales", Int64.Type}})
in
    #"Changed Type"

Best Regards,
Herbert

[mugdhadesai]

@v-haibl-msft

Thanks for responding.

You are right, I understand that the issue is in the way in which my report is accessing the source data.

I have two reports based on two different excel files stored in the same SharePoint location.

One is giving the above error, another is refreshing fine.

Query for the report giving error :-

Query for the report working fine :-

There is a differece in how both are picking up data.

Can you please help me as to how should I modify the erroneous query - the excel contains multiple tabs - how to select data of a particular tab?

Even the query that is working fine takes data from an excel having multiple tabs.

The report is huge and it will be a big task to rebuild it. It will be good if I can modify the query and make it work.

Thank you

Mugdha

[mugdhadesai]

@v-haibl-msft

Trying to refresh in Desktop also gives same error.

Thanks

Mugdha

[v-haibl-msft]

@mugdhadesai

Please try to remove [ApiVersion=15] from the erroneous query, re-publish Power BI Desktop file and check if refresh works in Power BI Service.

Best Regards,
Herbert

[mugdhadesai]

@v-haibl-msft

I have tried that. When I remove [ApiVersion=15], the query works fine in query editor - the table in preview gets populated with latest data in query editor, but when I click on "Close & Apply", it gives the below error - 

Then when I sign in as different user and select my organizational account, it gives below error - 

So I am unable to apply the query changes - 

And if query changes are not applied, then no use re-publishing the report onto service right?

Thank you

Mugdha

[v-haibl-msft]

@mugdhadesai

Please try to change ApiVersion=15 to ApiVersion=14.

Best Regards,
Herbert

[mugdhadesai]

@v-haibl-msft

Thanks a lot.

Removing [ApiVersion=15] worked for most of the queries.

And the credentials error went away when I did "Switch Account" and did a re-login in Desktop.

But one query is not working, giving the below error - 

Below is the erroneous query - 

Remaining part of line 2 of above query - 

When I click on "Go to Error", it highlights the "Navigation" step in the right side pane after which I get below - 

When I click on "Edit Settings", it opens up a dialogue box giving SharePoint path and asking to select the required file. But after selecting the required file, it again gives same error.

Can you please help as to what may be the problem with this query?

In this query I also tried changing to ApiVersion=14, but it gives same error.

Thank you for your help

Mugdha

[v-haibl-msft]

@mugdhadesai

For the last problem, I suggest you to create a support ticket at http://support.powerbi.com (see bottom of page).
If issue is resolved, you can share the solution here later to help others who have the same problem.

Best Regards,
Herbert