Dashboard in PowerBI: I — Employ a data source switch

Switch easily between alternative data sources

In this short tutorial series, we will see some advanced techniques or tricks for loading data into PowerBI Desktop. I will demonstrate them on a use case of a sales dashboard. Nevertheless, they can be useful in any other PowerBI project.

This series is intended to medium skilled users of PowerBI Desktop (at the level of MS Data Analyst Associate). The solutions presented in this series were tested on:

• MS PowerBI Desktop, Version: 2.87.923.0
• R 4.0.2

The PowerBI report and data can be downloaded from GitHub.

A. Why a data source switch

Imagine you are developing a dashboard with a company’s sales data. Now you want to present your dashboard to other potential clients. You cannot display the real data. You need to generate mock data and you have to change paths in each imported query.

One obvious solution would be to use a global variable, but global variables do not exist in PowerBI Desktop. Another approach is to give the PowerBI file a different name and change the paths once for all. What is the deal? Later, you update your real report and you need to change the paths again. Finally, you might save your report, swap the source directory with your mock data and refresh the report. But moving the data directory temporarily is not a good practice since it brings chaos into your data structure.

The solution presented here is a surrogate for global variables.

B. How to create the switch

1. Import your source file as a new query.
2. Create a table of data sources and their paths (Enter data).
3. Create a function that will return the required data source.
4. Replace the paths with the function call.

C. Steps in pictures

1. Import your source file as a new query.

a. Import your data source

b. See the report with your “real” data

2. Create a table of data sources and their paths (Enter data).

Create a query called SourcePaths. The Path column contains the actual folder paths. The other column just describes the source and is not obligatory.

c. Specify your alternative data source paths

3. Create a function that will return the required data source.

Add a Blank Query and enter this definition:

= () => SourcePaths[Path]{0}

The parentheses ( ) can contain a parameter, which we do not need here. The curly braces refer to a line in our table.

d. Create a function returning the required data source

4. Replace the paths with the function call.

Change the absolute path to:

PickSourcePath() & "YOUR-FILE-NAME.CSV"

e. Change the absolute path to the function call

Now, change the function definition to point to the other source.

f. Switch the data source in the function definition (change the index from 0 to 1)

The report after the change:

g. The report with the ‘high’ data (see the changed scale)

Summary

This article has showed how to parameterize data source by mimicking a global variable, a feature missing in PowerBI. This concept might be applied in other scenarios where a constant needs to be shared within a report.

The code for this part can be downloaded directly from GitHub.