Power Query Editor

If you followed our instructions, your Power Query Editor window should look like Figure 1-4.

FIGURE 1-4 Power Query Editor after connecting to Excel and a folder

As you can see, after you instructed Power Query to automatically combine files from the folder, it created the Targets query and several helper queries, whose names are italicized—this means they won’t be loaded. We will review the data loading options later in this chapter, and we’ll continue using the same queries we created in this example.

Query dependencies

You can check the dependencies queries have by selecting Query Dependencies on the View tab. The Query Dependencies view provides a diagram like the one in Figure 1-5 that shows both data sources and queries.

FIGURE 1-5 Query Dependencies view

To view the dependencies of a specific query, select a query, and Power BI will highlight both the queries that depend on the selected query as well as queries and sources that the query depends on.

The default layout is top to bottom; you can change the layout by using the Layout drop-down list.

Importing data

When you import data, you load a copy of it into Power BI. Since Power BI is based on an in-memory columnar database engine, the imported data consumes both the RAM and disk space, because data is stored in files. During the development phase, the imported data consumes the disk space and RAM of your development machine. After you publish your report to a server, the imported data consumes the disk space and RAM of the server to which you publish your report. The implication of this is that you can’t load more data into Power BI than your hardware allows. This becomes an issue when you work with very large volumes of data.

You have an option to transform data when you import it in Power BI, limited only by the functionality of Power BI. If you only load a subset of tables from your database, and you apply filters to some of the tables, only the filtered data gets loaded into Power BI.

After data is loaded into the Power BI cache, it is kept in a compressed state, thanks to the in-memory database engine. The compression depends on many factors, including data type, values, and cardinality of the columns. In most cases, however, data will take much less space once it is loaded into Power BI compared to its original size.

One of the advantages of this data connection method is that you can use all the functionality of Power BI without restrictions, including all transformations available in Power Query Editor, as well as all DAX functions when you model your data.

Additionally, you can combine imported data from more than one source in the same data model. For example, you can combine some data from a database and some data from an Excel file in a single table.

Another advantage of this method is the speed of calculations. Because the Power BI engine stores data in-memory in compressed state, there is little to no latency when accessing the data. Additionally, the engine is optimized for calculations, resulting in the best computing speed.

Data from imported tables can be seen in the Data view in Power BI Desktop, and you can see the relationships between tables in the Model view. The Report, Data, and Model buttons are shown in Figure 1-9 on the left.

FIGURE 1-9 Power BI Desktop when importing data

DirectQuery

When you use the DirectQuery connectivity mode, you are not caching any data in Power BI. All data remains in the data source, except for metadata, which Power BI caches. Metadata includes column and table names, data types, and relationships.

For most data sources supporting DirectQuery, when connecting to a data source, you select the entities you want to connect to, such as tables or views. Each entity becomes a table in your data model. The experience is similar to the Navigator window you saw earlier in the chapter when connecting to an Excel file, shown in Figure 1-1.

If you only use DirectQuery in your data model, the Power BI file size will be negligible compared to a file with imported data.

The main advantage of this method is that you are not limited by the hardware of your development machine or the capacity of the server to which you will publish your report. All data is kept in the data source, and all the calculations are done in the source as well.

Data from DirectQuery tables cannot be seen in the Data view of Power BI Desktop; if all tables in a data model are in DirectQuery mode, the Data view button will not be visible, though you can still use the Model view. A fragment of the interface when using DirectQuery is shown in Figure 1-10.

FIGURE 1-10 Power BI Desktop interface when using DirectQuery

Live Connection

A special case of DirectQuery, called Live Connection, is available for Power BI service datasets and Analysis Services data models. It differs from DirectQuery in a few ways:

• You cannot apply any transformations to data.

• It is not possible to define physical relationships in Live Connection.

• Data modeling is limited to only creating measures.

You may consider using Live Connection rather than importing data because of the enhanced data modeling capabilities and improved security features in the data source. More specifically, unlike DirectQuery with some databases, Live Connection always considers the username of the user who is viewing a report, which means security can be set up dynamically. Additionally, SQL Server Analysis Services can be configured to refresh as frequently as needed, unlike the scheduled refresh in the Power BI service, which is limited to eight times a day without Power BI Premium.

If you connect to a dataset in DirectQuery or Live Connection mode and add other data, you’ll create a composite model, covered next.

Composite models

A composite model is a data model that combines imported data and DirectQuery or Live Connection data or that uses DirectQuery to connect to multiple data sources. For example, you could be getting the latest sales data from a database by using DirectQuery, and you could be importing an Excel spreadsheet with sales targets. You can combine both data sources in a single data model by creating a composite model.

For each table in a composite model that uses imported data or DirectQuery, the storage mode property defines how the table is stored in the data model. To view the property, you can hover over a table in the Fields pane in the Report or Data view; alternatively, you can view or change it in the Model view in the Advanced section of the Properties pane once you select a table. Note that you cannot change the storage mode of tables that you get from models by using Live Connection.

Storage mode can be set to one of the following options:

• Import

• DirectQuery

• Dual

The Dual mode means a table is both cached and retrieved in DirectQuery mode when needed, depending on the storage mode of other tables used in the same query. This mode is useful whenever you have a table that is related to some imported tables and other tables whose storage mode is DirectQuery. For example, consider the data model from Table 1-1.

In this model, the Date table is related to both the Sales and the Targets tables. When you use data from the Date and Sales tables, it is retrieved directly from the database in DirectQuery mode; when you use Date and Targets together, no query is sent to the database, which improves the performance of your reports.

Creating parameters

To create a new parameter in Power Query Editor, on the Home ribbon select Manage Parameters > New Parameter. You will then see the Manage Parameters window shown in Figure 1-11.

FIGURE 1-11 The Manage Parameters window

For each parameter, you can configure the following options:

• Name This will become the parameter name by which you can reference it.

• Description This will show up when you hover over the parameter in the Queries pane or when you open a report template that contains the parameter.

• Required This determines whether the parameter value can be empty.

• Type This is the data type of the parameter. Not all Power Query data types are available. For example, whole number cannot be selected; instead, you can choose decimal number for numerical parameters.

  • Suggested Values You can choose one of these three options:

  • Any value This is the default setting, and it allows any value within the limits of the parameter type.

  • List of values This allows you to type a list of values from which you can choose a parameter. When you use this option, you may also specify the default value, which will be selected if you open a template with this parameter.

  • Query When this option is selected, you will need to use a query of type list that will feed the list of values.

• Current Value This is the value the parameter returns when you reference it.

Using parameters

Let’s follow an example of how to use parameters in practice:

1. Create a new Power BI Desktop file.

2. Open Power Query Editor by selecting Transform data on the Home ribbon.

3. Create a new parameter as follows:

   • Name: Year

   • Type: Decimal number

   • Current Value: 2006

4. Leave all other parameter options as is and select OK.

Now that you have created the new parameter, let’s connect to the CompanySales table from the AdventureWorks OData feed:

1. Still in Power Query Editor, select New Source > OData feed.

2. Enter https://services.odata.org/AdventureWorksV3/AdventureWorks.svc/ in the URL box and select OK. If prompted for credentials, select Anonymous.

3. Select the CompanySales check box and select OK.

In the lower-left corner of Power Query Editor, note that the CompanySales query, which you’ve just created, returns 235 rows. We are now going to use our parameter to filter the query as follows:

1. Select the filter button on the OrderYear column header and select Number Filters > Equals. You will be presented with the filter options in Figure 1-12.

   

   FIGURE 1-12 Filter rows options

2. Select the top 1.2 drop-down list next to equals and select Parameter. Note that the Year parameter we created earlier is automatically selected because it is the only parameter.

3. Select OK.

The query now returns 50 rows, because it is filtered to only show rows where OrderYear is 2006—the current value of the Year parameter.

Editing parameters

You can edit parameters in Power BI Desktop or the Power BI service. Editing a parameter refers to changing its current value, as opposed to managing a parameter, which refers to changing any parameter attribute.

To continue with our example, you can edit the Year parameter by selecting it in the Queries pane and changing its current value to 2007. Note how the CompanySales query now returns 96 rows instead of 50.

You can also edit parameters after publishing your report to the Power BI service. This is done in the Parameters section of dataset settings, as shown in Figure 1-13.

FIGURE 1-13 Editing parameters in the Power BI service

After you change the parameter value, select Apply.

Creating functions

Power Query allows you to create your own functions, which can be useful when you want to apply the same logic multiple times. One way to create a custom function is by converting a query to a function. If your query already uses parameters, then those will become the parameters of the new function.

Let’s continue our AdventureWorks example and create a function that will output sales for a particular year based on the year you enter:

1. Right-click the CompanySales query and select Create Function.

2. In the Function name box, enter SalesByYear.

3. Select OK.

This creates the SalesByYear group of queries, which contains the Year parameter, the CompanySales query, and the SalesByYear function.

Now test the new function:

1. Select the SalesByYear function in the Queries pane.

2. Enter 2008 in the Year box.

3. Select Invoke.

4. This creates the Invoked Function query that returns 73 rows and contains data for year 2008 only, which you can verify by selecting the OrderYear column filter button.

Note that there is a special relationship between the SalesByYear function and CompanySales query: updating the latter also updates the former, which in turn updates all function invocations. For example, we can remove the ID column from the CompanySales query, and it will disappear from the Invoked Function query, too.

Identify data anomalies

Power Query provides different functionality for identifying data anomalies depending on what you are looking for; you might be looking for unexpected values, such as prices over $10,000 when you analyze sales of a retail store, or you may be looking for missing or error values. In this section, we cover missing or error values, and later in the chapter we look at how you can identify unexpected values.

When you look at a table in Power Query Editor, you can check the quality of each column. By default, each column will have a colored bar under its header, which shows how many valid, error, and empty values there are in each column. To show the exact percentages, select View > Data Preview > Column quality.

Figure 1-15 shows the Employee query from our earlier Wide World Importers example with the Column quality feature enabled.

FIGURE 1-15 Column quality

Note how the Parent Employee Key column has 85% valid values and 15% empty ones. If we didn’t expect any empty values in the column, this would prompt us to investigate the issue. For the column quality purposes in Power Query, empty values are null values or empty strings. There are no errors in this query, so the Error percentage is always 0%.

If we now go to the Customer query and check the Postal Code column, we’ll see that there are <1% error values. Note that Power Query won’t tell you the percentage of valid or missing values when there are errors in a column. For now, we’ll leave this error as is, and we’re going to fix the error in this column later in this chapter.

To copy the column quality metrics, right-click the area under the column header and select Copy Quality Metrics.

Examine data structures and interrogate column properties

In addition to column quality, you can look at column value distribution. To enable this feature, select View > Data Preview > Column distribution. In Figure 1-16, you can see the Stock Item query with this feature enabled.

FIGURE 1-16 Column distribution

Note that you can now see how many distinct and unique values each column has, as well as the distribution of column values in the form of a column chart under each column header. Column profiling is shown irrespective of the column type, which you can see to the left of a column’s name.

The distinct number refers to how many different values there are in a column once duplicates are excluded. The unique number shows how many values occur exactly once. The number of distinct and unique values will be the same only if all values are unique.

The column charts show the shape of the data—you can see whether the distribution of values is uniform or if some values appear more frequently than others. For example, in Figure 1-16, you can see that the Selling Package column mostly consists of one value, with four other values appearing significantly less frequently.

To copy the data behind a column chart, right-click a column chart and select Copy Value Distribution. This will provide you with a list of distinct values and the number of times they appear in the column.

Knowing the column distribution can be particularly useful to identify columns that don’t add value to your analysis. For example, in the State Province query, you can see that the following columns have only one distinct value each:

• Country

• Continent

• Region

• Subregion

Depending on your circumstances, you may choose to remove the columns to declutter your queries.

Interrogate data statistics

You can also profile columns to get some statistics and better understand your data. To enable this feature, select View > Data Preview > Column profile. After you enable this feature, select a column header to see its profile. For example, Figure 1-17 shows the profile of the Unit Price column from the Stock Item query.

FIGURE 1-17 Column profile

You can now see two new areas in the lower part of Power Query Editor:

• Column statistics This shows various statistics for the column. In addition to the total count of values and the number of error, empty, distinct, and unique values, you can see other statistics, such as minimum, maximum, average, and the number of zero, odd, and even values, among others. You will see different statistics depending on the column type. For instance, for text columns you will see the number of empty strings in addition to the number of empty values. You can copy the statistics by selecting the ellipsis next to Column statistics and selecting Copy.

• Value distribution This is a more detailed version of the column chart you can see under the column name. You can now see which column refers to which value, and you can hover over a column to apply filters based on the corresponding value. You can copy the value distribution as text by selecting the ellipsis on the Value distribution header and selecting Copy. Also when you select the ellipsis, you can group the values depending on the data type. For example, you can group columns of type Date by year, month, day, week of year, and day of week. Text values can be grouped by text length. Whole numbers can be grouped by sign or parity (odd or even), whereas decimals can be grouped by sign only.

Replace values

You can replace undesirable values with different values by using the Power Query user interface. This approach can be appropriate when there are error values in the data source that you cannot fix. For example, when connecting to Excel, the #N/A values will show up as errors. Unless you can fix the errors in the source, it may be acceptable to replace them with some other values.

Since errors are not values as such, the procedure to replace them is different compared to replacing other unexpected values.

To replace errors:

1. Right-click a column header and select Replace Errors.

2. Enter the value you want to replace errors with in the Value box.

3. Select OK.

You can only replace errors in one column at a time when using the user interface.

To replace a value in a column:

1. Right-click a column header and select Replace Values.

2. Enter the value you want to replace in the Value To Find box.

3. Enter the replacement value in the Replace With box.

4. Select OK.

You can also right-click a value in the column and select Replace Values. This way, the Value To Find box will be prepopulated with the selected value.

For text columns, you can use the advanced options shown in Figure 1-18.

FIGURE 1-18 Replace Values options

• Match entire cell contents With this option checked, Power Query won’t replace values where the Replace With value is only part of the Value To Find value.

• Replace using special characters Use this to insert special character codes, such as a carriage return or a nonbreaking space, in the Replace With or Value To Find box.

To replace values in multiple columns at once, you must select multiple columns before replacing values. To do so, hold the Ctrl key and select the columns whose values you want to replace. If you want to select a range of columns based on their order, you can select the first column, hold the Shift key, and select the last column.

The type of the replacement value must be compatible with the data type of the column—otherwise, you may get errors.

Remove rows

If there are errors in a column, you may wish to remove the rows entirely, depending on the business requirements. To do so, you can right-click a column header and select Remove Errors. Note that this will only remove rows where there are errors in the selected column. If you wish to remove all rows in a table that contain errors in any column, you can select the table icon to the left of the column headers and select Remove Errors.

Identify the root cause of error

When you see an error in a column, you can check the error message behind the error. To do so, select the error cell, and you will see the error message in the preview section at the bottom.

Figure 1-19 shows the error message behind the error we saw in the Postal Code column of the Customer query.

FIGURE 1-19 Error message

After seeing this error message, you know that the error happened because Power Query tried to convert N/A, which is text, into a number. To confirm this, you can go one step back by selecting the Promoted Headers step in the list of Applied Steps—the cell will show N/A, so you must transform the column type to Text to prevent the error from happening, as covered later in this chapter.

Although errors can happen for a variety of reasons, and not all errors occur because of type conversions, reading the error message will usually give you a good idea of what’s going on.

Keys for joins in Power Query

Power Query allows you to perform joins based on one or more columns at once, so it’s not necessary to create composite keys to merge tables in Power Query.

For joins in Power Query, column types are important. For example, if a column is of type Date, then you won’t be able to merge it with a column of type Date/Time even if the Date/Time column has no Time component.

Keys for relationships

For relationships, Power BI is more forgiving of the data type. If columns that participate in a relationship are of different types, Power BI will do its best to convert them in one type behind the scenes. Although the relationship may still work, it’s always best to have the same data type for columns that are in a relationship.

Power BI only allows you to create physical relationships between two tables on a single pair of columns. This means that in case you have a composite key in a table, you’ll need to combine the key columns in a single column before you can create a physical relationship between two tables. You can do it in Power Query or by creating a calculated column in DAX.

There are two ways of combining columns in Power Query:

• Create a new column This option keeps the original columns. To add a new merged column, select the columns you want to combine and select Add column > From Text > Merge Columns.

• Merge columns in-place This option removes the original columns. To merge existing columns, select the columns you want to merge and select Transform > Text Column > Merge Columns.

Either way, you’ll be presented with the options shown in Figure 1-20.

FIGURE 1-20 Merge columns options

You can use one of the predefined separators from the Separator drop-down list, or you can specify your own by selecting Custom from the drop-down list. You’re also given an option to enter the new column name. After you specify the options, select OK.

Using locale

Occasionally, you will need to transform data by using a certain locale. For example, if your computer uses the DMY date format and you receive data in MDY format, you’ll need to change type with the locale. To do so, right-click a column header and select Change Type > Using Locale. From there, you’ll need to select the data type and locale from drop-down lists.

Working with query steps

To start getting the Targets for 2022 query in the right shape, let’s undo some of the automatic transformations done by Power Query. By looking at the Applied Steps pane, you can see that after navigating to the right sheet in the Excel file, Power Query has promoted the first row to headers, even though the row did not contain column names. It also changed column types, considering column names as valid column values, which led to incorrect data type selection.

1. Right-click the Promoted Headers step in the Applied Steps pane.

2. Select Delete Until End.

3. Select Delete.

This removes the Promoted Headers step and all subsequent steps. The Delete Until End option is especially useful when you have more than two steps you want to remove.

As Figure 1-24 shows, you can now see some notes left by a sales planner in the first two rows. Note that columns are now not typed.

FIGURE 1-24 Targets for 2022 with automatic transformations undone

Reducing rows and columns

Since the first three rows don’t contain any meaningful data, we can remove them. In addition to filtering columns, the Power Query user interface offers the following options for row reduction:

• Keep Rows

  • Keep Top Rows Keeps the specified number of top rows. Works on the whole table only.

  • Keep Bottom Rows Keeps the specified number of bottom rows. Works on the whole table only.

  • Keep Range of Rows Skips a specified number of top rows and then keeps the chosen number of rows. Works on the whole table only.

  • Keep Duplicates Keeps rows that appear more than once. This option can work on the whole table, meaning all column values will have to match for rows to be considered duplicates, or it can work on selected columns only, so values will only need to match in specified columns for duplicates to be kept.

  • Keep Errors Keeps rows that contain errors. This option can work on the whole table or selected columns only.

• Remove Rows

  • Remove Top Rows Removes a specified number of top rows. Works on the whole table only.

  • Remove Bottom Rows Removes a specified number of bottom rows. Works on the whole table only.

  • Remove Alternate Rows Removes rows following a user-supplied pattern: it starts with a specified row, then alternates between removing the selected number of rows and keeping the chosen number of rows. Works on the whole table only.

  • Remove Duplicates Removes rows that are duplicates of other rows. Can work on the whole table or selected columns only.

  • Remove Blank Rows Removes rows that completely consist of empty strings or nulls. Works on the whole table only; if you need to remove blank values from a specific column, you can select the filter button to the right of a column’s name and select Remove Empty.

  • Remove Errors Removes rows that contain errors. Can work on the whole table or selected columns only.

Since notes can potentially change, it’s best not to remove them by filtering out specific values. In our case, Remove Top Rows is appropriate, so we can perform the following steps:

1. Select Remove Rows on the Home ribbon.

2. Select Remove Top Rows.

3. Enter 3 in the Number of rows box.

4. Select OK.

Now that we have removed the top three rows, we can use the first row for the column names by selecting Use First Row as Headers on the Home ribbon. The result should look like Figure 1-25.

FIGURE 1-25 Targets for 2022 with unnecessary rows removed and headers in place

Note how Power Query automatically detected column types, this time correctly since only the first column is a text column and all the other columns are numeric.

Next, you can see that the last row is a total row, and the last column, Year Target, is a total column. You need to remove both, because keeping them may result in incorrect aggregation later in Power BI.

There are several ways to remove the last row, and which way is best for you depends on several factors:

• If you’re sure that the last row is always going to be a total row, you can remove it in the same way that you removed the top three rows earlier.

• If, on the other hand, you’re not sure that the total row is always going to be there, it’s safer to filter out a specific value from the Buying Group column—in our example, Buying Group Total.

• Depending on business logic, you can also apply a text filter that excludes values that end with the word Total. This kind of filter relies on actual Buying Group column values not ending with Total.

Let’s say you’re sure that only the total row label ends with the word Total, so we can apply a corresponding filter to the Buying Group column as follows:

1. Select the filter button on the Buying Group column header.

2. Select Text Filters > Does Not End With.

3. Enter Total in the box next to the drop-down list that shows does not end with.

4. Select OK.

The last row is now filtered out, and you should remove the last column as well, since it contains totals for the year. To do so, select the Year Target column and press the Delete key.

With the totals gone, Power Query Editor should show “13 columns, 3 rows” in the lower-left corner. However, the current Targets for 2022 query still does not meet our goals because we don’t have a single date or month column. Instead, targets for each month appear in a separate column. We’ll address this issue next.

Pivoting columns, unpivoting columns, and transposing

The current Targets for 2022 query is sometimes called pivoted because attributes appear on both rows and columns and the same measure is scattered across multiple columns—just like in an Excel PivotTable.

Power Query provides several options when it comes to pivoting and unpivoting:

• Pivot Column This creates a new column for each value in the column you pivot. One scenario when this option can be useful is when you have different measures mixed in the same column. For example, if you have Quantity and Price as text labels in one column and numeric quantity and price values in another column, you could pivot the first column to create separate Quantity and Price columns with numeric quantity and price values, respectively.

• Unpivot Columns The selected columns will be transformed into two: Attribute, containing old column names, and Value, containing old column values. In general, unpivoting is useful when the source data has been prepared by using a PivotTable or similar method. This specific option is appropriate when the list of columns you want to unpivot is known in advance.

• Unpivot Other Columns All columns except the selected ones will be unpivoted. This option is particularly useful when you only know the list of columns that don’t need to be unpivoted. For example, if you have a file where monthly data is added as columns every month and you want to unpivot months, then you could use this option.

• Unpivot Only Selected Columns Despite the names of the previous two options, Power Query tries to recognize when you want to unpivot the selected columns or other columns, so this option allows you to be strict about which columns are unpivoted.

Furthermore, Power Query offers the Transpose feature, which allows you to transpose tables, treating rows as columns and columns as rows. This operation can be useful when you have multiple levels of headers; you can transpose your table, merge the columns that contain headers, and transpose again, so there will be only one row of headers.

For our Wide World Importers example, right-click the Buying Group column and select Unpivot Other Columns. This puts all column headers except for Buying Group in a single column called Attribute, and it puts all values in the Value column. The result should look like Figure 1-26.

FIGURE 1-26 Unpivoted months

As you can see, months now appear in a single column, which is what we want for Power BI. Now you need to transform month names into date values so you can create a relationship with the Date table later.

Adding columns

You can add a new column in Power Query by using one of the following options on the Add column ribbon:

• Column From Examples This option allows you to type some examples in a new column, and Power Query will try its best to write a transformation formula to accommodate the examples.

• Custom Column You can type your own M formula for the new column.

• Invoke Custom Function This option invokes a custom function for every row of a table.

• Conditional Column This option provides an interface where you can specify the if-then-else logic for your new column.

• Index Column This option creates a sequential column that starts and increments with the values you specify. By default, it will start with 0 and increment by 1 for each row.

• Duplicate Column This option creates a copy of a column you select.

In addition to these options, you can use data type–specific transformations to add columns, most of which are also available on the Transform ribbon. The difference between the Add Column and Transform ribbons is that the former will add a new column whereas the latter will transform a column in place.

Targets For 2022

In the Targets for 2022 query, you must transform month names into dates, and you’ll use the Custom Column option for this as follows:

1. Select Add Column > Custom Column.

2. Enter Start of Month in the New column name box.

3. Enter Date.From([Attribute] & “2022”) in the Custom column formula box. The Custom Column window should look like Figure 1-27.

   

   FIGURE 1-27 Custom Column window

4. Select OK.

5. Right-click the Attribute column and select Remove.

When entering a custom column formula, you can use the columns available in the table by double-clicking them in the Available columns list on the right; this will insert a column reference into your formula.

Once you complete these steps, the result should look like Figure 1-28.

FIGURE 1-28 Targets for 2022 with Start of Month column

Important Custom Column Types

The Custom column feature creates a column without a data type by default; you must set the data type manually before loading to ensure that the data is loaded correctly.

As you can see, the Start of Month column you’ve just created has no column type, and you may want to make it the first column instead of the last. Additionally, you have to rename the Value column by completing the following steps:

1. Select the ABC123 icon on the Start of Month column header and select Date.

2. Right-click the Start of Month column and select Move > To Beginning.

3. Double-click the Value column header.

4. Enter Target Excluding Tax and press the Enter key.

The Targets for 2022 query is now finished, and we need to transform the Targets query next.

Targets

In the Targets query, you have yearly targets, and you need to have monthly targets. Let’s say that, according to business rules, it’s all right to divide the yearly target evenly across months for years prior to 2022, which this query contains.

There are a few ways to solve this problem, one of which is adding a new column that contains months and expanding it as follows:

1. Select Add Column > Custom Column.

2. Enter Months in the New column name box.

3. Enter {1..12} in the Custom column formula box.

4. Select OK.

The result should look like Figure 1-29.

FIGURE 1-29 Months custom column added to the Targets query

In M, braces signify a list, and a double period signifies a range. When you used {1..12} as a custom column formula, you put a list of numbers from 1 to 12, inclusive, in each cell of the new column. Therefore, you see the List hyperlinks in the Months column cells. Because custom columns by default don’t have a data type set, you see ABC123 as the column type, even though in reality it is of type List.

If you select any List hyperlink, you’ll navigate to a new step that contains one such list. You can also preview the contents of a cell without navigating to the list by selecting a cell without selecting a hyperlink.

You can expand the new column by selecting the double-arrow button on the column header. You’ll then see two options:

• Expand to New Rows This option duplicates each table row by the number of list items in the row’s list.

• Extract Values This option keeps the number of rows in the table the same, and it concatenates the list items by using a separator you specify, which can be one of the predefined ones or a custom value you provide.

In our example, you use the Expand to New Rows option. The result should look like Figure 1-30.

FIGURE 1-30 Expanded Months column

Before you can create a column of type Date, you’ll have to transform the first column, which contains file names. Each file is named after a year and has a .csv extension. There are multiple ways to extract the year number from the column. The most appropriate way depends on the business requirements. In our example, you can extract the first four characters using the following steps:

1. Select the Source.Name column.

2. On the Transform ribbon, select Text Column > Extract > First Characters.

3. Enter 4 in the Count box.

4. Select OK.

5. Right-click the Source.Name column header and select Change Type > Whole Number.

Now that you have the year number, you can construct a date from it as follows:

1. On the Add Column ribbon, select Custom Column.

2. Enter Start of Month in the New column name box.

3. Enter #date([Source.Name], [Months], 1) in the Custom column formula box.

4. Select OK.

5. Right-click the Start of Month column header and select Change Type > Date.

In the new Start of Month column, we’ve got dates now, as desired. You can now clean up the query in the following way:

1. Hold the Ctrl key and select the following columns in order:

   1. Start of Month

   2. Buying Group

   3. Target Excluding Tax (Millions)

2. Right-click the header of any of the selected columns and select Remove Other Columns.

Important Selection Order

The order in which you select columns matters; after you remove other columns, the remaining columns appear in the order in which you selected them.

The Target query now has three columns. The only problem left in this query is to correct the Target Excluding Tax (Millions) column. To do so, complete the following steps:

1. Select the Target Excluding Tax (Millions) column.

2. On the Transform ribbon, select Number Column > Standard > Multiply.

3. Enter 1000000 in the Value box and select OK.

4. Similarly, select Number Column > Standard > Divide, enter 12 in the Value box, and select OK.

5. Double-click the Target Excluding Tax (Millions) column header, enter Target Excluding Tax, and press Enter.

The result should look like Figure 1-31.

FIGURE 1-31 Transformed Target query

Note how the column names are now the same as those in the Targets for 2022 query—this is on purpose. You are now ready to combine the two queries into one.

Append

When you append queries, you combine tables vertically. As a result, you get a taller table. Usually the tables will have the same columns, though this is not strictly necessary. The resulting table will have all columns from all queries, and if some columns were missing in one of the original queries, then they will be populated with null values if values are missing.

You can append queries in two ways:

• Append Queries This option appends one or more queries to the selected query and does not create any new queries. This is the default option if you select Append Queries without selecting the drop-down button.

• Append Queries as New This creates a new query that contains concatenated rows of the original queries. You can select this option after selecting the drop-down button.

In the Wide World Importers example, let’s append the Targets for 2022 and Targets queries as follows:

1. Select the Targets query from the Queries pane.

2. Select Combine > Append Queries. You should see the options shown in Figure 1-32.

   

   FIGURE 1-32 Options when appending

3. Select Targets for 2022 from the Table to append drop-down list.

4. Select OK.

As a result, you will see a new step in the Applied Steps pane called Appended Query. Unless you scroll down, you will not see any new rows because they are appended to the end of the query. In the lower-left corner, you can note the change in the number of rows, which increased to 144 from 108 in the previous step.

To meet our goal of having the End of Month column in the Targets query, let’s transform the Start of Month column:

1. Right-click the Start of Month column and select Transform > Month > End of Month.

2. Rename the Start of Month column to End of Month.

Note how you need to apply the transformation only once, even though the current query consists of two queries—Targets and Targets for 2022.

For now, you can leave the Targets for 2022 query as is, although it would be undesirable to load it since now there’s duplicated data in the Targets for 2022 and Targets queries. We will address this issue later in the “Configure data loading” section.

Merge

As discussed earlier, when you merge queries you combine them horizontally; as a result, you get a wider table. When merging, you need a set of keys—columns that have matching values in both tables—telling Power Query which rows of the first table should be combined with which rows of the second table.

Like with the Append Queries feature, you can either merge two queries without creating a new one or you can merge queries as new.

There are six kinds of joins in Power Query:

• Left Outer All from first, matching from second

• Right Outer All from second, matching from first

• Full Outer All rows from both

• Inner Only matching rows

• Left Anti Rows only in first

• Right Anti Rows only in second

You can see the visual representation of joins in Figure 1-33.

FIGURE 1-33 Joins available in Power Query

In case your data isn’t perfect, Power Query allows you to use fuzzy matching when performing merges. You can use the following options:

• Similarity threshold Can be valued from 0 to 1, where 0 will make all values match each other and 1 will allow exact matches only. The default is 0.8.

• Ignore case Lowercase and uppercase letters will be treated as the same.

• Match by combining text parts Power Query will try to combine separate words into one to find matches between keys.

• Maximum number of matches This option will limit the number of rows from the second table that are matched against the first table, and it can be useful if you expect multiple matches.

• Transformation table You can use a column with two columns—From and To—to map values during the matching process. For example, you can map NZ to New Zealand, and the two values will be considered the same for merge purposes.

For Wide World Importers, let’s merge the City and State Province queries using the following steps:

1. Select the City query in the Queries pane.

2. Select Combine > Merge Queries on the Home ribbon.

3. Select State Province in the drop-down list below the City query preview.

4. Select State Province Key in both tables. The columns should be highlighted as in Figure 1-34.

   

   FIGURE 1-34 Merge options

5. Ensure Left Outer is selected from the Join Kind drop-down list.

6. Select OK.

After you complete these steps, you’ll see a new column named State Province added to the City query. Note that the new column

• Is of type Table

• Has Table hyperlinks in each cell

• Has a double-arrow button instead of a filter button on its header

If you select any cell in the new column without selecting the hyperlink, you’ll see a preview of the cell contents, and you’ll see a table row in our case.

You can expand the new column by selecting the double-arrow button on its header. There are two ways to expand a table column:

• Expand You can select the columns from the joined table that you want to add to the current table. If there is more than one matching row in the joined table, the current table’s rows will be duplicated after expansion.

• Aggregate This option aggregates rows and won’t duplicate any rows in the current table. You can apply arithmetic and statistical functions to the columns of the joined table. For example, if it made business sense, you could take the average of State Province Key from the State Province table.

When expanding the table column, you can use the original column name as a prefix. This option can be useful if there are column names that are the same in both tables but their content is different. For instance, if you are merging the Product and Product Category tables and both have a column called Name, then you can use the original column name as a prefix to avoid confusion.

In our example, let’s expand the merged column by completing these steps:

1. In the City query, select the double-arrow button on the State Province column header.

2. Clear the Select All Columns check box.

3. Select the State Province and Sales Territory check boxes.

4. Clear the Use original column name as prefix check box.

5. Select OK.

6. Rename the State Province.1 column to State Province.

7. Remove the State Province Key column.

The result should look like Figure 1-35.

FIGURE 1-35 City query with columns from the State Province query

You’ve merged the City and State Province queries, and you’ve got five useful columns as a result. You don’t need the State Province Key column anymore, and the columns you didn’t include from the State Province query all had one distinct value each.

As is the case for the Targets for 2022 query, the information from the State Province query now appears in two queries, so there’s some data duplication in our queries. Again, we’ll address this later in the chapter.