If you used an excel file in a local source (or even OneDrive for Business, but sourced locally), you need to do one extra step to make your query refreshable without the need to the gateway. Here in this blog post, I’ll explain an easy way to do that change. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
On-Premises(Local) Excel Source
In the example below, I have created a Power BI report with Get Data from Excel, and I used the Excel file that is located in my OneDrive for Business directory:
After building the report, and publishing it to the Power BI service, in the schedule refresh, you will get to the place that you need to connect it to a gateway
You cannot schedule refresh the report unless you set up a gateway! If you don’t know what the gateway is, here is brief information about it: Gateway is an application that creates a connection from the Power BI dataset in the service to the local domain (on-premises) data source. Here in this article, I explained everything you need to know about the gateway.
As you see in the above expression, the gateway is needed for data sources that are on-premises. One Drive for Business is not an on-premises data source. So why do you get this issue of needing to set up the gateway? The reason is that our file is not sourced from OneDrive for Business. It is sourced from a local folder. To see where the data source of your Power BI leaves, click on Data Source Settings under the Transform Data in the Power BI Desktop.
Here you can see all the data sources, and you can click on any of those to change if you want to.
As you can see, the source in the above file is a local file in my local folder in C drive! Even though I have selected it from the OneDrive for Business folder, it is still pointing to the local folder representative of that. and as long as the local folder is the source, the gateway is need to get it refreshed in the service. Here is the quick solution for you.
Solution: Change Source to Web Query
You can change the source of your excel file to a web query, but you need to have the web URL address of that file. Here is how you can get it:
Find the Web URL Source
Open the Excel file locally, and then go to File menu, under Info, right-click on Open File Location, and then Copy Path.
The path should look like this (paste it into a notepad window):
You need to use this path now as the source
Change the Source to the Web.Contents
Go to Transform Data section of Power BI Desktop
In the Power Query Editor window, click on the Source step of your query.
If you don’t see the formula bar, go and enable it in the View tab;
Now change the File.Contents(“…”) part of the formula above with Web.Contents(“…”)
here is how I change mine from:
= Excel.Workbook(File.Contents("C:\Users\RezaRad\OneDrive - RADACAD\Data Sources\Pubs.xlsx"), null, true)
= Excel.Workbook(Web.Contents("https://radacad-my.sharepoint.com/personal/reza_radacad_com/Documents/Data Sources/Pubs.xlsx"), null, true)
Note that the address inside the Web.Contents are coming from the path that you have copied in the notepad in the previous step plus a forward slash (/), and then the file name and extension.
Web.Contents(“<path copied in the previous step>/filename.xlsx”)
And note that the Web should be written with capital “W”, and lowercase “eb”.
After doing this change for all your queries that are coming from the file from OneDrive, then click on Close and Apply in the Home tab.
Now this time, after publishing it to the service, in the Schedule Refresh tab, you can see that the gateway configuration is disabled, and you just need to Edit Credentials under Data Source credentials.
Set the credentials with OAuth2, and Sign in with the account which has access to the OneDrive for Business folder;
Now you can schedule the dataset to refresh (without the need to gateway):
Or you can run the refresh manually to test if it works fine.
I hope the method mentioned in this quick blog post, helps you in your Power BI implementation.