Excel 2024: Clean Data with Power Query

Get Power Query

You may already have Power Query. It is in the Get & Transform group on the Data tab.

But if you are in Excel 2010 or Excel 2013, go to the Internet and search for Download Power Query. Your Power Query commands will appear on a dedicated Power Query tab in the Ribbon.

Clean Data the First Time in Power Query

To give you an example of some of the awesomeness of Power Query, say that you get the file shown below every day. Column A is not filled in. Quarters are going across instead of down the page.

To start, save that workbook to your hard drive. Put it in a predictable place with a name that you will use for that file every day.

---

---

In Excel, select Get Data, From File, From Workbook.

Browse to the workbook. In the Preview pane, click on Sheet1. Instead of clicking Load, click Transform Data. You now see the workbook in a slightly different grid - the Power Query grid.

Now you need to fix all the blank cells in column A. If you were to do this in the Excel user interface, the unwieldy command sequence is Home, Find & Select, Go To Special, Blanks, Equals, Up Arrow, Ctrl+Enter.

In Power Query, select Transform, Fill, Down.

All of the null values are replaced with the value from above. With Power Query, it takes three clicks instead of seven.

Next problem: The quarters are going across instead of down. In Excel, you can fix this with a Multiple Consolidation Range pivot table. This requires 12 steps and 23+ clicks.

In Power Query select the two columns that are not quarters. Open the Unpivot Columns dropdown on the Transform tab and choose Unpivot Other Columns, as shown below.

Right-click on the newly created Attribute column and rename it Quarter instead of Attribute. Twenty-plus clicks in Excel becomes five clicks in Power Query.

Now, to be fair, not every cleaning step is shorter in Power Query than in Excel. Removing a column still means right-clicking a column and choosing Remove Column. But to be honest, the story here is not about the time savings on Day 1.

When you are done cleaning the data, click Close & Load as shown below.

In a few seconds, your transformed data appears in Excel. Awesome.

But Wait: Power Query Remembers All of Your Steps

Look on the right side of the Power Query window. There is a list called Applied Steps. It is an instant audit trail of all of your steps. Click any gear icon to change your choices in that step and have the changes cascade through the future steps. Click on any step for a view of how the data looked before that step.

The Payoff: Clean Data Tomorrow With One Click

But again, the Power Query story is not about the time savings on Day 1. When you select the data returned by Power Query, a Queries & Connections panel appears on the right side of Excel, and on it is a Refresh button. (We need an Edit button here, but because there isn't one, you have to right-click the original query to view or make changes to the original query).

It is fun to clean data on Day 1. I love doing something new. But when my manager sees the resulting report and says Beautiful. Can you do this every day?I quickly grow to hate the tedium of cleaning the same data set every day.

So, to demonstrate Day 400 of cleaning the data, I have completely changed the original file. New products, new customers, smaller numbers, more rows, as shown here. I save this new version of the file in the same path and with the same filename as the original file.

If I open the query workbook and click Refresh, in a few seconds, Power Query reports 92 rows instead of 68 rows.

Cleaning the data on Day 2, Day 3, Day, 4,...Day 400,...Day Infinity now takes two clicks.

This one example only scratches the surface of Power Query. I have several more YouTube videos in a playlist at https://mrx.cl/pqplaylist. You will learn about other features, such as these:

• Combining all Excel or CSV files from a folder into a single Excel grid
• Converting a cell with Apple;Banana;Cherry;Dill;Eggplant to five rows in Excel
• Doing a VLOOKUP to a lookup workbook as you are bringing data into Power Query
• Making a single query into a function that can be applied to every row in Excel

Bonus Tip: Tame the Sequence of Refresh All

Say that you have a Power Query and then build a pivot table on the results. When you go to the Data Tab and click Refresh All, you need the Power Query refresh to complete before the pivot table refreshes. Excel MVP Celia Alves from SolveAndExcel.ca offers this important tip: Right-click the query in the Queries & Connections pane and choose Properties. Make sure to uncheck the Enable Background Refresh setting. This forces the Power Query to finish refreshing before the pivot table is updated. Otherwise, you will have to click Refresh All twice in order to get the pivot table to update.

Bonus Tip: Data Profiling in Power Query

Some excellent data profiling options appeared in late 2019. They are not obvious to find, as they are on the View tab in Power Query. Use the Column Quality, Column Distribution, and Column Profile boxes.

My favorite is the Column Profile. As you would expect, Weekday has 7 distinct values.

Choose one column and then Column Profile. You will see a window at the bottom with statistics and a frequency distribution.

Column Quality shows if you have any empty or error cells in each column:

For a complete description of Power Query, check out Master Your Data by Ken Puls and Miguel Escobar.

Thanks to Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser, and Colin Michael for nominating Power Query.