Creating and Managing Parameters in Power Query Editor

A parameter in power query editor is used to store any value and use it in the power query. The same parameter value can be used multiple times in power queries in the Power BI.

Example: Let’s create a table in the Power Query.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [EmployeeID = 1, Name = "Ashish", Country = "India"], 
      [EmployeeID = 2, Name = "Katrina", Country = "Australia"], 
      [EmployeeID = 3, Name = "Alia", Country = "China"], 
      [EmployeeID = 4, Name = "Vicky", Country = "India"], 
      [EmployeeID = 5, Name = "Mohini", Country = "Japan"], 
      [EmployeeID = 6, Name = "Meenakshi", Country = "China"], 
      [EmployeeID = 7, Name = "Esha", Country = "India"], 
      [EmployeeID = 8, Name = "Anjali", Country = "Australia"],
      [EmployeeID = 9, Name = "Yashoda", Country = "India"],
      [EmployeeID = 10, Name = "Zoha", Country = "Japan"]
    }
  ), 
  return = Table.SelectRows(MyTable, each ([Country] = "Japan"))
in
  return 

The output of the above code is shown below:

Creating and Managing Parameters in Power Query Editor

In the above code we can see that we are filtering the rows based on the “Country” column value. Right now, it is static to value “Japan”.

To make it dynamic, we can use Parameters in Power BI. By using the following steps, we can create a parameter in Power BI.

Step 1: In the Power Query Editor, we can easily manage our parameters inside the Manage Parameters window. To get to the Manage Parameters window, select the New Parameter option inside Manage Parameters in the Home tab.

Creating and Managing Parameters in Power Query Editor

The Manage Parameters is opened as shown in the image below:

Creating and Managing Parameters in Power Query Editor

Step 2: Set the name of the parameter. Fill other details, and then select OK to create a new parameter.

We can also specify the Description to the parameter, which will be useful to undertand the purpose of creating the parameter. The Required checkbox indicates whether a value for the parameter must be provided.

From the Type, select the datatype of the parameter.

Creating and Managing Parameters in Power Query Editor

From the Suggested Values dropdown select Any value. The Current Value specifies the value that's stored in this parameter. Type Australia for the Current Value.

Creating and Managing Parameters in Power Query Editor

We can see in the image below that the parameter is created.

Creating and Managing Parameters in Power Query Editor

Step 3: Now change the Query code as shown below:

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [EmployeeID = 1, Name = "Ashish", Country = "India"], 
      [EmployeeID = 2, Name = "Katrina", Country = "Australia"], 
      [EmployeeID = 3, Name = "Alia", Country = "China"], 
      [EmployeeID = 4, Name = "Vicky", Country = "India"], 
      [EmployeeID = 5, Name = "Mohini", Country = "Japan"], 
      [EmployeeID = 6, Name = "Meenakshi", Country = "China"], 
      [EmployeeID = 7, Name = "Esha", Country = "India"], 
      [EmployeeID = 8, Name = "Anjali", Country = "Australia"],
      [EmployeeID = 9, Name = "Yashoda", Country = "India"],
      [EmployeeID = 10, Name = "Zoha", Country = "Japan"]
    }
  ), 
  return = Table.SelectRows(MyTable, each ([Country] = #"Country Parameter"))

//Here, the Country Parameter is the name of the parameter.
//As it contains space, so we are using # symbol in front of it
in
   return 

In the output of the above code, we can see the records from the table where Country value is “Australia”.

Creating and Managing Parameters in Power Query Editor

Step 4: We can also edit the existing parameters in the Power BI at any time. For example: we can change its current value, as shown in the image below.

Creating and Managing Parameters in Power Query Editor

We can see the corresponding effect on our data, as shown in the image below:

Creating and Managing Parameters in Power Query Editor

The data only shown is for country China.

Step 5: Right now, we can see the data in the Power BI report view.

Creating and Managing Parameters in Power Query Editor

We can change the value of the parameters from the Power BI report view. In the Power BI report view, click on Transform data, and then click on Edit parameters.

Creating and Managing Parameters in Power Query Editor

We can edit the parameter value and click on Ok. Let’s edit the parameter value to India and click on Ok.

Creating and Managing Parameters in Power Query Editor

Here the current value we are specifying to India. We can see the data of the country India as shown in the image below:

Creating and Managing Parameters in Power Query Editor