Query Parameters in Power Query – Part 2

As promised, here is Part 2 of Query Parameters in Power Query.  My last blog demonstrated how this feature (released in April 2016) could be used to reduce the data volumes in your PowerBook.  This article focuses on how you can easily switch between different parameter values and dynamically display the results. 

Query Parameters are fully explained in Part 1, so I will move straight on to the exciting stuff!

Use Cases & Example

There are plenty of scenarios when you want to filter large data sets based on various criteria.  A typical business case could be looking at a customer table (in SQL Server, a text file, etc.), where the user wants to return only data by a person’s First Name, Last Name or City in which they are from.  This would require more than one parameter if we want to make our filtering dynamic. 

I will talk you through a slightly different example.  Imagine a user wanting to look at the current products their business is selling, but look at the data by:

1.       Product Name ONLY = EnglishProductName

2.       Product Category, Product Sub Category & Product Name = EnglishProductCategoryName, EnglishProductSubcategoryName & EnglishProductName

Query Parameters can easily facilitate this.  To follow my example, download AdventureWorksDW2014 Database (found here) and connect through SQL Server Database. Paste the following SQL Code into the SQL Statement box – like below:  

SELECT DISTINCT

              –DPC.[EnglishProductCategoryName] AS CategoryName,

              –DPS.[EnglishProductSubcategoryName] AS SubCategoryName,

              –DP.EnglishProductName AS ProductName,

              DP.ProductAlternateKey,

              DP.ListPrice As ProductListPrice,

              COALESCE(DP.[Status],‘Previous Version’) AS ProductStatus

FROM   [dbo].[DimProduct] DP

INNER JOIN

              [dbo].[DimProductSubcategory] DPS

              ON DPS.[ProductSubcategoryKey] = DP.[ProductSubcategoryKey]

INNER JOIN

              [dbo].[DimProductCategory] DPC

              ON DPS.[ProductCategoryKey] = DPC.[ProductCategoryKey]

ORDER BY

       DPC.[EnglishProductCategoryName],

       DPS.[EnglishProductSubcategoryName],

       DP.EnglishProductName

NOTE:   You will see the first three columns are commented out.  This is because we will be referencing them in our parameter value.

Switching between Parameter Values

I will now demonstrate how to create 2 parameter values that will be used to display the product data in different ways.

The following steps show you how to achieve this.  

1.       We now want to create the new parameter, in the Query Editor, click Manage Parameters from the ribbon.
 

2.       Create one parameter and select ‘List of Values’ as the Allowed Values drop down.  You will see there are two types of list output – one for just EnglishProductName and the other amalgamating the three product columns.   Click OK when happy.

The code for the ‘concat’ function is:
Concat(EnglishProductCategoryName,’ – ‘,EnglishProductSubcategoryName,’ – ‘,EnglishProductName)

Note:     As we are connecting to SQL Server, the ‘Concat’ function is required to bring the product columns together.

3.       Rename the query to ‘DimProduct’.  We now need to insert the parameter into the DimProduct – by clicking the Advanced Editor and replacing EnglishProductName with our new parameter – like below:

4.       You will see the following warning message.  Click Edit Permission.

5.       The message is just warning you that as you are connected to the database, the query could make changes to the underlying objects.  As this is a ‘Select’ query, there is no danger of this.  Click Run.

6.       As we set the default value to be EnglishProductName, this is what we now see in the query pane.  The field in question is called ProductName.

7.       To switch to the other parameter value, simply click Manage Parameters > Edit Parameters

8.       Select the other parameter value and click OK.

9.       Now look at ProductName.  It includes all the category and sub category data also.  Wirth the three concatenated fields split by a ‘-‘.

10.   That is parameter value switching in a nut shell.  Feel free to experiment with more combinations of values.

Conclusion

Whilst my demo is very simplistic, this is just showing you how powerful a concept Query Parameters can be.  This blog solely focuses on configuring a parameter to display different result sets, depending on what the user wants to see.  Thinking about the entire Power BI suite, we could hook up out parameterized datasets to Dashboards and empower the user to look at KPI’s or trends in data, depending on their role/department.  The sales and customer service departments will focus on similar dimensional data – like customer and date, but the former might want to look at the ‘SalesAmount’ measure, whilst the latter would be interested in ‘RefundedAmount’.  These fields can be contained in one query parameter!

Query Parameters have been around in Power BI for a few months now and the use of the functionality is becoming more wide spread.  Look for more blogs on this subject in the near future.

Further Reading

There are already a couple of great blogs out there and the below links take you to some other excellent use cases:

1.       Chris Webb’s Blog – https://blog.crossjoin.co.uk/2016/05/08/the-m-code-behind-power-bi-parameters/

2.       Soheil Bakhshi Blog (1 of 2) – http://biinsight.com/power-bi-desktop-query-parameters-part-1/

3.       Soheil Bakhshi Blog (2 of 2) – http://biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-parameters/