I get this question asked in webinars, speakings, and comments on my blog posts that Is it possible to use a local file as a source in Power BI and schedule that to be refreshed? The answer is Yes, of Course. You can host your files locally or on a server on premises, and then use gateways to create the connection from Power BI website to the local file. And it will be able to schedule automatically. In this post I will show a full walk through of using an Excel file on my local machine as a source, and scheduling that to be refreshed from Power BI service (or website). I won’t go into details of what is gateway, or how to build a sample report in Power BI, if you are interested to learn more on those subjects read posts on Power BI online book; From Rookie to Rock Star.
Install and Configure Gateway
There are two gateways that allows us to connect from Power BI service or web site (which is a cloud service) to on-premises data sources; Personal and Enterprise gateway. There are some differences in these two gateways which I will explain in separate post. In this example I will be using Enterprise Gateway to connect to on-premises Excel file. You can download the gateway from the Power BI website after you logged in, through options mentioned in screenshot below;
Then choose Enterprise Gateway for this example
After downloading follow the setup wizard with few steps and you will have Enterprise gateway installed with few clicks. There are some limitations though; You cannot have Enterprise and Personal Gateway on the same machine. I won’t talk about their differences, that would be a whole separate post which I will write later. In this post we only go through an example of using this gateway.
After installation, you can configure your gateway. All you have to do is to sign in to your Power BI account from gateway, set a name for your gateway and a code or recovery key. then you will be good to go. I’ve already setup my gateway and named it as RezaSurface. Now I can manage my gateway through Power BI website, through Setting icon, and then Manage Gateways.
In the Manage Gateways page I can see the gateway I’ve configured named RezaSurface. And I can also see that it is online with a green checkbox close to it. which means everything works correctly.
Now I can create a Data Source for the file that I want to use as a source. Click on Add Data Source (numbered 2 in screenshot above). as you can see in screenshot below I name the new data source as Excel On Prem, and choose data source type as File, and set full path of file. This should be the local path of file in the computer that has gateway installed on it. Also I enter Windows username and password for the computer that has enterprise gateway installed on it. This username and password will be used to access the local file.
Note that for this example I will be using sample Cashflow Data.xlsx file which I previously built a Waterfall chart with it. If you want to learn more about building a waterfall Power BI chart, read this post. After adding data source above I can see the connection is successful to my local excel file.
Deploy Power BI Solution
Power BI solution that I have is a simple waterfall chart on a cash flow data table in an Excel source. Here is my sample excel source table:
and here is the waterfall chart I’ve built for the data set above in Power BI Desktop
If you need more details information about how to build report above read Waterfall Chart blog post here.
I named this Power BI file as Excel On Prem, and publish it to my workspace in Power BI
Now I can see my report in Power BI website;
Now that I have my report and data set published in Power BI service, and gateway configured with the data source, I can connect these two together and schedule refresh.
Click on ellipsis button besides the Excel On Prem data set and choose Schedule Refresh.
Then I will be redirected to Settings page where I can configure settings for this data set. As you see in screenshot below, I expand Gateway connection, and I choose using an enterprise gateway which I’ve already created a data source for this file there before. and then I apply changes.
The connection between the data set in my Power BI report and the data source in gateway is now created. and I can Schedule Refresh based on the schedule I would like daily or weekly…
Testing the Refresh
Now If I change the Excel file in my local folder as below; start of my cash flow now changed to 8000$ instead of 5000$;
After schedule refresh run (or even after manual refresh of the data set on Power BI site), I can see the report refreshed as below;
You can see that the waterfall chart starts with $8K which is the new value from local Excel file.
In this post you’ve learned how to use gateway to create connection between on-premises source file and Power BI website, this functionality will expand your Power BI solution to use on-premises source files as source and schedule automatic refresh on those. Note that you can apply this on any files, it shouldn’t be only Excel files. There are also options for using a folder as source which is useful when you have multiple files with same structure in a folder on-premises. Think about ways that this functionality can help your solutions.