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?
