How to Use Power Query in Excel - Practical Guide

Learn to use Power Query to import, clean, and transform data from multiple sources without manual entry. You'll discover how to connect to CSV, Excel, databases, and web sources, then shape raw data into analysis-ready formats—saving hours on data prep work.

Why This Matters

Power Query automates tedious data preparation tasks and handles large datasets efficiently, reducing errors and freeing time for analysis and decision-making.

Prerequisites

•Excel 2016 or later (or Excel 365)
•Basic understanding of data structure and spreadsheets
•Access to data source (CSV, Excel file, database, or web URL)

Step-by-Step Instructions

Open Power Query Editor

Go to Data tab > Get Data > From File (or other source). Select your data source and click Open to launch Power Query Editor.

Preview and Load Data

Review the data preview in Power Query Editor. Click Home > Close & Load to import data into Excel, or continue editing first.

Remove Unwanted Columns

Right-click column headers and select Remove Columns, or select multiple columns and use Home > Remove Columns to eliminate unnecessary data.

Clean and Transform Data

Use Home tab options: Remove Duplicates, Remove Errors, Change Data Type, or use Transform tab for splitting/merging columns and fixing case issues.

Apply Changes and Refresh

Click Home > Close & Load to finalize. Use Data > Refresh All to update the dataset when source data changes.

Alternative Methods

Using Get & Transform (Legacy)

In older Excel versions, access Power Query via Data > From Other Sources > From Text or From Web. Functionality is similar but interface differs slightly.

Manual Data Import with Paste Special

For small datasets, manually copy and paste data, then use Data > Text to Columns to format. Not recommended for large or recurring imports.

Tips & Tricks

✓Use Home > Choose Columns early to hide irrelevant columns and simplify your view.
✓Apply sorting and filtering in Power Query Editor before loading to reduce file size.
✓Name your queries meaningfully (right-click query > Rename) for easier maintenance.

Pro Tips

★Combine multiple data sources using Append Queries (Home > Append Queries) to consolidate data from different files or sheets.
★Use Merge Queries (Home > Merge Queries) to join data from two tables on a common column, similar to SQL JOIN operations.
★Create reusable queries and parameterize file paths to automate imports across multiple similar datasets.

Troubleshooting

Power Query Editor won't open

Ensure you're using Excel 2016 or later. In Excel 2013, Power Query is an add-in available for download from Microsoft. Restart Excel if the option doesn't appear.

Data loads as text instead of numbers

Select the column in Power Query Editor, go to Home > Change Data Type, and choose Number or Decimal. Apply before loading.

Refresh button is greyed out

Verify the source file exists and is accessible. If imported from a web source, check your internet connection and URL validity.

Related Excel Formulas

VLOOKUP INDEX-MATCH FILTER

Frequently Asked Questions

Can Power Query connect to databases like SQL Server?

Yes. Use Data > Get Data > From Database to connect to SQL Server, Oracle, or other databases. You'll need appropriate database credentials and drivers installed.

Is Power Query available in Excel Online?

Power Query is available in Excel 365 desktop version and limited in Excel Online. For full functionality, use the desktop application.

How do I schedule automatic data refreshes?

Excel doesn't natively schedule automatic refreshes on a timer. Use a VBA macro or Power Automate to trigger refreshes on a schedule, or manually refresh by clicking Data > Refresh All.