Power Query Basics: How to Import and Clean Data Consistently
Welcome back to our Global Excel Solutions series! As part of our Week 9: Automation & Power Query journey, we are diving into Post 2: Power Query Basics: How to Import and Clean Data Consistently. If you regularly download messy CSV reports from your company database, clean up the exact same columns, and delete the same blank rows every month, it is time to stop doing it manually.
Here is why you should switch from manual data prep to Power Query:
The Manual Way
Opening CSV files and copy-pasting data into a master workbook.
Manually formatting dates, deleting blank rows, and splitting text columns every single week.
The Power Query Way
Connecting Excel directly to a folder or file so data imports automatically.
Building a "recipe" of cleaning steps that Excel remembers and applies instantly with one click of a Refresh button.
Full step-by-step walkthrough + quick workflow guide below:
What is Power Query?
Power Query is Excel’s built-in data connection and transformation engine. It acts as a bridge between your raw, messy data source (like a folder of CSVs, a database, or a website) and your clean Excel spreadsheet. Instead of changing the raw data itself, Power Query records every cleaning step you make (like deleting columns or filtering out blanks) into a repeatable query that runs automatically the next time you get new data.
Step-by-Step Instructions
Connect to Your Data: Go to the Data tab on the Excel ribbon, click Get Data, and choose your source (e.g., From File > From Text/CSV).
Open the Editor: When the preview window pops up, do not click Load! Instead, click Transform Data to open the Power Query Editor window.
Clean Your Data: Use the buttons in the Power Query ribbon to format your data. You can Remove Columns, Filter Rows to remove blanks, or use Split Column to separate text. Every action you take is recorded in the Applied Steps pane on the right.
Load to Excel: Once your data looks perfect, click Close & Load in the top left corner. The pristine data will appear in a new green Excel Table.
Refresh Next Time: Next month, when you get a new raw file, simply replace the old one, right-click your green Excel table, and hit Refresh. Power Query instantly re-runs all your cleaning steps on the new data!
Top Use Cases for Power Query
Combining Multiple Files: Tell Power Query to look at a specific folder on your computer and automatically stack every single monthly sales CSV file it finds into one massive master table.
Automating CRM Cleaning: Connect directly to messy Salesforce or HubSpot exports to strip out unnecessary tracking columns and fix misaligned date formats before they hit your dashboard.
Unpivoting Data: Transform wide, unreadable cross-tabulated data tables (where months stretch endlessly across columns) into clean, flat database lists ready for Pivot Tables.
Pro Tip: Look at the Applied Steps window on the right side of the Power Query Editor. If you make a mistake while cleaning, do not panic! You can simply click the red "X" next to that specific step to undo it without ruining the rest of your query.
