#2 Enterprise capabilities in Power BI - Dynamic data sources

[ankitpatira]

This time we will look at the new way of connecting dynamically to a data source within power bi desktop. As with monthly power bi desktop updates we see new features getting released that makes it even more enterprise capable and solve real world problems that we face in day to day work. Power BI Desktop update for July 2016 included ability to reference queries as a source for parameters and so we are going to look at how to achieve dynamic selection of source using query parameters.

We will look at one of the most common scenario where you have an automated process that drops files daily with date stamp to a shared folder to be picked for analysis and reporting.  Files contain raw data for some events and different teams within your organisation need to use it for their purpose analysis and reporting. Since files get dropped daily there is a need to be able to connect to file from a specific data and only import data within that file. You working for central BI team within your organisation and so you want to create and distribute power bi report template that different teams can use to connect to these files and only import file that is relevant for the time period they are interested in.

So to address that scenario we will create data source that will list all the files available within that folder and then use query parameter which will have that as a source. Finally we will have actual file data source that contains data from the file that will dynamically change based on query parameter selection. If you are not aware of query parameters and templates and how they work in power bi desktop I would suggest reading part 1 of this article as I have assumed you’re familiar and have used them before.

Source Files

For this purpose we will use sample road crash statistics data files that are dropped to a shared folder as shown in the screenshot below.

Create folder data source

Purpose of this data source is to provide list of available files within shared folder that users within different teams can choose to import data from. In Power BI Desktop under Query Editor click New Source -> Folder and connect to folder that contains files. As seen below you will get list of all the files within that folder. Since the result contains other columns with whole bunch of information related to the folder we will use Name column to convert the result into the list of all file names within that folder. To do that right click Name column and click Drill Down.

Create Query Parameter

Next create new query parameter as shown below. One of the updates for July power bi desktop was the ability to have another query as a source for parameter. So here we will use folder query created above as a source for parameter. One of the limitation of this was that we could only use query that returns list output as a source for the parameter and so we converted output of folder data source as list.

Import File

Finally we start by importing one of the csv files as our data source. At this point we will end up with two data sources one for the folder and one for the actual file along with parameter. To be able to access that parameter within power bi desktop right click parameter name and make sure Enable Load is selected. At last so that file data source connects and imports file that we select via parameter replace part of the file data source code via Advanced Editor.

I would also hide folder query as that is not required for the reporting purpose and only as a source for parameter. At last when this exported out as power bi template different teams can use that template to connect to see list of available files within shared folder and import only the selected one.

[yoshihirok]

Nice article.

Query and Parameter and M are nice work.

[Beyondpoojah]

Hi ,

Can you share  the M code that you used to import the file or  from where we can learn M  better.

[Krunalbpatel]

Hi,

Can you please Explain how you import the file? And How can I change report file dynamically after publish report?