Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
leewsimpson
Frequent Visitor

DataFlow to get excel file from Teams / Sharepoint

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?

1 ACCEPTED SOLUTION
blopez11
Resident Rockstar
Resident Rockstar

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

 

View solution in original post

3 REPLIES 3
blopez11
Resident Rockstar
Resident Rockstar

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

 

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.

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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors