Select the range B3:F30 and click Data ➤ From Table/Range.

You will be prompted to format the range as a table. Check the My table has headers box.

The table is loaded into the Power Query Editor. This is where all the magic happens. We should name the query first. Expand the Queries pane on the left, right-click the query, click Rename, and name it Data (Figure 5-6).

Right-click the second column (named Column 1) and click Remove to delete this empty column.

Select the Name column. Click Home ➤ Split Column ➤ By Delimiter. Select Custom from the list and enter a comma followed by a space (, ) as the delimiter (Figure 5-7).

This step splits the name and the ID into separate columns.

Select the Name.1, Name.2, and Region columns and click Transform ➤ Fill ➤ Down.

Click the filter arrow in the Product column and uncheck the (null) box.

Click and drag the Name.2 column to the first position so that the ID column is before the name column.

Double-click the Name.2 header and rename it ID. Repeat for the Name.2 column and rename it Name.

Click the data type button (Figure 5-9) in the Amount column header and change the data type to Currency.

Click Home ➤ Close & Load list ➤ Close & Load To. Specify Table to an Existing worksheet and select a cell to import to (Figure 5-12).

Click Data ➤ Get Data ➤ From Other Sources ➤ Blank Query.

The Power Query Editor opens, and you are taken to the Formula Bar (click View ➤ Formula Bar if you do not see one). Type the following formula:

All the tables from the workbook are shown. We want to combine all of them into one table. Click the double arrow button in the Content column header (Figure 5-14).

Note If there were tables in the list we wanted to exclude, they could be filtered out at this stage.

Ensure all the column boxes are checked to be included in the result. Uncheck the Use original column name as prefix box.

All the tables are expanded and stacked into one table. The table name is displayed in a column called Name. We do not really need this column as we have a Date column, but we will keep it as a label for our tables and charts.

Rename the query Master.

Move the Name column to the second position before the Product column.

We will shorten the month name to only the first three letters as this would work better in the axis of a chart.

Select the Name column and click Transform ➤ Extract ➤ First Characters. Enter 3 as the number of characters.

There are also some irregularities in the format of these month names. In Figure 5-14, you can see that June has an uppercase U and September has a lowercase S.

Click Transform ➤ Format ➤ Capitalize Each Word.

Rename the Name column as Month.

Change the data type of the Date column to Date, Product and Region columns to Text, and the Amount column to Currency.

Click Home ➤ Close & Load list ➤ Close & Load To. Select Table and New worksheet.

From a blank Excel workbook, click Data ➤ Get Data ➤ From File ➤ From Workbook.

Locate and select another-excel-workbook.xlsx.

The Navigator window opens, listing all the tables, sheets, and defined names in the workbook (Figure 5-18). Check the Select multiple items box. Check the boxes for the Products table and the Angel, Bloomsbury, and Victoria sheets only.

Click Transform Data.

Each workbook item has been loaded as a separate query. The Products query is perfect. However, we need to perform some transformation steps on the other three.

We will append the Angel, Bloomsbury, and Victoria tables together to one table. But first we need to add a column with the name of the table to distinguish which store the sales came from when they are appended.

Select the Angel query from the Queries pane on the left.

Click Add Column ➤ Custom Column.

Enter Store as the new column name and enter = "Angel" in the formula box provided (Figure 5-19).

Move the Store column to the first position in the table.

We now need the month names and sales values in columns instead of rows. Select the Store and Column1 (product) columns. Right-click and click Unpivot Other Columns.

Rename Column1 to Product, Attribute to Month, and Values to Amount.

Change the data type of the Store column to Text and the Amount column to Currency.

The Angel query is now completed (Figure 5-20).

Repeat steps 5–12 for the Bloomsbury and Victoria queries.

With the query for each store transformed, we will now append them into one sales table.

Note We could have appended the tables after adding the custom column and then only performed the unpivot, column naming, and data type steps once. I decided to keep the transformations local to the specific queries to make troubleshooting problems easier in the future.

Select the Angel query and click Home ➤ Append Queries list ➤ Append Queries as New.

Select the Three or more tables option, select the Bloomsbury table, and click Add and then repeat for the Victoria table (Figure 5-21).

Name the query SalesCombined.

All three tables are stacked into one table. Perfect for a PivotTable or Excel functions such as SUMIFS to analyze.

The append query still references the three store queries. So, any changes to the Excel workbook, when refreshed, will update through to the SalesCombined query.

Click Home ➤ Close & Load list ➤ Close & Load To. Select Only Create Connection.

All five queries are loaded, but they will not appear on the worksheet as they are connection only.

The three store queries were staging queries which we then appended. The Products query we will use in a Merge query shortly. The SalesCombined query is the only one we need to load to a table on the worksheet.

Right-click the SalesCombined query in the Queries & Connections pane and click Load To.

Select table and load it to the existing worksheet.

All five queries are loaded, but only the SalesCombined query is loaded as a table to the worksheet (Figure 5-22).

Save the workbook as store-sales.xlsx.

Click Data ➤ Get Data ➤ Launch Power Query Editor.

Select the SalesCombined query from the Queries pane.

Click Home ➤ Merge Queries. This will merge to the currently selected query.

Select Products from the list of tables to merge SalesCombined with.

Click the Product column in the SalesCombined table and then the Product column in the Products table (Figure 5-23).

These are the two key fields to uniquely identify and link the products correctly. The message at the bottom of the Merge window confirms a complete match of 324 out of 324 rows.

Note You can select multiple columns to identify records correctly from two tables. For example, it could be product name and size.

The Join Kind is set to Left Outer. This is what we need for this example. There are six different join kinds.

The Products table is added as a column to the SalesCombined query. Click the double arrow button in the column header. Uncheck the Product column and the Use original column name as prefix box (Figure 5-24).

Move the column to the third position after the Product column.

Click Home ➤ Close & Load.

Click Data ➤ Get Data ➤ Combine Queries ➤ Merge.

Select Event1 from the first list of tables and Event2 from the second list.

Click the Name column in Event1 and then again in Event2. This is the identifying value in both columns.

Select Inner from the Join Kind list (Figure 5-27).

This will produce the results of those who attended both events. The preview text at the bottom of the window informs us that there will be eight names returned.

Name the query AttendedBoth.

Close and load the query as a table to the worksheet.

Start another merge query with the two tables and specify the Right Anti from the Join Kind list.

Name the query Event2Only.

Figure 5-29 shows the results of the right anti join. This join returns rows that exist in the right table only. Therefore, the left table returns no rows, and we need to expand the right table to get the results.

Click the double arrow button in the Event2 header, keep both columns checked, and uncheck the Use original column name as prefix box.

Remove the first two columns and rename the Name.1 column to Name and the Location.1 column to Location.

Close and load the query as a table to the worksheet.

Start a new workbook and click Data ➤ Get Data ➤ From File ➤ From Folder.

Click Browse and locate the sales-data folder (Figure 5-32).

A window appears listing all the files found in that folder. Click Transform Data.

Click the filter arrow for the Extension column and clear the check boxes for the .pdf and .txt extensions.

Click the Combine Files button (double arrow in the Content header).

In the Combine Files window, you can specify a sample file from the list. This is the file that Power Query will follow as a framework for the other files when appending them. These files all have the same column headers, so the first file (April.csv) is fine for this example.

With the Source.Name column selected, click Home ➤ Replace Values. Enter .csv as the Value To Find and leave the Replace With box empty (Figure 5-35).

Rename the Source.Name column as Month.

Select the Date column and click Home ➤ Sort Ascending.

Change the data type of the Amount column to Currency.

Rename the query as AllMonthsSales.

Click Home ➤ Close & Load list ➤ Close & Load To.

Select PivotTable Report and place it on an existing worksheet.

Click Data ➤ Get Data ➤ From Other Sources ➤ From Web.

Enter the following URL into the box provided (Figure 5-39):

https://www.imdb.com/chart/top/?ref_=nv_mv_250

In this example, only the document itself and one table are returned. Select Table 0 and click Transform Data.

In the Power Query Editor, rename the query TopFilms.

Figure 5-41 shows the data loaded into Power Query. There are a few transformations to walk through to get the rank, title, year, decade, and rating columns that we want.

Select the Rank & Title and IMDb Rating columns and click Home ➤ Remove Columns list ➤ Remove Other Columns.

Let us split the rank and title into separate columns.

With the Rank & Title column selected, click Home ➤ Split Column ➤ By Delimiter. Use a custom delimiter and enter a full stop (period) followed by a space (. ) (Figure 5-42). Select Left-most delimiter as some of the film titles include full stops in their name.

Select the Rank & Title.2 column and click Home ➤ Split Column ➤ By Delimiter. Use a custom delimiter and enter a space followed by an opening parenthesis “ (”.

To remove the closing parenthesis from the year, select the Rank & Title.2.2 column and click Home ➤ Replace Values. Enter a closing parenthesis “)” for the Value To Find and leave the Replace With box empty.

Rename the first column to Rank, the second column to Title, and the third column to Year.

Figure 5-43 shows the progress so far with the TopFilms query.

Select the Year column and click Add Column ➤ Column From Examples list ➤ From Selection.

We will now enter example values for the rows, and Power Query will try and work out what we need.

Enter 1990-1999 for the first film into the column provided on the right and press Enter. This film (your list may be different as it changes over time) was released in 1994 so 1990–1999 is the decade.

Column From Examples’ first attempt to understand what we need is incorrect (Figure 5-44).

Enter 1970-1979 for the second film and press Enter. Column From Examples has now successfully understood what we want (Figure 5-45). Click OK.

Click and drag the Range column to between the Year and IMDb Rating columns.

Rename the Range column to Decade.

Change the data type of the Year column to Whole Number.

All the transformation steps of the query are complete. However, we could clean up the Applied Steps box.

Earlier in this chapter, we covered deleting the Changed Type steps. We can also rename steps. For example, we have two split column steps. Renaming these will provide more transparency to the query steps.

Right-click the Split Column by Delimiter step and click Rename. Name this step Split Rank and Title.

Right-click the Split Column by Delimiter1 step and click Rename. Name this step Split Title and Year.

You will be thankful for the time taken to rename steps when you revisit the query in the future. It will be much easier to understand. Figure 5-46 shows the Applied Steps after renaming the two split column steps.

Close and load the query as a table to a worksheet.

Click Data ➤ Get Data ➤ From File ➤ From PDF.

Locate the new-members.pdf document and click Import.

In the Navigator window, Power Query lists all of the tables and pages that it has identified in the document (Figure 5-48). The table with new member data that continues over three pages has been identified as three different tables by Power Query (tables 2, 3, and 4).

The preview area in the window is great to help discern the different tables.

Check the Select multiple items box and check the boxes for Table002, Table003, and Table004.

Click Transform Data.

The three tables are imported as separate queries (Figure 5-49). These queries make up the three parts of the new members data table, so we will append them shortly.

Firstly, let us tidy up the table headers. In the first query, the first row of data are the headers.

Ensure that the Table002 (Page 1) query is selected and click Home ➤ Use first row as headers.

Select Table003 (Page 2) and rename the headers Name, Type, and Source to match the headers used in Table002. Repeat for Table003 (Page 3).

Select Table002 (Page 1) and click Home ➤ Append Queries list ➤ Append Queries as New.

In the Append window, select Three or more tables. Add the two other tables to the Tables to append box (Figure 5-50).

Name the query NewMembers.

Close and load it as a table to the worksheet.

Right-click the Master query in the Queries & Connections pane (click Data ➤ Queries & Connections if it is hidden) and click Load To.

Select Only Create Connection and click OK.

Changing the query back to a connection only query will remove the table on the worksheet (Figure 5-51). Click OK to confirm this.

Click Data ➤ Get Data ➤ Launch Power Query Editor.

Right-click the Master query and click Reference. Repeat this step so that we have two new queries.

Rename one of the queries MonthlySalesByRegion and the other query ProductSales.

Select the MonthlySalesByRegion query and click Home ➤ Group By.

Select Advanced so that we can group by more than one column. Select Region from the list, click Add grouping, and then select Month from the second list (Figure 5-55).

Enter Total for the new column name and specify the operation as Sum and the column to sum as Amount.

Select the Month column and click Transform ➤ Pivot Column.

Select the Jan column, press Shift, and select the Dec column. Click Home ➤ Data Type list ➤ Currency.

Let us now create the second report.

Select the ProductSales query and click Home ➤ Group By.

Select Advanced and select the Product column to group by.

This report has two aggregations. For the first one, enter Total Sales for the column name and to Sum the Amount column. For the second aggregation, use No of Sales for the column name and Count Rows for the operation (Figure 5-58).

Change the data type of the Total Sales column to Currency.

Select the Total Sales column and click Home ➤ Sort Descending.

Close and load the queries.

Click Data ➤ Get Data ➤ Launch Power Query Editor.

Select the Master query.

Select the Source step in the Applied Steps box and click Home ➤ Refresh Preview.

The ProductSales and MonthlySalesByRegion tables are being picked up when combining all the workbook tables (Figure 5-59).

Delete the current Filtered Rows step. We will insert a new one.

Select the Source step and filter out the MonthlySalesByRegion and ProductSales tables from the Name column.

Refresh the queries, and they work perfectly.