How to Use Power Query in Excel: Step-by-Step Guide

To use Power Query in Excel, go to the Data tab and select Get Data to import data from various sources. Then use the Power Query Editor to clean and transform your data before loading it into your worksheet.

Syntax

Power Query uses a visual interface and a formula language called M to transform data. The basic steps include:

• Get Data: Import data from files, databases, or online sources.
• Transform Data: Use the Power Query Editor to filter, sort, merge, and clean data.
• Load Data: Load the transformed data back into Excel as a table or connection.

The M language syntax looks like this:

let
    Source = Csv.Document(File.Contents("path\to\file.csv")),
    FilteredRows = Table.SelectRows(Source, each [Column1] > 10)
in
    FilteredRows

Here, let defines steps, and in returns the final result.

m

let
    Source = Csv.Document(File.Contents("C:\\Users\\User\\Documents\\data.csv")),
    FilteredRows = Table.SelectRows(Source, each [Column1] > 10)
in
    FilteredRows

Example

This example shows how to import a CSV file, filter rows where the value in Sales column is greater than 100, and load the result into Excel.

m

let
    Source = Csv.Document(File.Contents("C:\\Users\\User\\Documents\\sales.csv"), [Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    FilteredRows = Table.SelectRows(PromotedHeaders, each [Sales] > 100)
in
    FilteredRows

Output

Table with rows where Sales > 100, showing columns like Date, Product, Sales

Common Pitfalls

• Wrong file path: Make sure the file path is correct and accessible.
• Data types: Check that columns have correct data types after import.
• Refreshing data: Remember to refresh the query if the source data changes.
• Loading options: Choose to load data as a table or only create a connection to avoid clutter.

m

/* Wrong way: Using incorrect file path */
let
    Source = Csv.Document(File.Contents("C:\\wrong_path\\file.csv"))
in
    Source

/* Right way: Correct file path */
let
    Source = Csv.Document(File.Contents("C:\\Users\\User\\Documents\\file.csv"))
in
    Source

Quick Reference

Action	Description	Where to find
Get Data	Import data from files, databases, web, etc.	Data tab > Get Data
Transform Data	Clean, filter, merge, and shape data	Power Query Editor window
Load Data	Load transformed data into Excel worksheet or data model	Close & Load button in Power Query Editor
Refresh Data	Update data from the original source	Right-click query table > Refresh
Advanced Editor	Edit or write M code manually	Power Query Editor > View > Advanced Editor

Key Takeaways

Use the Data tab's Get Data button to start Power Query in Excel.

Power Query Editor lets you clean and transform data visually or with M code.

Always check file paths and data types to avoid errors.

Load data as a table or connection depending on your needs.

Refresh queries to keep your data up to date.