Excel Magic: Clean Messy Data in Seconds with Get & Transform! #excel #powerquery #datacleaning, by Pratap Narula on YouTube. 9 min 8 s.
- Video length
- 9 min 8 s
- Time to do it
- 15 minutes
- Difficulty
- Easy
- Steps
- 8
What you need
Steps
Open your workbook and click any cell inside the messy data. Press Ctrl+T to turn the range into a table, tick My table has headers and click OK.
With a cell still selected in the table, go to the Data tab and click From Table/Range. Excel opens the Power Query Editor in a new window.
Check the column headers and data types in the editor. Click the icon at the left of each header to set the correct type, such as Text or Whole Number.
Select a text column, right-click the header and choose Transform, then Trim to remove extra spaces. Use Format, then lowercase or Capitalize Each Word to fix text case.
To fix inconsistent values, right-click a cell and choose Replace Values. Enter the wrong value in Value To Find and the correct one in Replace With, then click OK.
Repeat trim, case and replace steps on each column that needs it. Each action is recorded in the Applied Steps list on the right.
Click Close & Load in the top left. Power Query loads the cleaned data into a new worksheet as a table.
To reuse the cleanup later, paste new raw data into the source table and click Refresh All on the Data tab. The steps run again automatically.
Tips and common mistakes
If a step goes wrong, click the X next to it in Applied Steps to remove it, or delete the step and redo it.
Keep your raw data on its own sheet and load the clean result to a new sheet so the original stays untouched.
Name your query in the Query Settings pane so you can find it later when you have several.
Save the file as.xlsx before loading, since older.xls files may not support queries.
Questions
Do I need to install anything for Power Query?
No. Get & Transform is built into Excel 2016 and later and Excel for Microsoft 365. In older versions you may need the free Power Query add-in.
Will the cleanup run again when my data changes?
Yes. Paste or edit the raw data in the source table, then click Refresh All. Power Query replays every step you recorded.
Can I undo a step I added by mistake?
Yes. Open the query in the Power Query Editor and delete the step in the Applied Steps list, then load the data again.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
