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;

Schedule Refresh

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.

Summary

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.