HOW TO CREATE DATA SOURCE PARAMETERS IN POWER BI

Parameters allow developers to create dynamic connection strings, enabling flexibility in connecting to different data sources or environments. In this blog, we will see how to use data source parameters in Power BI. It also includes a step-by-step exercise for practice.

BENEFITS OF USING DATA PARAMETERS IN POWER BI

Data source parameters provide the following advantages:

1. Creation of dynamic connection strings, enabling flexibility in connecting to different data sources or environments.
2. Parameters make it easier to manage and maintain Power BI reports. Developers can centralize connection details, making updates more efficient.
3. Reports become reusable across different environments, such as, Dev, QA and Prod.

---

STEPS TO CREATE DATA SOURCE PARAMETERS IN POWER BI

The steps to create and utilize data source parameters in Power BI are:

1. Load the required data.
2. Navigate to Query editor and create a new parameter by navigating to Hometab and then Manage Parameters.
3. In the Manage Parameters box, provide the name of the parameter, Type, Suggested Values and Current Value.
4. Replace the hard-coded path in the Advance Editor code with the parameter name.
5. Select the parameter and the data source connection will change based on the selected parameter value.

---

EXERCISE: CREATE A DATA SOURCE PARAMETER TO CONNECT TO DIFFERENT EXCEL FILES.

In the example below, we will create three folders and XLS files within each of these folders. We will connect to these files in Power BI based on parameter selection. 

To illustrate this concept, I have created three folders in my C:\ drive: Dev, QA, and Prod. Each of these folders contains an Excel file named CustomerData.xls. 

It has only two columns: CustomerName and Sales. The data differs in the three files. The structure of the XLS in Dev, QA, and Prod folders is shown below:

The structure of the file is same, but the data varies.

Now, follow the steps below:

1. Launch Power BI desktop and load the CustomerData.XLS from the Devfolder. Learn how to connect Excel datasource in Power BI.
2. Select Transform Data to navigate to the Query Editor. The data preview will display CustomerName as Dev as we are connected to the XLS in the Dev folder.
3. From the menu, click on the Advanced Editor; it will display the text as follows:

let
Source = Excel.Workbook(File.Contents("C:\PowerBI_Blogs\Dev\CustomerData.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CustomerName", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"

It displays the current file path as C:\PowerBI_Blogs\Dev\CustomerData.xlsx, which is the location where I have saved my XLS file.

Our objective is to replace this path with the parameter.

4. Create a parameter.

From the Home tab, select Manage Parameters and then select New Parameter.

Provide the following inputs in the Manage Parameters box.

• Name. Provide a name of the parameter.
• Type. This is the type of the parameter. In this case it will be Text.
• Suggested Values. It should be list of values, as we will be providing the values as Dev, QA, and Prod.
• Default Value. Select the default value from the drop-down which you wish to provide to the parameter.
• Current Value. Select the value from the dropdown.

5. Change the Advance Editor code.

Click on the Advanced Editor and change the hard-coded path with the parameter as shown below:

C:\PowerBI_Blogs\”& SourceParam &”\CustomerData.xlsx”)

the ampersand (&) symbols is used for the concatenation. 

The new Advanced Editor code (with the parameter) will look like the following:

let
Source = Excel.Workbook(File.Contents("C:\PowerBI_Blogs\"& SourceParam &"\CustomerData.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CustomerName", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"

6. Now, changing the parameter value will display the data from the respective folder.

Selecting QA from the parameter, displays the file from the QA folder.

---