Tutorials›Excel›Power Query Basics
🟢 Free Demo
Excel TutorialTopic 12 of 31

Power Query Basics

Import, clean, and transform raw data from any source automatically using Power Query.

✅ What You Will Learn

›What Power Query is and how it differs from formulas
›How to import data from CSV, Excel files, and web pages
›How to apply basic transformations: rename, remove, change types
›How to filter rows and sort data in the Power Query Editor
›How to load cleaned data back to Excel and refresh it automatically

Power Query is Excel built-in ETL (Extract, Transform, Load) tool. It lets you connect to a data source, clean and reshape the data using a visual interface, and load the result into Excel as a table. Every step you take is recorded and can be replayed with one click whenever the source data updates.

This is fundamentally different from manual data cleaning with formulas. With formulas, you clean data once and the formulas sit alongside it. With Power Query, you define a series of steps once and can refresh the entire process with a single click — even when the source file has thousands of new rows added.

Power Query is available in Excel 2016 and later under the Data tab (Get Data group). In Excel 2010 and 2013 it can be installed as a free add-in from Microsoft.

Examples

Example 1Connect to a CSV file and load it into Excel
Steps:
1. Data tab → Get Data → From File → From Text/CSV
2. Navigate to your CSV file → Import
3. Preview appears — check delimiter (comma, tab, etc.)
4. Click "Transform Data" to open Power Query Editor
   (or "Load" to load without transformation)
5. In the Editor, click "Close & Load" to bring data into Excel
💡

Power Query creates a connection. Next time you click Refresh (Data tab → Refresh All), it re-reads the CSV and applies all your transformation steps to the updated data.

Example 2Common first steps in the Power Query Editor
1. Remove unnecessary columns:
   Select columns to keep → right-click → Remove Other Columns

2. Rename columns (double-click header)

3. Change data types (click the icon left of each column header):
   ABC = text, 123 = whole number, 1.2 = decimal, calendar = date

4. Filter rows (click dropdown arrow in column header)

5. Remove blank rows:
   Home tab → Remove Rows → Remove Blank Rows
💡

Every step appears in the "Applied Steps" pane on the right. Click any step to see what the data looked like at that point — and delete or edit any step if you make a mistake.

📌 Key Points to Remember

  • ✓Power Query is non-destructive — it never changes the source file; it only reads from it
  • ✓Each transformation step is recorded and replayable — edit any step in the Applied Steps pane
  • ✓Data tab → Refresh All re-runs the entire query on the latest source data
  • ✓Power Query M language runs behind the scenes — the visual interface generates the code automatically
  • ✓Connect to multiple files in one folder (Get Data → From Folder) to combine monthly files automatically

🏢 Real-World Application

Analysts at logistics companies receive daily CSV exports from their warehouse system — each file has 50,000 rows of raw order data with inconsistent formatting. Using Power Query, the analyst built a one-time cleanup process (remove blank rows, fix date formats, split a combined address column). Now every morning they click Refresh All and the clean, analysis-ready table updates in 10 seconds. What used to take 45 minutes of manual cleanup per day is now a single button press.

⚠️ Common Mistakes to Avoid

WRONGDeleting or renaming the source file after building a Power Query connection
FIXThe query remembers the file path. If the file moves or is renamed, right-click the query in the Queries & Connections pane → Properties → update the source path.
WRONGLoading data to the sheet with "Load" instead of "Transform Data" first
FIXAlways click "Transform Data" to open the editor first so you can verify column types and data quality before loading. You can always load without transforming but it is harder to fix issues later.
WRONGNot setting column data types — numbers imported as text
FIXAlways set data types in Power Query by clicking the type icon (left of column header). Do it in Power Query, not after loading to Excel, so it is applied on every refresh.
✏️Test Yourself

What happens to Power Query transformation steps when you click "Refresh All"?

❓ Frequently Asked Questions

Is Power Query available in all Excel versions?

Power Query is built into Excel 2016 and later (Data tab → Get Data). For Excel 2010 and 2013, download the free Power Query add-in from Microsoft. It is not available in Excel for Mac before 2019.

What is the difference between Power Query and VLOOKUP/formulas?

Formulas transform data cell by cell. Power Query transforms the entire dataset at once, records every step, and can re-run the entire process on updated data with one click. For recurring imports and cleanups, Power Query is far more efficient.

Can Power Query connect to live databases?

Yes — Power Query can connect to SQL Server, MySQL, Oracle, Azure, and many other sources directly via Get Data → From Database. Changes in the database are pulled on Refresh.

✏️ Practice Exercise

Download any public CSV dataset (e.g. from data.gov.in or Kaggle). Import it into Excel using Power Query. In the Power Query Editor: remove any columns you do not need, fix data types for numeric and date columns, remove blank rows, rename at least three columns, and filter out any rows where the key metric is null or zero. Load the clean data to a new sheet.

← PreviousSlicers & TimelinesNext →Power Query Transforms
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.