Configuration of Power BI Gateway Data Sources For Files And Folders

Recently I’ve been building a lot of Power BI reports from csv and Excel files, and to make sure that scheduled refresh works I have been setting up data sources in an On Premises Data Gateway (what used to be called the Enterprise Gateway). I had assumed that if I was connecting to file-based data sources in my Power BI dataset then, in the gateway, I would need to set up one data source for each file that I’m connecting to – which is a bit of a pain. In fact it turns out that you can set up a gateway data source for the folder that the files are in instead.

Let me give you an example. Imagine that you have three Excel files in a folder called C:\Sales Data:

Now imagine that you have three queries in Power BI that get data from these three files:

Here’s an example of the M code for one of these queries:

1 2 3 4 5 6 7 8 9

let     Source =         Excel.Workbook(         File.Contents("C:\Sales Data\SalesData_1.xlsx")         , null, true),     SalesDataTable_Table =         Source{[Item="SalesDataTable",Kind="Table"]}[Data] in     SalesDataTable_Table

There’s nothing really to notice here except that the code uses File.Contents() to get the data from a single file – I’m not using Folder.Contents().

However, once the report has been published only one data source needs to be set up in the On Premises Data Gateway for it to refresh successfully, even though the report connects to three different files. Here’s a screenshot of the gateway data source I set up in the Power BI service:

Two things to point out:

• The data source type is set to Folder
• The full path property is set to the path of the folder that the files used by the report are in, ie C:\Sales Data