Choose a workbook, report, or query which you regularly modify. Good candidates for this intro level exercise require recurring scrubbing or processing such as:
- Splitting, combining, or removing rows or columns
- Extracting data from columns
- Combining multiple files with identical layouts
- Changing header names
- Adding a column that uses simple conditional logic (VLOOKUPs in the same worksheet or IF statements)
- Adding a column that contains a simply mathematical operation
- Performing a VLOOKUP from one file to another
The goal is to select a file that allows you to gain confidence using your new Powers by flexing your muscles; the goal is NOT to take steroids and blow your muscles up unnaturally, disfiguring the results.
Get Your Data
In Excel, start a New workbook.
Office 365, Excel 2016, and Excel 2019 users
In the ribbon, go to the Data tab, Get & Transform Data section.
Excel 2010 and Excel 2013 users
In the Power Query tab, go to the Get External Data section.
All users
Explore the importing options available.
Some of the most popular options for finance are:
Choose the data source type corresponding to the source for your data.
Browse to your file and select “Import”.
On the pop-up Navigator screen, your choices will vary depending on the source file type. The example below is the pop-up screen after choosing an Excel workbook as the source.
- Selecting the folder icon "Query CC transactions" imports all the subitems.
- Selecting Table1 or Working_Budget_Query brings in those Excel Tables.
- Selecting All_Trans brings in that worksheet in the Excel workbook.
- Selecting any item will provide a preview of the data to be imported.
Once a selection has been made, select "Transform Data" for newer Excel versions, or "Edit" for Excel 2010-2013.
Explore the Power Query Editor
Below the ribbon, the left side contains the Queries menu. If your import included multiple items, you can locate the other queries by expanding the Queries menu.
The center contains a preview of the columns in your query, but a maximum of 999 rows. Any changes made in PQ are stored in your new workbook and do not affect your original source file.
The right side has Query Settings.
Under Name, rename the query using the following best practice standards.
No spaces. If it's a data query (like GL activity), add Data in the name. If it's a lookup query (like a chart of accounts), add LU in the name. In our prior exercises, the queries were named "Bank_acct_LU" and "GL_bal_Data".
Notice in the above Applied Steps area, PQ has already recorded some steps. These will vary depending on your data source. Selecting the gear next to Source or Navigation will take you back to the Navigation prompt. Selecting the X to the left of any step will remove that step.
💥BEST PRACTICE TIP
There is no undo in Power Query. Be careful when removing steps. If you accidentally delete a step, you can close the query and discard any changes; however, you will loose all changes made during the current session.
Explore the PQ Menus
Let's discuss the 3 tabs where most
processing takes place: Home, Transform, and Add Column.
- Home steps can be applied to the entire query.
- Transform steps apply to selected columns.
- Add Column steps result in adding new columns to your query.
Interestingly, the same step may be listed in different tabs, but execute differently.
For example, Extract on the Transform tab extracts the requested information from the existing column, leaving it behind, and removing what is left. Whereas, Extract on the Add Column tab keeps the existing column as is and creates a new column with the extracted data.
The Home tab
Try adding some of the Home tab processes as steps to your query. If the step doesn't perform as expected, select the X next the Applied steps to remove it.
Close & Load is the final step of the query process. This loads the query in to the Excel workbook. Outputs are either an Excel Table, a Pivot Table, a Pivot Chart, or Connection only.
Manage Columns and
Reduce Rows allows for quick cleanup of your file, removing rows or columns. In addition, you can remove only blank rows, only duplicates, or rows that have errors. An error gets flagged in PQ when the data in a column does not match the data type for that column.
To
sort, select a column and the appropriate ascending or descending button. To sort by multiple columns, select the second column to be sorted and repeat. It will be applied as one step with multiple sort criteria.
Split Column is a
powerful PQ function. Explore the numerous options available including split By Number of Characters far right or far left, which replaces the LEFT and RIGHT functions in native Excel. Splitting By Delimiter, Custom, can include a full string of text, compared to native Excel's single character split.
Group By aggregates data for a designated column. Similar to building a Pivot Table, but inside a query. Columns not specified in the Group By dialog box are removed from the query.
Data Type specifies the type of data that is in a selected column.
Any data in a column that does not meet the data type characteristics will result in an error. For example, data type Decimal Number will result in an error if the value in a row is "text".
Use First Row as Headers and
Use Headers as First Row allows the promotion or demotion of the first row or headers.
Replace Values leaves an audit trail to allow others to follow the transformation from the source file to the revised analysis. Something that native Excel's Find & Replace doesn't provide.
Merge Queries,
Append Queries, and
Combine Files allow the blending of multiple data sets replacing VLOOKUPs or their counterpart INDEX, MATCH. There is a lot left to explore on this subject; when getting started, keep it fairly simple, like our examples in the Intro Series exercise.
The Transform tab
The Transform tab performs actions on selected columns. The menu options available differ based on the data type for the selected column(s). You'll notice some duplication of commands from the Home menu. This allows you to perform all column transformations without flipping between two menus.
Some of the key processes to consider trying on the Transform menu are described below.
Transpose allows you to flip rows and columns, similar to Paste Special, Transpose in native Excel.
Reverse Rows flips all rows so that the bottom row becomes the top row and vice versa.
Replace Values has an expanded option to
Replace Errors for cleaner reporting.
Fill Up and Fill Down fill in blank spaces above or below each cell.
Initial data in native Excel
Data after loading to PQ
Transformed using Fill down
Move includes quick shortcuts to move a column to the beginning or end of your query.
Format is used to tidy up text records using Trim or Clean. It also provides options to change the case.
Merge Columns combines selected columns, separating them by a designated character or string.
Extract is a powerful data cleansing tool with multiple options for plucking out the data you need from a cell, replacing LEFT, RIGHT, MID, and FIND functions in native Excel.
Standard is a quick way to perform simple mathematical operations on a numerical column. Try "Multiply, "-1" to flip the sign of all numbers in the applicable column.
Rounding includes
Round Up,
Round Down, and
Round.
Date has enough functions available for its own upcoming blog post. Most options are self explanatory and expand your ability to transform a date column quickly and easily.
The Transform menu should give you plenty of options to start working your own magic in PQ.
💥BEST PRACTICE TIP
Learning new tricks is an investment. Consider building out a few steps in PQ, loading the query and finishing your transformation in native Excel. This progressive approach blends both worlds until PQ becomes second nature (which it quickly will with a little practice).💥
The Add Column tab
The Add Column processes add a column to the query. Some of the functions are the same as under the Transform tab, except Transform performs the action on the selected column and Add Column keeps the selected column and adds an additional column with the action performed: the duplicate ones to explore are
Format, Merge Columns, Extract, Standard, Rounding and
Date.
Column From Examples was developed for people learning PQ. Input samples of values for your new column and PQ will try and create the column.
Custom Column can be used to create various types of columns. Try creating a text column by entering ="Text". Or, create a mathematical column by inserting two columns that are multiplied together.
Conditional Column creates a column that if a value meets specified criteria then the output should be as defined. If not, additional rules can be applied or a null value will be the output.
Duplicate Column duplicates.
Hopefully this has been a successful data adventure for you. It's time to wrap up with a Close & Load and start
analyzing the results.
In this lesson, you learned how to:
