CSV file from SharePoint - refresh - credentials issue

[sevenhills]

Hi 

a) I have csv file hosted on SharePoint site (all within organization)

b) I can connect and consume in the data model using the power bi desktop (on my laptop)

         FYI, I can browse csv file using browser 

c) I published the data model to Power BI service (premium space)

       and I supplied my credentials to CSV file 

I can sign in successfully. But, When I try to refresh I get an error, where as my other colleague tries, it refreshes with out an error!

Error:

<pii>The credentials provided for the SharePoint source are invalid. (Source at https://mycompany.sharepoint.com/sites/myprojectdev.)</pii>. The exception was raised by the IDbCommand interface. Table: ...

Any idea what is going on?

Regards

S

[v-robertq-msft · Accepted solution]

Hi, @sevenhills 

According to your follow-up, I make some additional test, first I change the colleague account to a member of the site, then I use his account to refresh the dataset again, I still succeeded:

This result indicates that both member and admin of the site can refresh the dataset.

Then I wonder if it’s because you don’t have access to the root website. I changed to another account outside the organization, which can not access the root website. Then I added it to my site, it showed as a guest in the list:

Then I use this account to take over and refresh the dataset, I tried manual refresh and scheduled refresh, you guess what? They both succeeded!

Therefore, I think that your member access to the SharePoint site is enough for you to refresh the dataset. I suggest you to create a new workspace and publish the report again to the new workspace and try to refresh the dataset.

What’s more, my share point site is a private group, what’s your group type?

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft · Accepted solution]

Hi, @sevenhills 

In my opinion, the refreshing dataset has nothing to do with the workspace if you are the workspace admin. It’s only related to the credential problems, Here are the things you should check again:

• Edit the credential using the OAuth2 method and login with the correct account.
• Make sure you can refresh the dataset successfully in the Power BI desktop when you click “Refresh”.

If you still can not solve this problem, I suggest you to mark this reply as a solution and go to create a Power BI support ticket for better help

How to create a support ticket in Power BI

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft · Accepted solution]

Hi, @sevenhills 

According to your follow-up, I make some additional test, first I change the colleague account to a member of the site, then I use his account to refresh the dataset again, I still succeeded:

This result indicates that both member and admin of the site can refresh the dataset.

Then I wonder if it’s because you don’t have access to the root website. I changed to another account outside the organization, which can not access the root website. Then I added it to my site, it showed as a guest in the list:

Then I use this account to take over and refresh the dataset, I tried manual refresh and scheduled refresh, you guess what? They both succeeded!

Therefore, I think that your member access to the SharePoint site is enough for you to refresh the dataset. I suggest you to create a new workspace and publish the report again to the new workspace and try to refresh the dataset.

What’s more, my share point site is a private group, what’s your group type?

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sevenhills]

Hi @v-robertq-msft 

Appreciate your detailed try out. Wow! your commitment is impeccable. 

• Thank you for confirming member access is good enough. (your proved it right, check below my findings)
• Yes, it is a private group and only few selected people has access it

• I created temporary workspace and copied the dataset and refreshed and it works. Now, with your help, it ruled out that it is not root SharePoint access and not Sharepoint access related. it turns out something to do with Power BI workspace. 

Sorry, my bad, as I have other datasets from other external SharePoint sites and they are working fine. These external sites, where I am admin/owner of the SharePoint site and has root site access, which mislead to different thoughts. Appreciated your help. 

This is getting interesting...

• Sharepoint - I am member
• Workspace (error) - I am member and gets error when refreshed (credentials issue)
• Workspace (works) - I am owner/admin

Regards

S

[v-robertq-msft]

Hi, @sevenhills 

According to your description, I also make some tests to reproduce your situation, I created a report based on a dataset connected to SharePoint, and I can get the dataset refreshed successfully without gateway, like this:

Then I shared the workspace to my collegue’s username account(his account don’t have access to my sharepoint site level), then I used his account to take over and refresh the dataset, I got the same error:

Then I go to my sharepoint site to add him as as admin of my site:

Then I update the credential using OAuth method and refresh again, I succeeded:

Therefore, I think the admin of the sharepoint site is enough to get the credential to refresh the dataset, you should check these things:

1. If you are the admin or the member of the sharepoint site
2. Have you updated the dataset and use OAuth method then sign in the correct account.

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sevenhills]

Hi @v-robertq-msft 

Thank you for testing and confirming the issue I am facing.

Yes, I am in the same situation and do not have admin access to the site "myprojectdev" but have member access. And do not have root sharepoint online access. Now that explains why I was not able to process the data. Glad you provided the answer in details. I will request for Admin access meanwhile for the site "myprojectdev". 

Follow-up, 

- I can browse the files using browser. I am just wondering why it asks for / need admin permission?

- I am doubtful they will give admin access to me. In such case, Is there any alternative?

Regards
S

[v-robertq-msft]

Hi, @sevenhills 

According to your description, you said that the other colleague can refresh the dataset successfully but you failed. Do you use the same credential as they used? You should also check if you have used the correct link to connect to the share point:

To connect to Excel that is located under Documents of SharePoint online site, please use the URL below.

https://xxxxx.sharepoint.com/sites/<sitename>/Shared%20Documents/<Yourexcename>.xlsx

To connect to Excel that is located under a folder in SharePoint online Documents, please use the URL below.

https://xxxxx.sharepoint.com/sites/<sitename>/Shared%20Documents/<Foldername>/<Yourexcename>.xlsx

Then in the upper-right corner of the Power BI service, go to the gear icon  ->Manage gateways., tick on the option "Allow user's cloud data sources to refresh through this gateway cluster" like below, click "Apply". 

You can learn more: https://docs.microsoft.com/en-us/power-bi/service-gateway-mashup-on-premises-cloud.

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sevenhills]

I am sorry, unfortunately your reply to the post does not matches what I am looking.

• I have csv file and you are replying and talking about .xlsx file
• I have both sharepoint and power bi service as online (cloud) and you are saying gateway. we don't need gateway if they are both on cloud online. Isn't it?

My colleague credentials works and mine does not work. we both have same privileges at Sharepoint site level "https://mycompany.sharepoint.com/sites/myprojectdev"

Only difference is I don't have access to https://mycompany.sharepoint.com/

and my colleague has access to https://mycompany.sharepoint.com/

Thanks

[sevenhills]

Unfortunately, I cannot get root site access.

Has to see if anyone else has solution 🙂

Thanks for your replies

[StefanoGrimaldi]

it shouldnt matter, but for some reason sometimes when configuring to a specific site the credentials configurations fails on refresh on power bi desktop or service, and solved normally by giving access to root. 

[sevenhills]

I tried the way you asked me to change the datasource as "https://mycompany.sharepoint.com"

and I get the error as "The key did not match any rows"

I don't have access to the root SharePoint site of the company. I only have to site myprojectdev. Does it matter?

[StefanoGrimaldi]

change the source adress on the power bi desktop specific site setting sometime generate problemas

change https://mycompany.sharepoint.com/sites/myprojectdev.)</pii to https://mycompany.sharepoint.com

so the credential get to the source sharepoint instead of a site this solve this kind of problems normally 

check: https://community.powerbi.com/t5/Desktop/Seek-voluntarily-to-help-to-access-my-SharePoint-folder/m-p... 

[StefanoGrimaldi]

you should check on your gateway configuration amke sure you have no gateway configure for the sharepoint source, instead go to the main gateway configuration and check the it to use conection from web: 

[sevenhills]

FYI, We dont need gateway. CSV file source - sharepoint is online version only. (Power BI service is also online version only)

Thanks