I have been answering some questions on the Power BI (Or is it now Fabric?) community forum, and I have found recently that there are some people asking how to get data from Excel that is stored in SharePoint Online.
In this blog post I will show you how to connect to a single Excel file using Power BI desktop, and then configure it in the Power BI Service where it does not require a gateway.
In my example I have got the following file as shown below which I have saved to my SharePoint Team Site.
The trick here to making sure that I do not need to use a Power BI Gateway is in the way that I connect to my Excel file.
To do this I HAVE TO USE THE DATA SOURCE CONNECTION of WEB.
In order to get the WEB URL I do the following which I learnt from Chandoo How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)
I clicked on the three dots, then details and then I scrolled down to where it says Path as shown below.
I clicked on the option to “Copy direct link”
I then went into Power BI Desktop, clicked on “Get Data” and then selected “Web”
Next, I pasted in the direct link I had copied above.
I then clicked OK. (If I was prompted, I put in my Organizational User name and password)
Next, I then selected my table called “Budget” I had created earlier and clicked Load (Typically I would want to do some additional work in Power Query, and I would select Transform Data, but in this example I am looking to show you how I can refresh an Excel file without using the Gateway)
Now I needed to save my PBIX file and upload it to the Power BI Service.
Now that I had uploaded my PBIX file the next step is where I would now configure the data source connection details without the need for the Power BI gateway.
I navigated to where my dataset is stored.
When I got to the settings page, I could then see that there was an error as shown below.
The reason for this error is that when I uploaded my PBIX file it attempted to refresh the dataset. As shown above the refresh failed the connection test so it could not refresh the dataset.
In order for me to get the dataset to refresh and to NOT use the Power BI Gateway, I had to click on Edit credentials under the “Data source credentials”
I was then prompted to click on the derop down for the Authentication method and select “OAuth2”. At the same time, I set the Privacy settings to “Organizational”.
I then clicked on “Sign in”
I then signed in with my account.
Once I had signed in and authenticated, I got a confirmation in the top right-hand side in the Power BI Service as shown below.
And now I could also see that my data source credentials no longer had the triangle highlighting an issue.
My final step was to now test and see if I could successfully refresh the dataset.
I could now see that it was indeed successful.
And one final check to make is that if I expanded the “Gateway connections” I can see that it is telling me that I do not need a gateway.
In this blog post I have gone through the steps on how to get the Excel URL, then connect to the data using the Web connector. Finally, I explained how to configure the data source credentials so that it would not require the Power BI gateway to refresh the Excel file.
As always, I do appreciate you reading my blog post and I hope you found this helpful.
If there are any questions or comments, please leave them in the section below.
Be the first to comment