Get Data from an Excel file in sharepoint + can't schedule refresh because of invalid credentials

[Anonymous]

Hi Power BI community,

I've created a report in Power BI Desktop where there are, as data sources  :

- a CSV file stored locally which contains the deliveries done during the day

- an Excel file stored in a Sharepoint folder where a key user could modify it once per day with the planned hourly capacity of the team to handle the deliveries.

The report works pretty fine in Power BI Desktop, when I update the Excel file from Sharepoint, the values are correctly updated in the report after refreshing it.

But this is when I want to publish the report into the Power BI Service that I start to face some issues.

There is no particular error during the publishing part, but after, when I click on manual refresh from Power BI Service, I've got a warning icon (see below) :

When I go to the dataset settings, I can find that the data source can't be refreshed because of invalid credentials.

When I click on Edit Credentials URL, I can see that I only got the Anonymous value as Authentification Method :

When I click on the Sign In button, I've got the following error message :

When I go back to Power BI Desktop I've checked the Data source settings and I can see that I've got Windows credentials type :

After some searches, I tried to clear the permissions and reconnect as Windows type, republish the report, but still the same issue.

I've also tried to Edit the Type to use the Microsoft Account but I've got another kind of error message : "The WWW-Authenticate header doesn't contain a valid authorization URI ..."

I've also tried to get data from Web with the URL of the Excel file from Sharepoint. Same situation, I can see my changes from the Excel file in the Sharepoint updated in the report in Power BI Desktop. 

But after publishing the report, I can't refresh manually from the Power BI Service.

The difference here is that when I edit the credentials I've got a Basic Authentification Method and when I enter the usual credentials used to connect to the sharepoint, I've got the following error message :

I don't know where is the issue actually.

I don't know if it's because of me and my mishandlings or if it's because of missing rights.

What I can say if that the domain account used for Power BI is not precisely the same as the sharepoint account.

But if it's the problem, then I couldn't see the updated values from Sharepoint in Power BI Desktop.

Thank you in advance for your help in this topic.

Best regards,

Vincent.

[GilbertQ · Accepted solution]

It looks like there is an issue with the connector

Can you confirm you have got the latest PBI Gateway installed?

[Anonymous]

Hi @GilbertQ 

I updated the PBI Gateway and it works now.

Thank you for your help !

Best regards

Vincent.

[GilbertQ]

That is awesome thanks for letting us know!

[GilbertQ]

Hi @Anonymous 

If this is your internal SharePoint server you should use Windows Authentication. And then when you publish to the Power BI Service you could also use the Windows Authentication again.

Can you also make sure that your data source used is the SharePoint Folder and NOT SharePoint online

[Anonymous]

Hi @GilbertQ 

How can I check if the URL that I'm trying to connect to is a SharePoint server or a Sharepoint Online ?

BR

Vincent

[GilbertQ]

Hi there if it is Sharepoint online it should be https://COMPANYNAME.Sharepoint.com

Otherwise it is On-Prem

[Anonymous]

Hi @GilbertQ 

Then it's a On-Prem Sharepoint because the URL is not like this https://COMPANYNAME.Sharepoint.com

But according to your first message, if it's not a Sharepoint Online URL, then I should use the Windows Authentication (which I did). And when I publish the report into the Power BI Service, I still don't have the opportunity to use the Windows Authentication method.

It just shows the Anonymous Authentication method.

Why it doesn't show the Windows Authentication method ?

Thanks,

BR

Vincent.