Load and Clean Data with Power Query
February 14, 2023 - by Bill Jelen
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.
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