DataFlow to get excel file from Teams / Sharepoint

[leewsimpson]

I got this to work as a Dataset - however not very useful as I need a report with different data sources.

These are the steps I've tried to get the DataFlow to work:

New DataFlow

Choose Excel

Got the url from sharepoint.  If I paste the file URL in a browser I can download the file.

https://blah.sharepoint.com/:x:/r/sites/blahblah/Shared%20Documents/blah/blahblah/report%20210311.xlsx?d=wb8ff10cd95ba4dcd933d4f972cea626e&csf=1&web=1&e=VCRJsy

Choose Organizational Account.

Get Error:
There was an error in the OAuth popup window (Session ID: 8054b685-2b41-4024-9a62-77cfdad242b2)

Read somewhere that we need to remove the /:x/r

Reformatted url still works in a browser.  Now https://blah.sharepoint.com/:x:/r/sites/blahblah/Shared%20Documents/blah/blahblah/report%20210311.xlsx?d=wb8ff10cd95ba4dcd933d4f972cea626e&csf=1&web=1&e=VCRJsy

Now able to see OAUTH pop up and sign in OK.  Says “you are currently signed in”

Now get error:

An exception occurred: DataFormat.Error: The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part). (Session ID: 8054b685-2b41-4024-9a62-77cfdad242b2)

Remove the querystring at the end. 
https://blah.sharepoint.com/sites/blahblah/Shared%20Documents/blah/blahblah/report%20210311.xlsx
This still works directly in the browser.

Same format error:

An exception occurred: DataFormat.Error: The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part). (Session ID: 8054b685-2b41-4024-9a62-77cfdad242b2)

So what is the magical format that PowerBI will accept?

[blopez11 · Accepted solution]

You need to choose web as the datasource

You can follow this, it says one drive, but will work with sharepoint

I have done the same using dataflows with Excel file residing in Teams files

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

[titusadams]

Exactly what I needed to add my excel file to a dataflow. Thanks! Excited to see if i can track the changes over time as well.

[leewsimpson]

thank you - I chose web API and it worked as expected.  I wish the User Experience was cleaner - its all Microsoft so my expectations are higher!