© Adam Aspin 2020
A. AspinData Mashup with Microsoft Excel Using Power Query and Mhttps://doi.org/10.1007/978-1-4842-6018-0_11

11. Parameterizing Queries

Adam Aspin1 
(1)
Stafford, UK
 

Not all data flows are rigid and predictable. There will, inevitably, be cases where you also want to shape the data ingestion process depending on aspects of the source data. This can mean parameterizing your queries to allow user interaction or adjusting the data flow dynamically. Adding parameters to queries enables you to define and apply specific criteria to certain aspects of query processing.

All the files used in examples in this chapter are available for download from the Apress website as described in Appendix A.

As parameters are entirely managed inside the Query Editor, you will need to have the Query Editor open to carry out any of the examples in this chapter.

Parameterizing Queries

At their heart, parameters are a technique that enables you to

  • Select a value that can be used in one or more queries to alter the query flow by injecting a required element into a query step. This could be a file source or a filter value, for instance.

  • Restrict the selection of potential parameter values to a predefined list of options for a user to choose from.

There are currently three basic ways a user can select a parameter before running a query that will apply the chosen parameter. A parameter can be

  • A single value that you or the user enters

  • A selection of a value from a list of possible values that you enter manually

  • A selection of a value from a list of possible values that you create using existing queries

It follows that using parameters is a two-step process:

  • Create a parameter.

  • Apply it to a query.

A parameter is really nothing more than a specialized type of query. As it is a query, you can

  • Load it into the data model (although this is rarely required)

  • Reference it from another query

  • Build and modify it just like any other query

This chapter will explain how you can create and apply parameters. This will include showing you some of the ways that you can apply parameters in the Query Editor to filter or transform the data.

Creating a Simple Parameter

At its simplest, a parameter is a value that you store so that you can use it later to assist you in your data transformation. Here is how you can store a parameter containing a “True” value ready for use in filtering subsequent datasets:
  1. 1.

    Open the Excel file C:DataMashupWithExcelSamplesCarSalesDataForQueries.xlsx (unless it is already open, of course).

     
  2. 2.

    Display the Queries & Connections pane (unless it is already visible). Double-click any of the queries in the Queries & Connections pane to open the Query Editor.

     
  3. 3.

    In the Query Editor Home ribbon, click the small triangle at the bottom of the Manage Parameters button, then select New Parameter from the available menu options. The Parameters dialog will appear.

     
  4. 4.

    Enter DealerParameter as the parameter name and Filter dealer types as the description.

     
  5. 5.

    Ensure that the Required check box is selected.

     
  6. 6.

    Choose True/False from the popup list of types.

     
  7. 7.

    Enter True as the Current Value from the popup list. The dialog will look like the one in Figure 11-1.

     
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig1_HTML.jpg
Figure 11-1

The Parameters dialog

  1. 8.

    Click OK. The new parameter will appear in the Queries list on the left. You can see this in Figure 11-2.

     
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig2_HTML.jpg
Figure 11-2

A parameter in the Queries list

For the moment, all you have done is create a parameter and store a value in it. You will see how to use this parameter in a few pages’ time. As you can see, a parameter is stored as a type of query in the Power Query Editor Queries pane, and the default value is displayed after the query name in parentheses.

Creating a Set of Parameter Values

While a single parameter can always be useful, in reality you are likely to need lists of potential parameters. This will allow you or other users to choose a parameter value from a predefined list in certain circumstances. Here is an example of creating a parameter containing a subset of the available country names used in the sample data:
  1. 1.

    Using the Excel file that you created in the previous section (the one based on the Excel file C:DataMashupWithExcelSamplesCarSalesOverview.xlsx), open the Query Editor—unless it is already open.

     
  2. 2.

    In the Query Editor Home ribbon, click the small triangle at the bottom of the Manage Parameters button, then select New Parameter from the available menu options. The Parameters dialog will be displayed.

     
  3. 3.

    Enter CountriesParameter as the parameter name.

     
  4. 4.

    Ensure that the Required check box is selected.

     
  5. 5.

    Choose Text from the popup list of types.

     
  6. 6.

    In the Suggested Values popup list, select List of values.

     
  7. 7.
    Enter the following three values in the grid that has now appeared:
    1. a.

      France

       
    2. b.

      Spain

       
    3. c.

      Germany

       
     
  8. 8.

    Select France as the Default Value from the popup list.

     
  9. 9.

    Select Spain as the Current Value from the popup list. The dialog will look like the one shown in Figure 11-3.

     
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig3_HTML.jpg
Figure 11-3

The Parameters dialog for a set of options

  1. 10.

    Click OK. The new parameter will appear in the Queries list on the left.

     

Once again, all you have done is create the parameter. You will see how it can be applied in a couple of pages’ time.

Note

As you can see, any current value that you have chosen will appear in the Queries pane in parentheses to the right of the parameter name. This is to help you remember which value is current—and is possibly being used to shape a data flow process.

Creating a Query-Based Parameter

Typing lists of values that you can use to choose a parameter is not only laborious, it is also potentially error-prone. So you can use the data from existing queries to create the series of available elements that you use in a parameter instead of manually entering lists of values. Moreover, any lists that you enter manually are completely static. So you will have to remember to update them if the user requirements change. Parameter lists are, by contrast, dynamic. That is, they update automatically if the source data changes.

As an example of this, suppose that you want a parameter that contains all the available makes of car that the company sells:
  1. 1.

    Using the Excel file C:DataMashupWithExcelSamplesCarSalesDataForQueries.xlsx, open the Query Editor by double-clicking on any of the queries in the Queries & Connections pane.

     
  2. 2.

    Select the query Stock in the Queries list.

     
  3. 3.

    Right-click the title of the column named Model, and select Add as New Query. A new query named Model will appear in the Queries list. This query contains the contents of the column you selected.

     
  4. 4.

    In the newly created query, click Remove Duplicates in the Transform ribbon. The List column will only display unique values.

     
  5. 5.

    Rename the newly created query ModelList.

     
  6. 6.

    In the Query Editor Home ribbon, click the small triangle at the bottom of the Manage Parameters button, then select New Parameter from the available menu options. The Parameters dialog will be displayed.

     
  7. 7.

    Enter ModelsParameter as the parameter name.

     
  8. 8.

    Ensure that the Required check box is selected.

     
  9. 9.

    Choose Text from the popup list of types.

     
  10. 10.

    In the Suggested Values popup list, select Query.

     
  11. 11.

    Select ModelList as the query containing a list of values to use from the popup list of available lists.

     
  12. 12.

    Enter DB7 as the Current Value. The Parameters dialog should look like the one in Figure 11-4.

     
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig4_HTML.jpg
Figure 11-4

The Parameters dialog for a list of options

  1. 13.

    Click OK. The new parameter will appear in the Queries list on the left.

     
You should now be able to see all three parameters that you have created in the Queries pane, as shown in Figure 11-5.
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig5_HTML.jpg
Figure 11-5

Parameters in the Queries list

Note

Parameters and lists will appear in Excel in the Queries & Connections pane. Lists will also normally load to a new Excel worksheet. Unless you define these as “Connection only” you will need to remember—and to warn users—that these are not “classic” queries.

Once your parameters have been created, you can quit the Query Editor by clicking the Close & Load button. Your parameters can now be used to shape a data flow process.

Tip

It is also possible to create parameters “on the fly” (i.e., directly from inside a dialog that uses a parameter) when you want to use them. However, I find it better practice—and more practical—to prepare parameters beforehand. This forces you to think through the reasons for the parameter as well as the potential range of its use. It can also avoid your making errors when trying to do two different things at once.

Modifying a Parameter

Fortunately, parameters are not set in stone once they are created. You can easily modify

  • The structure of a parameter

  • The selected parameter element (the current value)

Modifying the Structure of a Parameter

Should you need to modify the way that a parameter is constructed, one way is to do the following:
  1. 1.

    In the Query Editor Home ribbon, click Manage Parameters. The Parameters dialog will be displayed as seen in the previous sections.

     
  2. 2.

    In the left pane of the dialog, click the parameter that you want to modify. The parameter definition will appear on the right.

     
  3. 3.

    Carry out any required modifications.

     
  4. 4.

    Click OK.

     
Alternatively, you can do this:
  1. 1.

    Click the parameter in the Queries pane on the left of the Query Editor.

     
  2. 2.

    Click the Manage Parameters button. The Parameters dialog will appear.

     
  3. 3.

    Carry out any required modifications.

     
  4. 4.

    Click OK.

     

You can also, if you prefer, right-click a parameter in the Queries pane and select Manage from the popup menu to display the Parameters dialog.

Applying a Parameter When Filtering Records

Now that you have seen how parameters are created, it is time to see them in action. As a first example of applying a parameter, you will see how to use a parameter to filter a query:
  1. 1.

    Open the file C:DataMashupWithExcelSamplesParametersExample.xlsx. This file contains the three parameters created previously.

     
  2. 2.

    Double-click the Countries query in the Queries & Connections pane to open the Power Query Editor and select the Countries dataset. You may need to display the Queries & Connections pane by clicking Data ➤ Queries & Connections.

     
  3. 3.

    Click the popup menu for the CountryName column on the right of the field name.

     
  4. 4.

    Select Text Filters ➤ Equals. The Filter Rows dialog will appear.

     
  5. 5.

    Leave Equals as the first choice.

     
  6. 6.

    Click the central popup (between equals and enter or select a value) and select Parameter from the list. You can see this in Figure 11-6.

     
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig6_HTML.jpg
Figure 11-6

Selecting a parameter for a filter

  1. 7.

    Select CountriesParameter for the third popup. The dialog will look like the one shown in Figure 11-7.

     
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig7_HTML.jpg
Figure 11-7

Applying a parameter for a filter

  1. 8.

    Click OK. The current parameter value (the country that you selected) will be applied, and the dataset will be filtered using the current parameter value.

     
Note

To remove a parameter from a filter, simply delete the relevant step in the Applied Steps list.

Modifying the Current Value of a Parameter

You could be forgiven for wondering if it is worth setting up a parameter merely to filter a dataset. However, this whole approach becomes more interesting if you modify the current parameter value and then refresh the data to apply the new parameter. Here is an example of this:
  1. 1.

    In the Query Editor Home ribbon, click the small triangle to display the menu for the Manage Parameters button.

     
  2. 2.

    Select Edit Parameters. The Enter Parameters dialog will appear.

     
  3. 3.

    From the popup list of values for the CountriesParameter, select one of the available values (and not the value that was previously selected). The dialog should look like the one shown in Figure 11-8.

     
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig8_HTML.jpg
Figure 11-8

Modifying the current value of a parameter

  1. 4.

    Click OK.

     
  2. 5.

    In the Query Editor Home ribbon, click Refresh Preview. The data will be refreshed and the new parameter values applied to the filters that use these parameters.

     

This approach becomes particularly useful if you have many combinations of filter values to test. In essence, you can apply a series of filters to several columns (or create complex filters) using several parameters and then test the results of different combinations of parameters on a dataset using the Enter Parameters dialog. This technique avoids having to alter multiple filters manually—and repeatedly. As an added bonus, you can restrict the user (or yourself) to specific lists of parameter choices by defining the lists of available parameter options. You can see this for the popup lists that appear when you select the CountriesParameter popup or the ModelsParameter popup.

Applying a Parameter to a Data Source

In some corporate environments, there are many database servers that are available, and possibly even more databases. You may find it difficult to remember all of these—and so may the users that you are preparing reports for using Power Query in Excel.

One solution that can make a corporate environment easier to navigate is to prepare parameters that contain the lists of available servers and databases. These parameters can then be used—and updated—to guide users in their choice of SQL Server, Oracle, or other database data sources.

To see this in action, you will first have to prepare two parameters:

  • A list of servers

  • A list of databases

You can then see how to use these parameters to connect to data sources. Of course, you will have to replace the example server and database names that I use here in step 6 with names from your own environment.

Note

You can only apply parameters if the check box Always allow is checked in the Query Editor View menu.

  1. 1.

    Open a new Excel file.

     
  2. 2.

    Click Get Data ➤ From Database ➤ From SQL Server Database.

     
  3. 3.

    Enter your server and database, and connect to the database.

     
  4. 4.

    Select a data table—or tables.

     
  5. 5.

    Click Transform Data to open the Query Editor.

     
  6. 6.
    Create a new parameter using the following elements:
    1. a.

      Name: Servers

       
    2. b.

      Type: Text

       
    3. c.

      Suggested Values: List of Values

       
    4. d.

      Values in the list: ADAM03 and ADAM03SQL2017 (or your database server)

       
    5. e.

      Default Value: ADAM03SQL2017 (or your database server)

       
    6. f.

      Current Value: ADAM03SQL2017 (or your database server)

       
     
  7. 7.

    Select the query that you created to connect to the source data.

     
  8. 8.

    In the Applied Steps list, click the cog icon to the right of the first step, named Source.

     
  9. 9.

    On the Server line, click the popup for the server and choose Parameter. Select the Server parameter. The SQL Server database dialog will look like the one shown in Figure 11-9.

     
../images/497001_1_En_11_Chapter/497001_1_En_11_Fig9_HTML.jpg
Figure 11-9

Using a parameter to select the server and database

  1. 10.

    Choose the data connectivity mode and any advanced options that you want to set.

     
  2. 11.

    Click OK. The server connection process and dialogs will appear, and you will then see the Navigator dialog displaying the tables and views for the current server and database values in the two parameters.

     

You could have defined and applied a database parameter as well. However, as the approach is virtually identical, I will leave you to attempt this unaided.

Other Uses for Parameters

These examples only cover a few of the cases where parameters can be applied in Power Query. Indeed, the range of circumstances where a parameter can be applied is increasing with each release of the product. So look out for all the dialogs that give you the option of using a parameter.

Using Parameters in the Data Source Step

One use of parameters that can quickly prove to be a real time-saver is to use parameters in the Source step of a query. Put simply, you can use a parameter instead of a fixed element name such as

  • An Excel file name

  • A file path

  • A database or data warehouse server

  • A database

It can be particularly useful to use parameters to define connections (i.e., server and database references), as this

  • Provides a central reference point for connection information

  • Avoids you having to type connection details for similar queries from the same server—and minimizes the risk of introducing typos

  • Makes it easier to switch between development, test, and production servers

To illustrate this, and assuming that you have created the parameter “Servers” from the previous section, try the following:
  1. 1.

    Create a connection to a SQL Server database (as described in Chapter 3).

     
  2. 2.

    Click the Transform Data button in the Home ribbon.

     
  3. 3.

    In the Query Editor, select the query created by the database connection.

     
  4. 4.

    Click the first of the Applied Steps on the right. This step should be named “Source.”

     
  5. 5.

    In the formula bar, replace the code that looks something like this:

     
= Sql.Database("ADAM03SQL2017", "PrestigeCars")
  1. 6.

    With this

     
= Sql.Database(Servers, "PrestigeCars")
  1. 7.

    Confirm your modifications by clicking the check box in the formula bar—or by pressing Enter. You will almost certainly have to confirm your database credentials.

     
Note

You need to be aware that hard-coded server and database names must be contained in double quotes, whereas parameters must not be enclosed in quotes. Also note that the M language used in the formula bar is case-sensitive. So you need to enter parameter names exactly as they were created. You will learn more about the M language in Chapter 12.

Applying a Parameter to a SQL Query

If you are using a relational database, such as Oracle or SQL Server, as a data source (and if you are reasonably up to speed with the flavor of SQL that the source database uses), you can query a database using SQL and then apply Power Query parameters to the source query.

Let’s see this in action:
  1. 1.

    Open a new Excel file, open the Query Editor, and create the parameter named CountriesParameter that you saw a few pages ago.

     
  2. 2.

    Click Close & Load to close the Query Editor.

     
  3. 3.

    In the Excel Report screen, click SQL Server Database.

     
  4. 4.

    Enter the server and database that you are using. (If you are using the examples from the Apress website, then it will be your server and the database CarSalesData.)

     
  5. 5.
    Click Advanced options and enter the following SQL statement:
    SELECT  *
    FROM    CarSalesData.Data.CarSalesData
    WHERE   CountryName = 'Germany'
     
  6. 6.

    Click OK and confirm any dialogs about data access and permissions.

     
  7. 7.

    Click Edit to connect to the data and open the Query Editor.

     
  8. 8.
    There should only be one Applied Step for the data connection. Expand the formula bar and tweak the formula so that it looks like this:
    = Sql.Database("ADAM03SQLSERVER2016", "CarSalesData", [Query="SELECT  * FROM CarSalesData.Data.CarSalesData WHERE   CountryName = '"& CountriesParameter &"'"])
     
  9. 9.

    Click the tick icon in the formula bar to confirm your changes. The data will change to display the data for France (the current parameter value) rather than Germany (the initial value in the SQL).

     

You can now alter the parameter value and refresh the data. This will place the current parameter inside the SQL WHERE clause and only get the data for the current parameter.

In case this seems a little succinct, let’s look at the code used by Power Query before you made the change in step 8. The M language read:
= Sql.Database("ADAM03SQLSERVER2016", "CarSAlesDAta", [Query="SELECT  * FROM    CarSalesData.Data.CarSalesData WHERE   CountryName = 'France'"])

The change was to replace

France

with

“& CountriesParameter &”

What you did was to replace the hard-coded criterion “France” with the parameter reference. Indeed, much as you would in Excel, you added double quotes and ampersands to the formula to allow the code to include an extraneous text element.

This was an extremely simple example, but I hope that it opens the door to some fairly advanced use of parameters in database connections.

Note

Updating data once a parameter has changed might require accepting data changes and new permissions.

Query Icons

As you could see previously in Figure 11-9, there are three query icons. These are explained in Table 11-1.
Table 11-1

Query Icons

Icon

Query Type

Description

../images/497001_1_En_11_Chapter/497001_1_En_11_Figa_HTML.jpg

Query

The icon for a standard query

../images/497001_1_En_11_Chapter/497001_1_En_11_Figb_HTML.jpg

List

The icon for a list

../images/497001_1_En_11_Chapter/497001_1_En_11_Figc_HTML.jpg

Parameter

The icon for a parameter

Conclusion

In this chapter, you saw how to add parameters to queries and how to interact with queries in a controlled fashion. This lets you make queries—and so the entire ETL process—more flexible and interactive.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.224.171.32