Load and Clean Data with Power Query


February 14, 2023 - by

Load and Clean Data with Power Query

Problem: I get a file from the ERP system that is formatted incorrectly. I routinely have to do the same formatting steps every week.

Strategy: The tools in the Get & Transform group of the Data tab started out as a free Power Query add-in for Excel 2010 and Excel 2013. While Power Query offers some tools that are similar to Excel tools, they also offer data-cleansing tools that are not available in Excel.


Power Query Tool Compare/Contrast to Excel

Split Column Like Text to Columns, but better options



Remove Column Like Deleting Columns

Remove rows by Filtering Similar to Filter

Group by Like Data Consolidate

Fill Down Like Ctrl+D

Unpivot Other Columns No equivalent in Excel

Add Column Like formulas, but new syntax

To start, choose New Query, From File, From CSV. Browse to the file and choose to Edit the Query.

As you clean the data in Power Query, your past steps are shown in the window on the right side of the screen. You can click any item in the list and modify it. This becomes a great audit trail when the internal auditor wants to know how you are producing the report.

The Applied Steps panel in Power Query lists all of the data cleansing actions you've performed. Hover over a step and an "X" icon for Delete appears.
Figure 1012. All of the steps you’ve done are shown.

After you finish cleaning the data in Power Query, choose Close & Load. The data is returned to the Excel grid. A panel on the right identifies that the data came from Power Query and offers a Refresh button. Click Refresh and Excel will re-execute all of the Applied Steps on the new source file.


This article is an excerpt from Power Excel With MrExcel

Title photo by JESHOOTS.COM on Unsplash