Dataflow Can't Connect to SharePoint Excel File

[lg1551]

I have a PBI desktop report that I want to put into a dataflow. One of my sources is an excel file hosted on SharePoint. It runs in the desktop just fine but when I try to connect in the dataflow I get invalid credentials error or "unexpected error". I've tried Basic, Anonymous, and Organizational accounts, Excel, Web, Web API, and SharePoint as sources in the data flow and I get the same error.

The credentials used in the desktop are the same account credentials used in the service as well as the SharePoint site, so what gives?

[v-tangjie-msft · Accepted solution]

Hi @lg1551 ,

This issue has been submitted internally. Internal ICM ID: 341717747 . Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,

Neeko Tang

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

[mabl4367]

I have the same problem even though in the same data flow I'm able to connect to another excel file in the same sharepoint folder!

I even tried to rename the file to "test.xlsx" but I'm still told that I'm using invalid credentials even though I use the same as for the file that I am able to load.

I also tested opening the file test.xlsx in sharepoint and I have no problems.

[aj1973]

Hi @lg1551 

Dataflow is basicaly like Online Power Query using a connector to connect to Sharepoint. Do you have the Role/Permission granted to do so? If so you need to verify with Power Admin if you have the permissions to connect your Desktop to the Dataflow you build in the service.

[lg1551]

I do have those permissions. I have created several data flows for the organization but for whatever reason I can't link to the excel file in SharePoint without a credentials error. Yet I can log into sharepoint and access the file and even access it in Power BI desktop but the dataflow will not allow it for whatever reason. Not had this with other dataflows I've set up.

[aj1973]

Can you create a new Dataflow pointing to the same file in Sharepoint?

[lg1551]

I'm unable to do that as well.

We have an existing PBIX desktop file which points to the SharePoint excel file. I'm wanting to convert all of the SharePoint sources to a dataflow with autorefresh to save some time when refreshing the PBIX. But when I point the DataFlow to the SharePoint excel file I get the credentials error. It makes no sense to me. The same credentials are used in the service, as are in the desktop PBIX as are used in the SharePoint. It's my same organizational account. I will check with the admin and see if there is some permissions setting that needs adjusting in the service.

[aj1973]

I don't think going through Dataflows is the best approach, Sharepoint is already an online service and has good connection to Power BI service. Maybe you have anthore reason to build on top of Dataflows. Anyways, I think the way you connected or trying to connect to Sharepoint is defaulting in here. Can you give more details on how did you build your report on top of sharepoint? 

[lg1551]

The reason for the dataflow is because they use a ton of SharePoint sources, excel files, lists, etc that take a long time to refresh in desktop. If they are fed to the dataflows which auto refresh nightly then the desktop can grab the transformed data and load the visuals. I just finished a separate report and it went from a 10-12 minute refresh to a 2-3 minute refresh by moving the SharePoint sources into dataflows.