Dataflow Accessing a Sharepoint Folder with an xlsx

[DebbieE]

I am creating a dataflow and I have a sharepoint folder with an xlsx in it (We made a Teams area)

I have copied the Sharepoint Link

https://testsite.sharepoint.com/sites/ExampleTeamsFoldername/Shared%20Documents/General

And I have attempted to use it in the data flow.

I have asked our admin about this and "it reads as though data sources in SharePoint Online locations don’t need to be added to the enterprise gateway…?" So we initially thought it didnt need a Gateway.

Attempting to go with this gets the following error

Sharepoint Request Failed. The remote Server returned an error (400) Bad request

So then I tried to use the Gateway

I dont think this is a data source on the Gateway. So before continuing, should this be a data source on the Gateway or and I doing this incorrectly?

Thanks in advance

[v-shex-msft · Accepted solution]

Hi @DebbieE,

I'd like to suggest you use an excel connector with an excel URL to get data on dataflow.

https://testsite.sharepoint.com/sites/ExampleTeamsFoldername/Shared%20Documents/General/test.xlsx

Reference link:

Use OneDrive for Business links in Power BI Desktop 
Regards,

Xiaoxin Sheng

[Anonymous]

You shouldn't need to use a gateway if you're using SharePoint online... If your company is using SharePoint server on-prem, then it will need to go through the gateway. 
I have several dataflows connecting to sharepoint lists and folders, and have received the error a couple of times, I usually just need to edit the credentials and make sure that I'm signed in.

Hope that helps!

Kim

[DebbieE]

 my credentials are fine. 

When I log in Via Desktop with the Sharepoint Folder I get no issues.

When I do the same thing in Service I get the error

[v-shex-msft · Accepted solution]

Hi @DebbieE,

I'd like to suggest you use an excel connector with an excel URL to get data on dataflow.

https://testsite.sharepoint.com/sites/ExampleTeamsFoldername/Shared%20Documents/General/test.xlsx

Reference link:

Use OneDrive for Business links in Power BI Desktop 
Regards,

Xiaoxin Sheng

[DebbieE · Accepted solution]

I also got around it by doing it in Desktop.

Then in Service creating a blank query and pasting the M Code into this query. Then it worked

[Quentin]

Hello Debbie E, 

I think i'm having the same issue of remote server returning an error. 
I was already pasting the advanced editor in my dataflow advanced editor and over there it works i can see and transform the data in the Query of the dataFlow, then when i close de dataflow and set the refresh, the refresh last something like 40min and fail for this message: remote server returning error