Excel file in OneDrive as source for PowerBI report is a very common scenario. If you want to use an Excel file as a data source for your PowerBI report, what better place to store it rather than OneDrive or SharePoint.
Here is an issue I ran into, cannot refresh the report after publishing it to PowerBI service. It kept on failing with below error:
As a matter of fact, I right away knew something is wrong because when I try to Edit credentials, OAuth option is not available for authentication. Only Basic and Anonymous are available options in drop down.
I made sure I copied the correct link to Excel file by opening it in browser copying the link instead of generating a share link. I have removed ?web=1 from the URL. In my report, added the source as a ‘Web’ source instead of Excel. Authentication in PowerBI Desktop is setup using Organizational account. Deleted the data source in PowerBI service and republished it. Created it from scratch again but still ‘OAuth’ Authentication option doesn’t show up in PowerBI service.
After closing observing the error (above image) again, noticed that since I copied the URL from browser, it included the ‘/:x/r/‘ which Microsoft adds by default for browser compatibility. Updated the source URL and removed ‘/:x/r/‘ string from URL. Published it again to PowerBI service.
Problem Solved! This time, I see ‘OAuth’ as an Authentication option and refresh works as expected.
Hope it helps someone. DO NOT include ‘/:x/r/‘ string in source URL.