Transforming a Data Query in the Power Query Editor in Excel 2019

Whenever you do a data query in Excel 2019 using the Get Data, From Text/CSV, From web, or From Table/Range command buttons on the Ribbon’s Data tab, you have the option of transforming that query in the Power Query Editor. When you do an external query with the Get Data, From Text/CSV, or From web options, you open the Power Query Editor after specifying the data table(s) to import into Excel by clicking Transform Data button in the Navigator dialog box. However, whenever you use the Table/Range command to designate a selected cell range in the current worksheet as a data table, Excel automatically opens the data table in a new Power Query Editor window so that you can create or transform an existing query.

Although the subject of using the Power Query Editor to perform advanced queries is a complicated topic, basic use of the Power Query Editor should prove to be no problem as the Power Query Editor’s interface and essential features are very similar to those of Excel 2019.

The following image shows the Power Query Editor window after opening it to create a new query with the Bo-Peep Client list data table entered into the current Excel worksheet (in the cell range, A1:I34 and named, Client_List). To create the new query, all you have to do is select the Excel range before clicking the From Table/Range command button on the Data table of the Ribbon.

As you see, in the Power Query Editor, the imported Excel client data table retains its worksheet row and column arrangement with the column headings complete with Auto-Filter drop-down buttons intact. Above the data table, the Power Query Editor sports a Ribbon type command structure with a File menu followed by four tabs: Home, Transform, Add Column, and View. To the right of the imported data table, a Query Settings task pane appears that not only displays the source of the data (a worksheet cell range named Client_List) but also all the steps applied in building this new query.

Once the Bo-Peep data records are loaded into the Power Query Editor, you can use its commands to query the data before returning the subset of records to a new worksheet in Excel. For this query, you are thinking of creating a subset of records where the status of the file is still active and the account is marked unpaid (in other words, all the clients who still owe the company money).

To do this, use the AutoFill buttons on the Status and Paid fields to filter the records to display just those with Active in the Status field and No in the Paid field. Then, sort the records by from the highest to lowest amount owed by sorting on the Total Due field in descending order. After that, you’re ready to select the Close & Load To option on the Close & Load command button on the Home tab to save the query and load it into a new worksheet in the current workbook. To do this, simply accept the default settings of Table and New Worksheet in the Import Data dialog box that appears after selecting the Close & Load To option prior to click OK.

The following image shows you the results. Here you see the new Excel worksheet that the Editor created (Sheet 1 in front of the Client List sheet) before it copied the filtered and sorted subset of the Bo-Peep records into a new data table in cell range A1: L10. When the Power Query Editor imported this new data table, the program also assigned it a table style, added AutoFilter buttons, and opened a Queries & Connections task pane. Now all that’s left to do is a bit of formatting, renaming the worksheet, and sending past due notices to all the delinquent clients listed in this table!