Excel Data Transformation Reference

Excel Power Query Cheat Sheet

Import, clean, transform, combine, and refresh data in Excel with Power Query. Use this practical reference for common transformations, query workflows, joins, M functions, and data-cleaning tasks.

View Quick Reference Browse Power Query

Beginner Friendly
Data Cleaning
Practical M Examples
Printable Reference

Power Query Editor

Queries

Sales_Data

Customers

Products

= Table.SelectRows(Source, each [Sales] > 1000)

Region

Product

Sales

East

Laptop

1,840

West

Monitor

1,260

North

Keyboard

1,120

Applied Steps

Source

Changed Type

Removed Columns

Filtered Rows

Search all Power Query sections.

Task	Editor Action	M Example
Filter rows Keep only rows that match a condition.	Filter dropdown	`Table.SelectRows(Source, each [Sales] > 1000)`
Remove columns Delete columns you do not need.	Home → Remove Columns	`Table.RemoveColumns(Source, {"Notes", "Temp"})`
Rename columns Change one or more column names.	Right-click column → Rename	`Table.RenameColumns(Source, {{"OldName", "NewName"}})`
Change data type Convert columns to text, number, date, and more.	Transform → Data Type	`Table.TransformColumnTypes(Source, {{"Sales", type number}})`
Remove duplicates Keep only unique rows or values.	Home → Remove Rows → Remove Duplicates	`Table.Distinct(Source, {"CustomerID"})`
Replace values Replace matching values in a column.	Transform → Replace Values	`Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceValue, {"Status"})`
Split a column Separate text using a delimiter.	Transform → Split Column	`Table.SplitColumn(Source, "Name", Splitter.SplitTextByDelimiter(" "))`
Fill down Fill null cells with the value above.	Transform → Fill → Down	`Table.FillDown(Source, {"Region"})`
Group rows Aggregate rows by one or more columns.	Transform → Group By	`Table.Group(Source, {"Region"}, {{"Total", each List.Sum([Sales])}})`
Unpivot columns Convert wide data into attribute-value rows.	Transform → Unpivot Columns	`Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")`
Merge queries Join tables using matching columns.	Home → Merge Queries	`Table.NestedJoin(Sales, {"ID"}, Customers, {"ID"}, "Customer")`
Append queries Stack tables vertically.	Home → Append Queries	`Table.Combine({January, February, March})`
Add custom column Create a new column with an M expression.	Add Column → Custom Column	`Table.AddColumn(Source, "Total", each [Qty] * [Price])`
Trim text Remove leading and trailing spaces.	Transform → Format → Trim	`Table.TransformColumns(Source, {{"Name", Text.Trim}})`
Extract year Return the year from a date value.	Add Column → Date → Year	`Date.Year([OrderDate])`

Remember: Power Query records transformations as sequential Applied Steps. When the source data changes, the query can usually be refreshed instead of repeating the same cleaning and transformation work manually.

Connect to Data

Import data from Excel workbooks, CSV files, folders, databases, web sources, and many other supported connectors.

Clean & Transform

Remove unnecessary rows, fix data types, replace values, split columns, remove duplicates, and reshape messy datasets.

Combine Data

Merge related tables using matching keys or append multiple tables together into one consolidated dataset.

Load & Refresh

Load the finished result into Excel and refresh the query when updated source data becomes available.

Why Use Power Query?

Manual data cleaning vs. Power Query

Manual Workflow

Open the latest source file.
Delete unnecessary rows and columns.
Fix formats and data types.
Copy or combine tables manually.
Repeat the same process next time.

Power Query Workflow

Connect to the source data.
Define transformation steps once.
Save the query with the workbook.
Update or replace the source data.
Refresh the query to repeat the process.

Simple Example

Cleaning a monthly sales export

Imagine receiving a new CSV export every month. Each file contains extra columns, inconsistent text formatting, duplicate rows, and dates stored in the wrong format.

1 Import the CSV

2 Remove unused columns

3 Fix data types

4 Remove duplicates

5 Load the clean table

6 Refresh next month

Key idea: Power Query does not normally edit the original source data. Instead, it stores a sequence of transformation steps and produces a cleaned result that can be loaded into Excel.

If you are still building core spreadsheet skills, start with our Excel Cheat Sheet for Beginners.

01 Connect

Connect to a Data Source

Start by connecting Power Query to the data you want to work with, such as an Excel table, CSV file, folder, database, or web source.

Typical action: Data → Get Data

02 Transform

Clean & Transform

Shape the data inside Power Query Editor by filtering rows, changing data types, renaming columns, removing duplicates, replacing values, and applying other transformations.

Typical result: A sequence of Applied Steps

03 Combine

Merge or Append Data

Combine related datasets when necessary. Merge queries to join matching tables, or append queries to stack tables with similar structures.

Common tools: Merge Queries · Append Queries

04 Load

Load the Result

Send the transformed result back to Excel as a worksheet table, a connection, or another supported destination depending on your workflow.

Typical action: Home → Close & Load

05 Refresh

Refresh When Data Changes

When the source data is updated, refresh the query to run the saved transformation steps again and update the loaded result.

Main benefit: Repeatable data preparation

How Applied Steps Work

Power Query builds transformations step by step

Every action you perform in Power Query Editor can create a new step. Each step uses the result of the previous step, creating a repeatable transformation pipeline.

Example M query

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"Date", type date}, {"Sales", type number}}
    ),
    #"Filtered Rows" = Table.SelectRows(
        #"Changed Type",
        each [Sales] > 1000
    )
in
    #"Filtered Rows"

Best practice: Build queries as clear, logical steps and give important steps meaningful names. This makes complex transformations easier to understand, troubleshoot, and maintain later.

Excel

Excel Tables & Workbooks

Import data from a table in the current workbook or connect to tables, worksheets, and named ranges stored in another workbook.

Common path Data → From Table/Range

Files

CSV & Text Files

Connect to delimited files such as CSV or text exports and transform them before loading the result into Excel.

Common path Data → Get Data → From File

Folder

Multiple Files from a Folder

Connect to a folder when many files share a similar structure. Power Query can combine them into one repeatable import process.

Common use Monthly exports or recurring reports

Database

Databases

Connect to supported database systems and select the tables, views, or other available objects needed for your analysis.

Common path Data → Get Data → From Database

Web

Web Data

Power Query can connect to supported web sources and retrieve structured data that can then be cleaned and transformed.

Common path Data → Get Data → From Web

Other

Additional Connectors

Depending on your Excel environment, additional connectors may be available for online services, cloud sources, and other data platforms.

Tip Check Data → Get Data for available sources

Standard Import Process

From source to Power Query Editor

Most imports follow the same sequence even when the source type changes.

Choose a source

Select the file, table, folder, database, or other connector.

Connect

Provide the file path, URL, server, or required credentials.

Preview the data

Inspect the available tables, sheets, files, or objects.

Choose Transform Data

Open Power Query Editor before loading when cleanup is needed.

Apply transformations

Clean, reshape, filter, combine, and validate the data.

Load the result

Return the transformed result to Excel when it is ready.

Load vs. Transform Data

Which option should you choose?

Load

Use when the data is already ready

Load the data directly when you do not need to clean, reshape, combine, or inspect it first.

Transform Data

Use when the data needs preparation

Open Power Query Editor when you need to change data types, remove rows, rename columns, merge tables, or perform other transformations.

M Example Import a CSV file

let
    Source = Csv.Document(
        File.Contents("C:\Data\sales.csv"),
        [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]
    ),
    #"Promoted Headers" = Table.PromoteHeaders(
        Source,
        [PromoteAllScalars=true]
    )
in
    #"Promoted Headers"

Tip: When data needs cleaning, choose Transform Data instead of loading it immediately. This lets you inspect the source and build repeatable transformation steps before the data reaches your worksheet.

Home Transform Add Column View

fx = Table.SelectRows(Source, each [Sales] > 1000)

Region	Product	Quantity	Sales
North	Laptop	4	4200
West	Monitor	8	2160
South	Keyboard	14	1260
East	Tablet	5	3500

Queries Pane

Shows the queries available in the workbook. Select a query to preview its data and edit its transformation steps.

Ribbon

Contains commands for importing, transforming, adding columns, combining queries, and controlling the editor view.

Formula Bar

Displays the M expression for the selected step. It can also be used to inspect or edit expressions directly.

Data Preview

Shows a preview of the current query result. Transformations are applied to this preview as you build the query.

Query Settings

Displays properties for the selected query and the sequence of Applied Steps used to create the current result.

Applied Steps

Lists each transformation in order. You can review, rename, reorder, edit, or remove steps when necessary.

Main Ribbon Tabs

Where common commands are located

Home

Manage queries, remove rows or columns, combine queries, refresh previews, and close the editor.

Transform

Change existing columns using tools such as data type, replace values, split, group, pivot, and unpivot.

Add Column

Create new columns from examples, conditions, indexes, dates, text operations, or custom M expressions.

View

Control editor features such as the Formula Bar, Query Settings, column quality, and profiling tools.

M Expression Example of an Applied Step

#"Filtered Rows" =
    Table.SelectRows(
        #"Changed Type",
        each [Sales] > 1000
    )

Useful tip: If the Formula Bar is hidden, enable it from the View tab. Seeing the generated M expressions makes it much easier to understand how Power Query translates editor actions into query steps.

For faster everyday work elsewhere in Excel, keep our Excel Keyboard Shortcuts Cheat Sheet nearby.

Data Type	Typical Use	Example	M Type
Text	Names, IDs, categories, codes, labels	`"North"`	`type text`
Whole Number	Counts, quantities, integer IDs	`125`	`Int64.Type`
Decimal Number	Measurements and numeric values with decimals	`19.95`	`type number`
Fixed Decimal Number	Values that need fixed decimal precision	`149.99`	`Currency.Type`
Percentage	Rates and percentage values	`0.25`	`Percentage.Type`
Date	Calendar dates without time	`2026-09-06`	`type date`
Time	Time values without a date	`14:30:00`	`type time`
Date/Time	Date and time stored together	`2026-09-06 14:30`	`type datetime`
Date/Time/Timezone	Date and time values that include timezone information	`2026-09-06T14:30:00+02:00`	`type datetimezone`
Duration	Elapsed time between values	`2.12:30:00`	`type duration`
True/False	Logical conditions and flags	`true`	`type logical`
Binary	File contents and binary source data	`[Binary]`	`type binary`

Check Types Early

Review data types near the beginning of a query. Incorrect types can cause filtering, sorting, joins, and calculations to behave unexpectedly.

IDs Are Often Text

Product codes, account numbers, and ZIP or postal codes may look numeric but are often better stored as text, especially when leading zeros matter.

Dates Must Be Real Dates

A date stored as text cannot reliably use date transformations until it has been converted to a proper date type.

Watch Regional Formats

Text such as 03/04/2026 can mean different dates depending on locale. Use locale-aware conversion when necessary.

Common M Patterns

Change and convert data types

Change Column Types Table.TransformColumnTypes

Table.TransformColumnTypes(
    Source,
    {
        {"OrderDate", type date},
        {"Quantity", Int64.Type},
        {"Sales", type number}
    }
)

Text to Number Number.FromText

Number.FromText([SalesText])

Text to Date Date.FromText

Date.FromText([DateText])

Value to Text Text.From

Text.From([CustomerID])

Locale-Aware Conversion

When dates or numbers use another regional format

Power Query can interpret values using a specific locale. This is useful when imported text uses date, decimal, or thousands separators that differ from your current Excel settings.

Example

Table.TransformColumnTypes(
    Source,
    {{"OrderDate", type date}},
    "en-US"
)

Common mistake: Do not assume that a column containing numbers should always use a numeric type. Values such as customer IDs, telephone numbers, postal codes, and product codes may need to remain text.

Columns

Remove Columns

Delete fields that are not needed in the final dataset. Removing unnecessary columns early can simplify the query and reduce the amount of data processed.

Table.RemoveColumns

Table.RemoveColumns(
    Source,
    {"Notes", "Temp", "InternalID"}
)

Columns

Keep Selected Columns

Keep only the columns required by the query. This can be useful when the source contains many fields but only a small subset is needed.

Table.SelectColumns

Table.SelectColumns(
    Source,
    {"Date", "Product", "Sales"}
)

Columns

Rename Columns

Replace unclear or inconsistent field names with readable, standardized names that are easier to use later in the workflow.

Table.RenameColumns

Table.RenameColumns(
    Source,
    {
        {"Cust_ID", "CustomerID"},
        {"Amt", "Sales"}
    }
)

Columns

Reorder Columns

Change the display order of columns so important fields appear together or follow the structure required by the final output.

Table.ReorderColumns

Table.ReorderColumns(
    Source,
    {"Date", "CustomerID", "Product", "Sales"}
)

Rows

Keep Top Rows

Keep a fixed number of rows from the beginning of a table. This is useful when testing a transformation or trimming imported metadata.

Table.FirstN

Table.FirstN(Source, 10)

Rows

Remove Top Rows

Remove header notes, title rows, metadata, or other unwanted rows that appear before the actual table data begins.

Table.Skip

Table.Skip(Source, 3)

Rows

Keep Bottom Rows

Keep only the final rows from a table, for example when the latest values are located at the bottom of a source file.

Table.LastN

Table.LastN(Source, 5)

Rows

Remove Duplicate Rows

Remove duplicate records from the entire table or use selected columns to determine which rows should be considered duplicates.

Table.Distinct

Table.Distinct(
    Source,
    {"CustomerID"}
)

Quick Reference

Common column and row operations

Task	Editor Action	M Function
Remove selected columns	Home → Remove Columns	`Table.RemoveColumns`
Keep selected columns	Home → Remove Other Columns	`Table.SelectColumns`
Rename a column	Right-click → Rename	`Table.RenameColumns`
Reorder columns	Drag column headers	`Table.ReorderColumns`
Keep top rows	Home → Keep Rows → Keep Top Rows	`Table.FirstN`
Remove top rows	Home → Remove Rows → Remove Top Rows	`Table.Skip`
Keep bottom rows	Home → Keep Rows → Keep Bottom Rows	`Table.LastN`
Remove duplicates	Home → Remove Rows → Remove Duplicates	`Table.Distinct`

Practical Example

Reduce a messy export to the fields you actually need

A sales export may contain dozens of technical or temporary fields. A common pattern is to keep the important columns, rename them, and remove duplicate records before continuing with the query.

Example transformation

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    #"Selected Columns" =
        Table.SelectColumns(
            Source,
            {"Cust_ID", "Product", "OrderDate", "Amount"}
        ),

    #"Renamed Columns" =
        Table.RenameColumns(
            #"Selected Columns",
            {
                {"Cust_ID", "CustomerID"},
                {"Amount", "Sales"}
            }
        ),

    #"Removed Duplicates" =
        Table.Distinct(
            #"Renamed Columns",
            {"CustomerID", "Product", "OrderDate"}
        )
in
    #"Removed Duplicates"

Best practice: Remove columns you do not need as early as practical. Smaller, cleaner datasets are easier to work with and can improve query readability and performance.

Filter

Filter Numeric Values

Keep rows where a numeric column meets a condition such as greater than, less than, equal to, or between specific values.

Sales greater than 1000

Table.SelectRows(
    Source,
    each [Sales] > 1000
)

Filter

Filter Text Values

Keep rows where text matches a value or where a column contains, starts with, or ends with specific text.

Region equals North

Table.SelectRows(
    Source,
    each [Region] = "North"
)

Filter

Filter Multiple Conditions

Combine conditions with logical operators such as and and or when a row must satisfy more than one rule.

Region and sales condition

Table.SelectRows(
    Source,
    each [Region] = "North"
        and [Sales] > 1000
)

Filter

Filter Dates

Filter rows by a specific date, date range, year, month, or other date-related condition after the column has a valid date type.

Orders from 2026

Table.SelectRows(
    Source,
    each Date.Year([OrderDate]) = 2026
)

Sort

Sort Ascending

Sort a table from lowest to highest, oldest to newest, or A to Z using one or more columns.

Sort by Sales ascending

Table.Sort(
    Source,
    {{"Sales", Order.Ascending}}
)

Sort

Sort Descending

Sort from highest to lowest, newest to oldest, or Z to A when the most recent or largest values should appear first.

Sort by Sales descending

Table.Sort(
    Source,
    {{"Sales", Order.Descending}}
)

Filter Operator Reference

Common conditions in M

Condition	M Pattern	Example
Equal to	`=`	`[Region] = "North"`
Not equal to	`<>`	`[Status] <> "Cancelled"`
Greater than	`>`	`[Sales] > 1000`
Less than	`<`	`[Stock] < 10`
Greater than or equal	`>=`	`[Quantity] >= 5`
Less than or equal	`<=`	`[Discount] <= 0.20`
AND condition	`and`	`[Sales] > 1000 and [Region] = "North"`
OR condition	`or`	`[Region] = "North" or [Region] = "West"`
Contains text	`Text.Contains`	`Text.Contains([Product], "Pro")`
Starts with	`Text.StartsWith`	`Text.StartsWith([Code], "A-")`

Multi-Column Sorting

Sort by more than one column

Power Query can sort by multiple fields in sequence. In the example below, Region is sorted alphabetically first, then Sales is sorted from highest to lowest within each region.

Multiple sort levels

Table.Sort(
    Source,
    {
        {"Region", Order.Ascending},
        {"Sales", Order.Descending}
    }
)

Practical Example

Keep recent high-value orders and sort them by sales

This pattern filters a sales table to orders from 2026 with sales above 1000, then sorts the remaining rows from highest to lowest.

Filter + sort pipeline

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {
                {"OrderDate", type date},
                {"Sales", type number}
            }
        ),

    #"Filtered Rows" =
        Table.SelectRows(
            #"Changed Type",
            each Date.Year([OrderDate]) = 2026
                and [Sales] > 1000
        ),

    #"Sorted Rows" =
        Table.Sort(
            #"Filtered Rows",
            {{"Sales", Order.Descending}}
        )
in
    #"Sorted Rows"

Important: Sorting is only necessary when row order matters in the final result. Avoid unnecessary sort steps in large queries because sorting can add extra processing work.

Duplicates

Remove Duplicate Rows

Remove repeated records from the entire table or use one or more selected columns to define what counts as a duplicate.

Table.Distinct

Table.Distinct(
    Source,
    {"CustomerID"}
)

Blanks

Remove Blank Rows

Remove rows that contain no meaningful values. This is useful when imported files contain empty lines around or inside the dataset.

Remove empty rows

Table.SelectRows(
    Source,
    each List.NonNullCount(
        Record.FieldValues(_)
    ) > 0
)

Nulls

Filter Out Null Values

Remove rows where an important column contains null, or replace null values with a more appropriate default.

Exclude null sales

Table.SelectRows(
    Source,
    each [Sales] <> null
)

Errors

Remove Error Rows

Remove rows containing errors in selected columns when those records cannot be corrected or are not useful in the final dataset.

Table.RemoveRowsWithErrors

Table.RemoveRowsWithErrors(
    Source,
    {"Sales"}
)

Text

Trim Extra Spaces

Remove leading and trailing spaces from text fields. This helps prevent values that look identical from being treated as different.

Text.Trim

Table.TransformColumns(
    Source,
    {{"Customer", Text.Trim}}
)

Text

Remove Non-Printable Characters

Clean text imported from external systems by removing control and other non-printable characters.

Text.Clean

Table.TransformColumns(
    Source,
    {{"Customer", Text.Clean}}
)

Replace

Replace Values

Standardize inconsistent values by replacing old labels, spelling variations, codes, or placeholders with a preferred value.

Table.ReplaceValue

Table.ReplaceValue(
    Source,
    "N/A",
    null,
    Replacer.ReplaceValue,
    {"Status"}
)

Case

Standardize Text Case

Convert inconsistent text to uppercase, lowercase, or proper case before grouping, matching, or merging datasets.

Text.Upper

Table.TransformColumns(
    Source,
    {{"Region", Text.Upper}}
)

Cleaning Quick Reference

Common data-cleaning tasks

Task	Editor Action	M Function
Remove duplicates	Home → Remove Rows → Remove Duplicates	`Table.Distinct`
Remove errors	Home → Remove Rows → Remove Errors	`Table.RemoveRowsWithErrors`
Replace values	Transform → Replace Values	`Table.ReplaceValue`
Trim text	Transform → Format → Trim	`Text.Trim`
Clean text	Transform → Format → Clean	`Text.Clean`
Uppercase	Transform → Format → UPPERCASE	`Text.Upper`
Lowercase	Transform → Format → lowercase	`Text.Lower`
Capitalize words	Transform → Format → Capitalize Each Word	`Text.Proper`

Null vs. Empty Text

They are not the same thing

In Power Query, null represents the absence of a value. An empty text value is still text, but contains no characters. Cleaning logic may need to handle both cases.

Null null

No value is present.

Empty Text ""

A text value exists but contains zero characters.

Whitespace " "

Text contains spaces and may need trimming.

Practical Cleaning Pipeline

Clean customer data before a merge

This example trims and cleans customer names, standardizes region values, replaces a placeholder with null, and removes duplicate customer IDs.

Multi-step cleaning example

let
    Source =
        Excel.CurrentWorkbook(){[Name="Customers"]}[Content],

    #"Trimmed Names" =
        Table.TransformColumns(
            Source,
            {{"Customer", Text.Trim}}
        ),

    #"Cleaned Names" =
        Table.TransformColumns(
            #"Trimmed Names",
            {{"Customer", Text.Clean}}
        ),

    #"Standardized Region" =
        Table.TransformColumns(
            #"Cleaned Names",
            {{"Region", Text.Upper}}
        ),

    #"Replaced Values" =
        Table.ReplaceValue(
            #"Standardized Region",
            "N/A",
            null,
            Replacer.ReplaceValue,
            {"Email"}
        ),

    #"Removed Duplicates" =
        Table.Distinct(
            #"Replaced Values",
            {"CustomerID"}
        )
in
    #"Removed Duplicates"

Best practice: Clean the columns that will be used for joins, grouping, and comparisons before those operations. Hidden spaces, inconsistent capitalization, nulls, and duplicate keys are common causes of unexpected results.

Split

Split by Delimiter

Divide a text column into multiple columns using a separator such as a space, comma, dash, slash, or another character.

Split full name by space

Table.SplitColumn(
    Source,
    "FullName",
    Splitter.SplitTextByDelimiter(
        " ",
        QuoteStyle.Csv
    ),
    {"FirstName", "LastName"}
)

Split

Split by Number of Characters

Split a fixed-width value when part of the text always occupies the same number of characters.

Split after first 3 characters

Table.SplitColumn(
    Source,
    "ProductCode",
    Splitter.SplitTextByPositions({0, 3}),
    {"Prefix", "Code"}
)

Merge

Merge Columns

Combine values from multiple columns into a single field using a delimiter such as a space, comma, hyphen, or custom separator.

Combine first and last name

Table.CombineColumns(
    Source,
    {"FirstName", "LastName"},
    Combiner.CombineTextByDelimiter(
        " ",
        QuoteStyle.None
    ),
    "FullName"
)

Extract

Extract Text Before a Delimiter

Keep only the text that appears before a delimiter. This is useful for separating codes, domains, prefixes, and structured identifiers.

Text.BeforeDelimiter

Text.BeforeDelimiter(
    [Email],
    "@"
)

Extract

Extract Text After a Delimiter

Keep the portion of a text value that appears after a selected delimiter.

Extract email domain

Text.AfterDelimiter(
    [Email],
    "@"
)

Extract

Extract Text Between Delimiters

Extract a value stored between known markers, such as a code inside brackets or parentheses.

Text.BetweenDelimiters

Text.BetweenDelimiters(
    [Description],
    "[",
    "]"
)

Text Transformation Reference

Common split, merge, and extract operations

Task	Editor Tool	M Function / Pattern
Split by delimiter	Transform → Split Column → By Delimiter	`Table.SplitColumn`
Split by position	Transform → Split Column → By Positions	`Splitter.SplitTextByPositions`
Merge columns	Transform → Merge Columns	`Table.CombineColumns`
Text before delimiter	Transform → Extract → Text Before Delimiter	`Text.BeforeDelimiter`
Text after delimiter	Transform → Extract → Text After Delimiter	`Text.AfterDelimiter`
Text between delimiters	Transform → Extract	`Text.BetweenDelimiters`
First characters	Transform → Extract → First Characters	`Text.Start`
Last characters	Transform → Extract → Last Characters	`Text.End`
Text range	Transform → Extract → Range	`Text.Middle`

Character Extraction

Extract text by position

When a value follows a predictable structure, position-based functions can extract the exact part you need without splitting the entire column.

First 3 characters

Text.Start([Code], 3)

Last 4 characters

Text.End([Code], 4)

Characters 4–7

Text.Middle([Code], 3, 4)

Practical Example

Turn an email address into username and domain columns

A common cleanup task is to extract reusable pieces of structured text. Here, one email field becomes two new columns without changing the original source.

Email extraction example

let
    Source =
        Excel.CurrentWorkbook(){[Name="Customers"]}[Content],

    #"Added Username" =
        Table.AddColumn(
            Source,
            "Username",
            each Text.BeforeDelimiter([Email], "@"),
            type text
        ),

    #"Added Domain" =
        Table.AddColumn(
            #"Added Username",
            "Domain",
            each Text.AfterDelimiter([Email], "@"),
            type text
        )
in
    #"Added Domain"

Tip: Use delimiter-based transformations when the separator is reliable. Use position-based extraction only when the source follows a consistent fixed-width structure.

Fill Down

Repeat the Value Above

Fill Down replaces null values with the most recent non-null value above them in the selected column.

Table.FillDown

Table.FillDown(
    Source,
    {"Region"}
)

Fill Up

Repeat the Value Below

Fill Up works in the opposite direction. Null values inherit the next non-null value found below them.

Table.FillUp

Table.FillUp(
    Source,
    {"Category"}
)

Before Fill Down

Region	Product	Sales
North	Laptop	4200
null	Monitor	2160
null	Keyboard	1260
South	Tablet	3500
null	Mouse	1100

After Fill Down

Region	Product	Sales
North	Laptop	4200
North	Monitor	2160
North	Keyboard	1260
South	Tablet	3500
South	Mouse	1100

Quick Reference

When to use each fill operation

Fill Down

Use when a heading or category appears once and applies to the rows that follow.

Common example: grouped reports

Fill Up

Use when a value appears after the rows it describes and should be copied upward into preceding null cells.

Common example: totals or labels below data

Multiple Columns

Power Query can fill several selected columns in the same step.

Useful for hierarchical reports

Multiple Columns

Fill more than one column at once

The column list passed to Table.FillDown or Table.FillUp can contain multiple fields.

Fill Region and Category

Table.FillDown(
    Source,
    {"Region", "Category"}
)

Practical Example

Normalize a grouped sales report

Some reports show the region only on the first row of each group. Filling the Region column down creates a proper value on every row, making the dataset easier to filter, group, merge, and analyze.

Grouped report cleanup

let
    Source =
        Excel.CurrentWorkbook(){[Name="SalesReport"]}[Content],

    #"Filled Region" =
        Table.FillDown(
            Source,
            {"Region"}
        ),

    #"Removed Blank Products" =
        Table.SelectRows(
            #"Filled Region",
            each [Product] <> null
        )
in
    #"Removed Blank Products"

Important: Fill Down and Fill Up only replace null values. If a cell contains an empty string, spaces, or another placeholder, clean or replace those values first.

Sum

Sum Values by Group

Group rows by a category and calculate the total of a numeric column for each unique value.

Total sales by region

Table.Group(
    Source,
    {"Region"},
    {
        {
            "Total Sales",
            each List.Sum([Sales]),
            type number
        }
    }
)

Count

Count Rows by Group

Count how many records belong to each category, such as orders per customer or transactions per region.

Orders per customer

Table.Group(
    Source,
    {"CustomerID"},
    {
        {
            "Order Count",
            each Table.RowCount(_),
            Int64.Type
        }
    }
)

Average

Calculate an Average

Return the average numeric value for each group using List.Average.

Average sales by product

Table.Group(
    Source,
    {"Product"},
    {
        {
            "Average Sales",
            each List.Average([Sales]),
            type number
        }
    }
)

Min / Max

Find Minimum and Maximum Values

Return the smallest and largest values inside each group with List.Min and List.Max.

Sales range by region

Table.Group(
    Source,
    {"Region"},
    {
        {"Min Sales", each List.Min([Sales]), type number},
        {"Max Sales", each List.Max([Sales]), type number}
    }
)

Aggregation Quick Reference

Common Group By calculations

Aggregation	M Pattern	Typical Use
Sum	`List.Sum([Sales])`	Total sales, quantity, cost, revenue
Average	`List.Average([Sales])`	Average order value or score
Minimum	`List.Min([Sales])`	Lowest value in each group
Maximum	`List.Max([Sales])`	Highest value in each group
Count Rows	`Table.RowCount(_)`	Number of records in each group
Count Values	`List.Count([Product])`	Number of values in a selected column
Distinct Count	`List.Count(List.Distinct([CustomerID]))`	Unique customers, products, or IDs
All Rows	`each _`	Keep each group’s nested table for later processing

Multiple Grouping Columns

Group by more than one field

Power Query can group by combinations of columns. This is useful for summaries such as sales by both Region and Product.

Region + Product

Table.Group(
    Source,
    {"Region", "Product"},
    {
        {
            "Total Sales",
            each List.Sum([Sales]),
            type number
        }
    }
)

Advanced Grouping

Keep all rows inside each group

The All Rows operation creates a nested table for each group instead of reducing it immediately to a single number. This is useful when additional calculations need to be performed inside each group.

Group into nested tables

Table.Group(
    Source,
    {"Region"},
    {
        {
            "Rows",
            each _,
            type table
        }
    }
)

Practical Example

Build a regional sales summary

This example groups sales records by Region and returns total sales, average sales, order count, and the largest order for each region.

Multiple aggregations

let
    Source =
        Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    #"Grouped Rows" =
        Table.Group(
            Source,
            {"Region"},
            {
                {
                    "Total Sales",
                    each List.Sum([Sales]),
                    type number
                },
                {
                    "Average Sales",
                    each List.Average([Sales]),
                    type number
                },
                {
                    "Order Count",
                    each Table.RowCount(_),
                    Int64.Type
                },
                {
                    "Largest Order",
                    each List.Max([Sales]),
                    type number
                }
            }
        )
in
    #"Grouped Rows"

Best practice: Check data types before grouping. Columns used for sums and averages should have a numeric type, while grouping keys should be cleaned and standardized so values such as "North" and "north " are not treated as separate groups.

Pivot

Pivot Column

Turn unique values from one column into separate columns and use another field as the values placed inside those new columns.

Table.Pivot

Table.Pivot(
    Source,
    List.Distinct(Source[Month]),
    "Month",
    "Sales",
    List.Sum
)

Unpivot

Unpivot Selected Columns

Convert multiple columns into two fields: one containing the original column names and one containing their values.

Table.Unpivot

Table.Unpivot(
    Source,
    {"Jan", "Feb", "Mar"},
    "Month",
    "Sales"
)

Unpivot

Unpivot Other Columns

Keep identifier columns unchanged and unpivot every other column. This is often safer when future source files may add new columns.

Table.UnpivotOtherColumns

Table.UnpivotOtherColumns(
    Source,
    {"Product"},
    "Month",
    "Sales"
)

Aggregate

Handle Duplicate Pivot Values

If several rows map to the same pivoted cell, Power Query needs an aggregation such as Sum, Average, Minimum, or Maximum.

Pivot with List.Sum

Table.Pivot(
    Source,
    List.Distinct(Source[Region]),
    "Region",
    "Sales",
    List.Sum
)

Wide Table

Product	Jan	Feb	Mar
Laptop	4200	5100	4800
Monitor	2100	2400	2250

After Unpivot

Product	Month	Sales
Laptop	Jan	4200
Laptop	Feb	5100
Laptop	Mar	4800
Monitor	Jan	2100
Monitor	Feb	2400
Monitor	Mar	2250

Quick Reference

Pivot vs. unpivot

Pivot Rows → Columns

Use when category values should become separate columns.

Unpivot Columns → Rows

Use when repeated measure columns should become one structured attribute and value pair.

Unpivot Other Columns Keep IDs Stable

Select identifier fields and automatically unpivot everything else.

When Unpivoting Helps

Convert report layouts into proper datasets

Many spreadsheets store months, years, departments, or scenarios across dozens of separate columns. Unpivoting converts that wide layout into a normalized structure that is easier to filter, group, merge, refresh, and analyze.

Monthly Reports Jan, Feb, Mar → Month + Value

Year Columns 2024, 2025, 2026 → Year + Value

Department Columns Sales, HR, IT → Department + Amount

Survey Responses Question columns → Question + Answer

Practical Example

Convert monthly sales columns into rows

This example keeps Product as the identifier and converts every remaining month column into a Month and Sales pair. New month columns added to the source are included automatically.

Dynamic unpivot pattern

let
    Source =
        Excel.CurrentWorkbook(){[Name="MonthlySales"]}[Content],

    #"Unpivoted Months" =
        Table.UnpivotOtherColumns(
            Source,
            {"Product"},
            "Month",
            "Sales"
        ),

    #"Changed Type" =
        Table.TransformColumnTypes(
            #"Unpivoted Months",
            {
                {"Month", type text},
                {"Sales", type number}
            }
        )
in
    #"Changed Type"

Best practice: Prefer Table.UnpivotOtherColumns when your identifier columns are stable but new measure columns may appear in future source files. This can make a refreshable query more resilient to changing data.

Merge

Merge Two Queries

Match rows between two tables using a shared key. The result adds a nested table column that can then be expanded.

Match Sales to Customers

Table.NestedJoin(
    Sales,
    {"CustomerID"},
    Customers,
    {"CustomerID"},
    "CustomerData",
    JoinKind.LeftOuter
)

Expand

Expand Merged Columns

After the merge, expand the nested table column to bring selected fields from the second query into the first table.

Expand customer fields

Table.ExpandTableColumn(
    #"Merged Queries",
    "CustomerData",
    {"CustomerName", "Region"},
    {"CustomerName", "Region"}
)

Multiple Keys

Merge on Multiple Columns

Use more than one key when a single field is not enough to uniquely identify the correct matching row.

Customer + Date match

Table.NestedJoin(
    Orders,
    {"CustomerID", "OrderDate"},
    Targets,
    {"CustomerID", "OrderDate"},
    "TargetData",
    JoinKind.LeftOuter
)

New Query

Merge as New

Use Merge Queries as New when you want to keep both original queries unchanged and create a separate merged result.

Home → Merge Queries → Merge Queries as New

Sales Query

CustomerID	Product	Sales
C101	Laptop	4200
C102	Monitor	2100
C103	Tablet	3500

Customers Query

CustomerID	Customer	Region
C101	Northwind	North
C102	Contoso	West
C103	Fabrikam	South

Merged Result

CustomerID	Product	Sales	Customer	Region
C101	Laptop	4200	Northwind	North
C102	Monitor	2100	Contoso	West
C103	Tablet	3500	Fabrikam	South

Merge Workflow

How to merge two queries in Excel

01 Choose the Primary Query

Open the query that should keep its rows and receive columns from the second table.

02 Select Merge Queries

Go to Home → Merge Queries and choose the second query.

03 Select Matching Keys

Click the corresponding key column in each table. For multiple keys, select columns in the same order.

04 Choose a Join Type

Select the relationship that determines which matched and unmatched rows should remain.

05 Expand the Result

Use the expand button on the new nested column and select the fields you want to add.

Matching Keys

The key columns must be compatible

Merge problems are often caused by differences in data type or text formatting. Clean and standardize both key columns before merging.

✓

Same data type on both sides

✓

Leading and trailing spaces removed

✓

Consistent capitalization where required

✓

Correct columns selected in the same order

✓

Duplicate keys understood before expanding

Practical Example

Add customer names and regions to sales data

This example keeps every sales row, matches it to the Customers query by CustomerID, then expands only the CustomerName and Region fields.

Merge + expand

let
    Sales =
        Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    Customers =
        Excel.CurrentWorkbook(){[Name="Customers"]}[Content],

    #"Merged Queries" =
        Table.NestedJoin(
            Sales,
            {"CustomerID"},
            Customers,
            {"CustomerID"},
            "CustomerData",
            JoinKind.LeftOuter
        ),

    #"Expanded CustomerData" =
        Table.ExpandTableColumn(
            #"Merged Queries",
            "CustomerData",
            {"CustomerName", "Region"},
            {"CustomerName", "Region"}
        )
in
    #"Expanded CustomerData"

Watch for duplicate keys: if the second table contains several matching rows for the same key, expanding the merged table can create multiple result rows for a single row in the first query. Confirm whether the relationship is truly one-to-one or one-to-many before expanding.

Most Common

Left Outer

Keeps every row from the first table and matches rows from the second table where possible.

Keep: All left rows + matching right rows

JoinKind.LeftOuter

Table.NestedJoin(
    Sales,
    {"CustomerID"},
    Customers,
    {"CustomerID"},
    "CustomerData",
    JoinKind.LeftOuter
)

Matched Only

Inner

Keeps only rows where the selected key exists in both tables. Unmatched rows from either side are excluded.

Keep: Only matching rows

JoinKind.Inner

Table.NestedJoin(
    Orders,
    {"ProductID"},
    Products,
    {"ProductID"},
    "ProductData",
    JoinKind.Inner
)

All Rows

Full Outer

Keeps every row from both tables, whether a matching key exists or not.

Keep: All left rows + all right rows

JoinKind.FullOuter

Table.NestedJoin(
    TableA,
    {"ID"},
    TableB,
    {"ID"},
    "Matches",
    JoinKind.FullOuter
)

Right Side

Right Outer

Keeps every row from the second table and matches rows from the first table where possible.

Keep: All right rows + matching left rows

JoinKind.RightOuter

Table.NestedJoin(
    Sales,
    {"ProductID"},
    Products,
    {"ProductID"},
    "ProductData",
    JoinKind.RightOuter
)

Find Missing

Left Anti

Keeps rows from the first table that do not have a matching key in the second table.

Keep: Unmatched left rows only

JoinKind.LeftAnti

Table.NestedJoin(
    Sales,
    {"CustomerID"},
    Customers,
    {"CustomerID"},
    "Matches",
    JoinKind.LeftAnti
)

Find Missing

Right Anti

Keeps rows from the second table that do not have a matching key in the first table.

Keep: Unmatched right rows only

JoinKind.RightAnti

Table.NestedJoin(
    Sales,
    {"ProductID"},
    Products,
    {"ProductID"},
    "Matches",
    JoinKind.RightAnti
)

Join Type Reference

Choose the right merge relationship

Join Type	M Constant	Rows Kept	Typical Use
Left Outer	`JoinKind.LeftOuter`	All left + matching right	Add lookup data without losing primary rows
Right Outer	`JoinKind.RightOuter`	All right + matching left	Preserve every row from the second table
Full Outer	`JoinKind.FullOuter`	All rows from both tables	Compare complete datasets
Inner	`JoinKind.Inner`	Matching rows only	Keep records present in both tables
Left Anti	`JoinKind.LeftAnti`	Unmatched left rows	Find missing lookup records
Right Anti	`JoinKind.RightAnti`	Unmatched right rows	Find records missing from the first table

Practical Example

Find sales records with missing customers

A Left Anti join is useful for data quality checks. It returns sales rows whose CustomerID does not exist in the Customers query.

Missing customer check

let
    Sales =
        Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    Customers =
        Excel.CurrentWorkbook(){[Name="Customers"]}[Content],

    #"Missing Customers" =
        Table.NestedJoin(
            Sales,
            {"CustomerID"},
            Customers,
            {"CustomerID"},
            "CustomerMatch",
            JoinKind.LeftAnti
        )
in
    #"Missing Customers"

Rule of thumb: use Left Outer when your first table is the primary dataset, Inner when you only want confirmed matches, and Left Anti when you want to find missing or unmatched records.

Two Tables

Append Two Queries

Combine the rows from two queries into one result using Table.Combine.

January + February

Table.Combine({
    January,
    February
})

Multiple Tables

Append Several Queries

Pass several tables to Table.Combine when you need to consolidate more than two datasets.

Quarterly append

Table.Combine({
    January,
    February,
    March
})

As New

Append Queries as New

Create a separate combined query while keeping the original source queries unchanged.

Home → Append Queries → Append Queries as New

Schema

Columns Are Matched by Name

Power Query aligns appended columns by their names. Columns missing from one table are added and filled with null for those rows.

Important: Column order does not need to be identical, but column names matter.

January

Date	Product	Sales
2026-01-05	Laptop	4200
2026-01-12	Monitor	2100

February

Date	Product	Sales
2026-02-03	Tablet	3500
2026-02-18	Keyboard	1260

Appended Result

Date	Product	Sales
2026-01-05	Laptop	4200
2026-01-12	Monitor	2100
2026-02-03	Tablet	3500
2026-02-18	Keyboard	1260

Append vs. Merge

Two different ways to combine data

Append Queries Stacks Rows

Use when tables contain the same type of records and should become one longer dataset.

January + February + March

Merge Queries Adds Columns

Use when two tables are related by a key and fields from one should be added to rows in the other.

Sales + Customer lookup

Mismatched Columns

What happens when column structures differ?

Power Query creates a combined set of column names. If a source table does not contain one of those columns, its rows receive null in that field.

January Date Product Sales

February Date Product Sales Region

Result Date Product Sales Region

Practical Example

Combine monthly sales queries

This example combines three monthly tables, assigns appropriate data types, and produces one consolidated sales dataset.

Monthly consolidation

let
    January =
        Excel.CurrentWorkbook(){[Name="January"]}[Content],

    February =
        Excel.CurrentWorkbook(){[Name="February"]}[Content],

    March =
        Excel.CurrentWorkbook(){[Name="March"]}[Content],

    #"Appended Sales" =
        Table.Combine({
            January,
            February,
            March
        }),

    #"Changed Type" =
        Table.TransformColumnTypes(
            #"Appended Sales",
            {
                {"Date", type date},
                {"Product", type text},
                {"Sales", type number}
            }
        )
in
    #"Changed Type"

Best practice: standardize column names and data types before appending. For recurring files with the same structure, consider importing from a folder instead of manually adding a new query every month.

Conditional

Add a Conditional Column

Create categories with familiar if/then logic using values from one or more existing columns.

Sales category

Table.AddColumn(
    Source,
    "Sales Category",
    each if [Sales] >= 5000 then "High"
         else if [Sales] >= 2000 then "Medium"
         else "Low",
    type text
)

Custom

Add a Custom Column

Write an M expression when the result requires calculations, text functions, date logic, or multiple source columns.

Calculate line total

Table.AddColumn(
    Source,
    "Total",
    each [Quantity] * [Price],
    type number
)

Text

Build Text from Multiple Columns

Combine fields into a new text value using the & operator or text functions.

Full customer label

Table.AddColumn(
    Source,
    "Customer Label",
    each [Customer] & " - " & [Region],
    type text
)

Date

Create a Year Column

Extract parts of dates into new fields for grouping, filtering, or reporting.

Extract year

Table.AddColumn(
    Source,
    "Year",
    each Date.Year([OrderDate]),
    Int64.Type
)

Conditional vs. Custom

Which column type should you use?

Conditional Column Best for simple rules

Use the visual interface for conditions such as greater than, equals, contains, or category-based logic.

Example: Sales ≥ 5000 → High

Custom Column Best for flexible expressions

Write M directly when calculations or transformations go beyond the options available in the Conditional Column dialog.

Example: [Quantity] * [Price]

Expression Quick Reference

Common custom column patterns

Task	Expression	Result
Multiply columns	`[Quantity] * [Price]`	Line total
Add columns	`[Sales] + [Tax]`	Total amount
Conditional value	`if [Sales] > 1000 then "Yes" else "No"`	Rule-based label
Combine text	`[FirstName] & " " & [LastName]`	Full name
Extract year	`Date.Year([OrderDate])`	Year number
Extract month name	`Date.MonthName([OrderDate])`	Month text
Text length	`Text.Length([ProductCode])`	Character count
Check null	`if [Email] = null then "Missing" else "Available"`	Null status

Conditional Logic

Build multiple conditions with if, then, and else

M uses lowercase if, then, and else. Every conditional expression must return a value for both the true and false paths.

Multiple conditions

if [Sales] >= 5000 then
    "High"
else if [Sales] >= 2000 then
    "Medium"
else
    "Low"

Common Operators

Operators used in custom expressions

= Equal

<> Not equal

> Greater than

< Less than

>= Greater or equal

<= Less or equal

and Both conditions

or Either condition

not Reverse condition

& Combine text

Practical Example

Add revenue and performance columns

This query calculates revenue from quantity and unit price, then creates a performance category based on the calculated result.

Calculated + conditional columns

let
    Source =
        Excel.CurrentWorkbook(){[Name="Orders"]}[Content],

    #"Added Revenue" =
        Table.AddColumn(
            Source,
            "Revenue",
            each [Quantity] * [UnitPrice],
            type number
        ),

    #"Added Performance" =
        Table.AddColumn(
            #"Added Revenue",
            "Performance",
            each
                if [Revenue] >= 5000 then "High"
                else if [Revenue] >= 2000 then "Medium"
                else "Low",
            type text
        )
in
    #"Added Performance"

Best practice: give custom columns meaningful names and assign an explicit data type when possible. If the same logic is reused many times, consider moving it into a reusable function or parameterized query later.

File Path

Store a File Location

Instead of hard-coding the same path inside multiple queries, store it once in a parameter and reference that parameter wherever needed.

Parameter value

"C:\Data\Sales\"

Date Filter

Control a Date Range

Use date parameters to change reporting periods without rewriting the filtering logic inside the query.

Filter with StartDate

Table.SelectRows(
    Source,
    each [OrderDate] >= StartDate
)

Environment

Switch Between Sources

Parameters can help a query switch between development, testing, and production sources without changing the transformation steps.

Environment switch

if Environment = "Production" then
    ProductionPath
else
    TestPath

Reusable

Reference a Parameter Directly

A parameter behaves like a named value in M. Reference its name directly inside another query or expression.

Use parameter in File.Contents

File.Contents(
    SalesFilePath
)

Create a Parameter

Manage Parameters in Power Query Editor

Parameters are created from the Power Query Editor. Give each parameter a meaningful name, select its data type, and define the value that queries should use.

01 Open Power Query Editor

Open any existing query or create a new one.

02 Open Manage Parameters

Go to Home → Manage Parameters → New Parameter.

03 Name the Parameter

Use a clear name such as StartDate or SalesFolder.

04 Select a Data Type

Choose Text, Date, Decimal Number, Whole Number, or another type.

05 Set the Current Value

Enter the value Power Query should use.

Common Parameter Uses

Values worth turning into parameters

Parameter	Example Value	Typical Use
StartDate	`#date(2026, 1, 1)`	Filter records from a chosen starting date
EndDate	`#date(2026, 12, 31)`	Limit records to a reporting period
SalesFolder	`"C:\Data\Sales\"`	Control a folder-based import
SalesFile	`"sales.csv"`	Change a source filename
RegionFilter	`"North"`	Filter a query to one region
MinimumSales	`1000`	Control a numeric filter threshold
Environment	`"Production"`	Switch between different data sources

Practical Example

Filter sales using StartDate and MinimumSales

Instead of editing the query whenever the reporting period or sales threshold changes, store those values in parameters and reference them in the filter step.

Parameter-driven filter

let
    Source =
        Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {
                {"OrderDate", type date},
                {"Sales", type number}
            }
        ),

    #"Filtered Rows" =
        Table.SelectRows(
            #"Changed Type",
            each [OrderDate] >= StartDate
                and [Sales] >= MinimumSales
        )
in
    #"Filtered Rows"

Dynamic File Path

Build a source path from parameters

Parameters can be combined with normal M expressions. This is useful when a folder stays the same but the source filename changes.

Folder + filename

let
    FilePath =
        SalesFolder & SalesFile,

    Source =
        Csv.Document(
            File.Contents(FilePath),
            [
                Delimiter = ",",
                Encoding = 65001,
                QuoteStyle = QuoteStyle.Csv
            ]
        )
in
    Source

Use descriptive names. StartDate is easier to understand than Parameter1.

Set the correct type. A date parameter should use the Date type rather than storing the date as ordinary text.

Avoid unnecessary parameters. Parameterize values that users or workflows are likely to change, rather than every constant in the query.

Why parameters matter: a parameter separates a value that may change from the transformation logic that should stay stable. That makes queries easier to reuse and reduces the need to edit M code manually.

Core Structure

Most Power Query scripts use let and in

The let block defines named steps. The in statement tells Power Query which step should be returned as the final result.

Basic M query

let
    Source =
        Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    #"Filtered Rows" =
        Table.SelectRows(
            Source,
            each [Sales] > 1000
        )
in
    #"Filtered Rows"

let

Define Query Steps

Everything between let and in defines values or transformation steps.

let

Step

Name Each Result

A step name is assigned with the = operator and can be referenced by later steps.

Source = ...

Comma

Separate Steps

Query steps inside a let expression are separated by commas.

Source = ...,

Return the Final Value

The expression after in determines what the query ultimately returns.

in Result

Query Anatomy

Read an M query step by step

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {
                {"Date", type date},
                {"Sales", type number}
            }
        ),

    #"Filtered Rows" =
        Table.SelectRows(
            #"Changed Type",
            each [Sales] > 1000
        )
in
    #"Filtered Rows"

Source imports the original table.

Changed Type uses the previous Source step as its input.

Filtered Rows receives the result of #"Changed Type".

in returns the final #"Filtered Rows" table.

M Syntax Quick Reference

Common language elements

Syntax	Meaning	Example
`let`	Starts a sequence of named expressions	`let Source = ...`
`in`	Returns the selected expression	`in Result`
`=`	Assigns a value to a name	`Total = 100`
`,`	Separates expressions in a let block	`Source = ..., Next = ...`
`[Column]`	References a field or column value in row context	`[Sales]`
`{0}`	Accesses an item by zero-based position	`MyList{0}`
`"Text"`	Creates a text value	`"North"`
`#date(...)`	Creates a date value	`#date(2026, 9, 8)`
`null`	Represents the absence of a value	`[Email] = null`
`each`	Shorthand for a single-argument anonymous function	`each [Sales] > 1000`

Tables

Table Values

Most Power Query transformations receive a table and return another table. Functions such as Table.SelectRows and Table.RemoveColumns follow this pattern.

Lists

List Values

Lists are ordered collections enclosed in braces. Many aggregation functions work with lists.

{10, 20, 30}

Records

Record Values

Records contain named fields enclosed in square brackets.

[Region = "North", Sales = 4200]

Functions

Function Calls

Functions receive arguments inside parentheses and return a value.

Text.Upper("excel")

Understanding each

A compact way to write row-based functions

The each keyword is shorthand for a function with one unnamed argument. Inside that expression, the underscore _ represents the current value passed to the function.

Using each each [Sales] > 1000

Equivalent function (_) => _[Sales] > 1000

Step Names

Why some names use #” ”

Simple identifiers can be referenced directly. Step names containing spaces or certain special characters are commonly written as quoted identifiers using #"...".

Simple Name Source

Name with Spaces #"Changed Type"

Another Example #"Removed Columns"

Practical Example

Build a complete transformation pipeline

This query imports an Excel table, assigns data types, filters rows, adds a calculated column, and returns the final transformed table.

Complete M example

let
    Source =
        Excel.CurrentWorkbook(){[Name="Orders"]}[Content],

    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {
                {"OrderDate", type date},
                {"Quantity", Int64.Type},
                {"UnitPrice", type number}
            }
        ),

    #"Filtered Rows" =
        Table.SelectRows(
            #"Changed Type",
            each [Quantity] > 0
        ),

    #"Added Revenue" =
        Table.AddColumn(
            #"Filtered Rows",
            "Revenue",
            each [Quantity] * [UnitPrice],
            type number
        )
in
    #"Added Revenue"

Best practice: let Power Query generate M for you first, then inspect the Formula Bar or Advanced Editor to learn how each interface action translates into code. Small manual edits are usually easier and safer than writing a complex query entirely from scratch.

For calculations performed directly in worksheet cells, see our Excel Formulas Cheat Sheet.

Table Transform tables

Filter, add, remove, group, join, append, pivot, and reshape rows.

Text Clean text

Trim, replace, split, format, and extract text values.

Date Work with dates

Extract years, months, days, and create date-based transformations.

List Aggregate values

Sum, count, average, find minimums, maximums, and transform lists.

Quick Reference

Common Power Query M functions

Function	Purpose	Example
`Table.SelectRows`	Filter rows using a condition	`Table.SelectRows(Source, each [Sales] > 1000)`
`Table.AddColumn`	Create a calculated or custom column	`Table.AddColumn(Source, "Total", each [Qty] * [Price])`
`Table.RemoveColumns`	Remove unwanted columns	`Table.RemoveColumns(Source, {"Notes", "Temp"})`
`Table.RenameColumns`	Rename one or more columns	`Table.RenameColumns(Source, {{"Old", "New"}})`
`Table.TransformColumnTypes`	Assign or change data types	`Table.TransformColumnTypes(Source, {{"Sales", type number}})`
`Table.Distinct`	Remove duplicate rows or keys	`Table.Distinct(Source, {"CustomerID"})`
`Table.ReplaceValue`	Replace values in selected columns	`Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceValue, {"Status"})`
`Table.FillDown`	Fill null values from the row above	`Table.FillDown(Source, {"Region"})`
`Table.Group`	Group rows and calculate aggregates	`Table.Group(Source, {"Region"}, {{"Total", each List.Sum([Sales])}})`
`Table.NestedJoin`	Merge two tables using matching keys	`Table.NestedJoin(A, {"ID"}, B, {"ID"}, "Match", JoinKind.LeftOuter)`
`Table.Combine`	Append multiple tables	`Table.Combine({January, February, March})`
`Table.UnpivotOtherColumns`	Convert wide data to attribute/value rows	`Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")`

Text Functions

Clean and transform text

Text.Trim Remove leading and trailing spaces

Text.Clean Remove control characters

Text.Upper Convert text to uppercase

Text.Lower Convert text to lowercase

Text.Proper Capitalize each word

Text.Length Count characters

Text.Start Return characters from the beginning

Text.End Return characters from the end

Text.Contains Check whether text contains a value

Text.Replace Replace part of a text value

Date Functions

Extract and transform dates

Date.Year Return the year number

Date.Month Return the month number

Date.MonthName Return the month name

Date.Day Return the day of month

Date.DayOfWeek Return the weekday number

Date.StartOfMonth Return the first day of the month

Date.EndOfMonth Return the last day of the month

Date.AddDays Add or subtract days

Date.AddMonths Add or subtract months

Date.ToText Format a date as text

List Functions

Aggregate collections of values

List.Sum Sum numeric values

List.Average Calculate an average

List.Count Count list items

List.Min Return the smallest value

List.Max Return the largest value

List.Distinct Return unique values

List.Sort Sort list values

List.Contains Check whether a value exists

List.Transform Apply a function to each item

List.Select Keep items matching a condition

Number Functions

Work with numeric values

Number.Round Round to a chosen number of decimals

Number.RoundDown Round toward a lower value

Number.RoundUp Round toward a higher value

Number.Abs Return the absolute value

Number.Mod Return the remainder after division

Number.Power Raise a number to a power

Number.Sqrt Return the square root

Practical Function Patterns

Useful expressions you can reuse

Trim a text column

Text.Trim

Table.TransformColumns(
    Source,
    {
        {"Customer", Text.Trim, type text}
    }
)

Extract month name

Date.MonthName

Table.AddColumn(
    Source,
    "Month",
    each Date.MonthName([OrderDate]),
    type text
)

Round a calculated value

Number.Round

Table.AddColumn(
    Source,
    "Rounded Sales",
    each Number.Round([Sales], 2),
    type number
)

Check for text

Text.Contains

Table.SelectRows(
    Source,
    each Text.Contains(
        [Product],
        "Pro"
    )
)

Combined Example

Use multiple M functions in one query

Real Power Query workflows usually combine several functions. This example cleans text, assigns data types, filters records, creates a month field, and calculates rounded revenue.

Transformation pipeline

let
    Source =
        Excel.CurrentWorkbook(){[Name="Orders"]}[Content],

    #"Cleaned Customer" =
        Table.TransformColumns(
            Source,
            {
                {"Customer", Text.Trim, type text}
            }
        ),

    #"Changed Type" =
        Table.TransformColumnTypes(
            #"Cleaned Customer",
            {
                {"OrderDate", type date},
                {"Quantity", Int64.Type},
                {"Price", type number}
            }
        ),

    #"Filtered Rows" =
        Table.SelectRows(
            #"Changed Type",
            each [Quantity] > 0
        ),

    #"Added Month" =
        Table.AddColumn(
            #"Filtered Rows",
            "Month",
            each Date.MonthName([OrderDate]),
            type text
        ),

    #"Added Revenue" =
        Table.AddColumn(
            #"Added Month",
            "Revenue",
            each Number.Round(
                [Quantity] * [Price],
                2
            ),
            type number
        )
in
    #"Added Revenue"

Remember: Power Query M functions are not Excel worksheet formulas. A worksheet function such as =YEAR(A2) runs inside a cell, while an M expression such as Date.Year([OrderDate]) runs during the Power Query transformation process.

Refresh

Refresh a Query

Refreshing reconnects to the data source, retrieves current data, and reruns the transformation steps in the query.

Data → Refresh All

Connection

Source Location Matters

If a file is moved, renamed, deleted, or becomes inaccessible, the query may fail until the source path or credentials are updated.

Credentials

Authentication Can Expire

Web, database, organizational, and cloud sources may require stored credentials or permissions before Power Query can refresh them.

Transformations

Steps Run Again

Power Query does not simply copy new rows into the output. It repeats the complete transformation pipeline using the latest source data.

Refresh Workflow

What happens when a query refreshes?

01 Connect

Power Query reconnects to the configured source.

02 Retrieve

Current data is read from the source.

03 Transform

Applied Steps run again in sequence.

04 Load

The transformed result updates its destination.

Common Data Sources

Sources frequently used with Excel Power Query

Excel Workbook Tables, named ranges, sheets, and workbook data

Text / CSV Delimited files such as CSV and text exports

Folder Combine multiple similarly structured files

Web Supported web pages, files, feeds, and endpoints

Database SQL Server and other supported database systems

SharePoint Files, folders, and supported organizational data

OData Feed Structured data exposed through OData services

Other Connectors Connector availability depends on Excel version and platform

Query Properties

Control when connections refresh

Depending on the connection and Excel version, query properties may offer options such as refreshing when the workbook opens or refreshing at a chosen interval.

Refresh on Open Update the connection when the workbook is opened.

Background Refresh Allow supported connections to refresh while you continue working.

Refresh Interval Some connection types support periodic refresh while the workbook remains open.

Source Maintenance

Common refresh problems

Problem	Likely Cause	What to Check
File not found	Source file was moved or renamed	Check the path in the Source step
Access denied	Missing permission or invalid credentials	Review Data Source Settings and account access
Column not found	Source structure changed	Check renamed, removed, or newly added columns
Data type error	New source values do not match the expected type	Inspect Changed Type and error rows
Unexpected row count	Filters, joins, duplicates, or source changes	Review Applied Steps in order
Slow refresh	Large source or expensive transformations	Reduce unnecessary rows, columns, sorts, and repeated steps

Source Step

The connection usually begins in Source

The first query step commonly defines where the data comes from. Later transformations depend on the table or value returned by that source expression.

CSV source example

let
    Source =
        Csv.Document(
            File.Contents(
                "C:\Data\sales.csv"
            ),
            [
                Delimiter = ",",
                Encoding = 65001,
                QuoteStyle = QuoteStyle.Csv
            ]
        )
in
    Source

More Reliable Sources

Avoid fragile hard-coded workflows

Queries are easier to maintain when source locations and structures remain predictable. Parameters, folder imports, named Excel tables, and stable database objects can reduce manual changes.

Single changing file Use a parameter

Store the path or filename when it may need to change.

Recurring files Use a folder source

Useful for monthly exports with the same structure.

Workbook data Use Excel tables

Structured tables are more reliable than arbitrary worksheet ranges.

Best practice: test a query with updated source data before relying on it as a recurring workflow. A query that works once can still fail later if filenames, columns, data types, credentials, or source structures change.

Worksheet

Load to an Excel Table

Load the transformed result into a worksheet when users need to view, filter, reference, or work directly with the output.

Home → Close & Load

Data Model

Load to the Data Model

Use the Data Model when the result will support relationships, PivotTables, or analysis across multiple related tables.

Connection Only

Create Only a Connection

Keep helper or staging queries available without placing their results on worksheets.

Load To…

Choose the Destination

Use Close & Load To… when you need more control over where and how the query result is loaded.

Load Options

Choose how the query result should be stored

01 Table

Load the result as an Excel table in a new or existing worksheet.

02 Only Create Connection

Keep the query available for other queries without creating a visible worksheet table.

03 Add to Data Model

Store the result in Excel’s Data Model for relational analysis.

Which Load Option?

Worksheet vs. Connection vs. Data Model

Destination	Best For	Visible on Sheet?	Typical Use
Excel Table	Direct worksheet use	Yes	Cleaned exports, reports, lookup tables
Connection Only	Intermediate queries	No	Staging, merge sources, reusable helper queries
Data Model	Relational analysis	No direct table required	PivotTables, related datasets, larger analytical models

Loading Workflow

From Power Query Editor to Excel

Once the query is ready, choose how the result should be loaded. The query remains connected to the transformation steps, so refreshing it later updates the loaded result.

01 Finish Transformations

Check columns, data types, filters, and query steps.

02 Choose Close & Load

Use the default load or open Close & Load To…

03 Select Destination

Choose worksheet table, connection only, or Data Model.

04 Refresh Later

Refreshing reruns the query and updates the loaded output.

Staging Queries

Keep helper queries as connections

Not every query needs a visible table. A common Power Query design is to keep source and intermediate queries as connections only, then load only the final reporting query.

Source Query Connection Only

Cleaning Query Connection Only

Final Query Load to Worksheet

Practical Example

Monthly sales reporting workflow

Imagine three raw queries for January, February, and March. Each source query can remain connection-only. An appended final query combines the months and loads only the finished sales table to Excel.

January Connection Only

February Connection Only

March Connection Only

All Sales Load to Excel Table

Existing vs. New Worksheet

Choose where a table appears

When loading a query to a worksheet table, Excel can place the output in a new worksheet or at a selected location in an existing worksheet.

New Worksheet

A simple choice that keeps the query output separate from existing worksheet content.

Existing Worksheet

Useful when the output must appear at a specific location in an established report layout.

Important: avoid typing important manual data directly inside a table that Power Query controls. A refresh can replace the query output. Keep manual inputs in a separate source table and combine them through Power Query when necessary.

Best practice: load only the results users actually need. Keeping source and intermediate queries as connection-only can reduce worksheet clutter and make complex Power Query workbooks easier to understand.

Expression.Error

Column Was Not Found

This often happens when a source column was renamed, removed, or its spelling changed after the query was created.

Typical message The column 'Sales' of the table wasn't found.

Check: source column names and the first Applied Step where the error appears.

DataFormat.Error

Invalid Data Type

A value cannot be converted to the expected type, such as text inside a column that Power Query is trying to convert to a number or date.

Typical cause "N/A" → type number

Check: Changed Type steps and unexpected source values.

Formula.Firewall

Privacy or Source Combination Issue

Power Query can block certain combinations of data sources when privacy settings prevent data from being passed safely between them.

Check: Data Source Settings, privacy levels, and how the queries reference each other.

Source.Error

File or Source Is Unavailable

The file may have moved, the server may be unavailable, a network path may not be accessible, or your permissions may have changed.

Check: the Source step, file path, server name, network access, and credentials.

Error Reference

Frequent Power Query problems and fixes

Problem	Likely Cause	Recommended Fix
Column not found	Column renamed or removed	Update the affected Applied Step or restore the source column
Cannot convert value	Wrong data type or mixed values	Clean values before changing the column type
File not found	File path changed	Update the Source step or path parameter
Access denied	Expired or missing credentials	Review Data Source Settings and sign in again
Key didn’t match any rows	Expected sheet, table, or named object is missing	Check the object name used in the navigation step
Token expected	M syntax error	Check commas, brackets, parentheses, quotes, and step names
Unknown identifier	Incorrect step or variable name	Check spelling and quoted identifiers such as #”Changed Type”
Duplicate rows after merge	Multiple matches exist in the joined table	Check key uniqueness before expanding merged columns
Unexpected null values	Missing matches or absent source data	Inspect joins, append schemas, and source completeness
Refresh is slow	Large data or expensive transformations	Reduce unnecessary rows, columns, sorts, and repeated steps

Debugging Workflow

Find the first broken step

Power Query transformations run in sequence. When one step fails, later steps often fail too. Start with the first Applied Step showing an error rather than trying to repair the final output immediately.

01 Find the Error

Locate the first Applied Step that displays the problem.

02 Inspect Previous Step

Confirm the input table still contains the expected structure.

03 Read the Formula Bar

Check which columns, steps, values, or functions are referenced.

04 Fix and Recheck

Correct the step, then verify all later steps again.

Data Type Errors

Clean before converting

Type conversion is one of the most common sources of Power Query errors. A numeric column may contain values such as N/A, -, or empty text that must be cleaned first.

Replace invalid text before conversion

let
    Source =
        Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    #"Replaced N/A" =
        Table.ReplaceValue(
            Source,
            "N/A",
            null,
            Replacer.ReplaceValue,
            {"Sales"}
        ),

    #"Changed Type" =
        Table.TransformColumnTypes(
            #"Replaced N/A",
            {
                {"Sales", type number}
            }
        )
in
    #"Changed Type"

Missing Values

null is different from blank text

Power Query distinguishes between null, an empty text string "", and text that only contains spaces. Cleaning logic should account for the type of missing value actually present.

Null null

No value exists.

Empty Text ""

A text value exists but contains zero characters.

Whitespace " "

The value contains spaces and may need Text.Trim.

Error Handling in M

Use try … otherwise

The try expression lets you handle expected errors in M without stopping the entire transformation.

Safe conversion

Table.AddColumn(
    Source,
    "Safe Number",
    each
        try Number.From([Value])
        otherwise null,
    type number
)

Merge Problems

Check keys before blaming the join

Unexpected merge results often come from differences in key values, not from the join type itself.

Same Data Type

Both key columns should use compatible data types.

Trim Text

Remove leading and trailing spaces before matching text keys.

Check Duplicates

Duplicate keys in the lookup table can multiply rows after expansion.

Check Nulls

Missing keys cannot produce a normal matching row.

Do not remove errors blindly: removing error rows can hide a real source-quality problem. First identify why the error exists, then decide whether to correct, replace, keep, or remove the affected rows.

Best debugging habit: inspect Applied Steps from top to bottom. The first step that breaks is usually far more useful than the final error message.

Example 1

Clean a Sales Export

Remove unnecessary columns, fix data types, clean customer names, remove invalid rows, and calculate revenue.

Import → Clean → Filter → Calculate

Example 2

Combine Monthly Files

Import recurring files from a folder, normalize their structure, append them, and create one refreshable reporting table.

Folder → Transform → Append → Load

Example 3

Merge Sales with Customer Data

Join a transaction table with a customer lookup table and expand only the fields needed for reporting.

Sales + Customers → Merge

Example 4

Unpivot a Monthly Report

Convert January, February, March, and other month columns into a normalized Month and Sales structure that is easier to analyze.

Wide Data → Unpivot → Long Data

Example 1

Clean and prepare a sales export

A typical sales export may contain extra columns, inconsistent text, invalid quantities, and raw fields that need to be converted before reporting.

01 Remove unused columns

Discard temporary notes and internal fields.

02 Assign data types

Convert dates, quantities, and prices.

03 Clean text

Trim inconsistent customer names.

04 Filter invalid rows

Keep records with valid quantities.

05 Add revenue

Calculate Quantity × Price.

Sales cleaning pipeline

let
    Source =
        Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    #"Removed Columns" =
        Table.RemoveColumns(
            Source,
            {"Notes", "Temp"}
        ),

    #"Changed Type" =
        Table.TransformColumnTypes(
            #"Removed Columns",
            {
                {"OrderDate", type date},
                {"Quantity", Int64.Type},
                {"Price", type number}
            }
        ),

    #"Cleaned Customer" =
        Table.TransformColumns(
            #"Changed Type",
            {
                {"Customer", Text.Trim, type text}
            }
        ),

    #"Filtered Rows" =
        Table.SelectRows(
            #"Cleaned Customer",
            each [Quantity] > 0
        ),

    #"Added Revenue" =
        Table.AddColumn(
            #"Filtered Rows",
            "Revenue",
            each [Quantity] * [Price],
            type number
        )
in
    #"Added Revenue"

Example 2

Combine recurring monthly files

Folder imports are useful when new files arrive regularly with the same basic structure. Power Query can combine them into one dataset that refreshes when additional files are added.

sales-jan.csv January

sales-feb.csv February

sales-mar.csv March

↓

Combined Sales Query One refreshable table

Folder source

let
    Source =
        Folder.Files(
            "C:\Data\Monthly Sales"
        ),

    #"CSV Files Only" =
        Table.SelectRows(
            Source,
            each [Extension] = ".csv"
        )
in
    #"CSV Files Only"

Power Query’s Combine Files workflow can then apply the same transformation logic to each file and append the results.

Example 3

Merge transactions with customer details

A transaction table might contain only a CustomerID. Merge it with a customer table to bring in fields such as customer name, region, or segment.

Sales CustomerID OrderDate Sales

Customers CustomerID Name Region

Combined Result CustomerID Name Region Sales

Merge and expand

let
    Merged =
        Table.NestedJoin(
            Sales,
            {"CustomerID"},
            Customers,
            {"CustomerID"},
            "Customer",
            JoinKind.LeftOuter
        ),

    Expanded =
        Table.ExpandTableColumn(
            Merged,
            "Customer",
            {"Name", "Region"},
            {"Customer Name", "Region"}
        )
in
    Expanded

Example 4

Unpivot a cross-tab report

Monthly reports often store each month in a separate column. Unpivoting converts those columns into rows, creating a cleaner structure for PivotTables, charts, and analysis.

Before

Product	Jan	Feb	Mar
A	120	150	170
B	90	110	140

After

Product	Month	Sales
A	Jan	120
A	Feb	150
A	Mar	170

Dynamic unpivot

Table.UnpivotOtherColumns(
    Source,
    {"Product"},
    "Month",
    "Sales"
)

Example 5

Group sales by region

Use Group By when you need one row per category with aggregated measures such as total sales, average order value, or transaction count.

Multiple aggregations

Table.Group(
    Source,
    {"Region"},
    {
        {
            "Total Sales",
            each List.Sum([Sales]),
            type number
        },
        {
            "Average Sale",
            each List.Average([Sales]),
            type number
        },
        {
            "Transactions",
            each Table.RowCount(_),
            Int64.Type
        }
    }
)

Example 6

Create reusable date and category columns

Add calculated fields during the transformation process so downstream Excel reports receive analysis-ready columns.

Add analysis columns

let
    #"Added Year" =
        Table.AddColumn(
            Source,
            "Year",
            each Date.Year([OrderDate]),
            Int64.Type
        ),

    #"Added Month" =
        Table.AddColumn(
            #"Added Year",
            "Month",
            each Date.MonthName([OrderDate]),
            type text
        ),

    #"Added Category" =
        Table.AddColumn(
            #"Added Month",
            "Sales Category",
            each
                if [Sales] >= 5000 then "High"
                else if [Sales] >= 2000 then "Medium"
                else "Low",
            type text
        )
in
    #"Added Category"

Reusable Pattern

A practical transformation order

There is no single correct order for every query, but this sequence is a useful starting point for many Excel data-cleaning workflows.

1 Connect

→

2 Remove Noise

→

3 Set Types

→

4 Clean Values

→

5 Combine

→

6 Calculate

→

7 Load

Think in repeatable steps: a good Power Query workflow should work not only with today’s dataset, but also when the same source is refreshed tomorrow with new rows and updated values.

Reduce Data

Remove Unneeded Rows Early

Filter out records you do not need as early as practical so later transformation steps process less data.

Reduce Width

Remove Unused Columns Early

Keeping only necessary columns can reduce query complexity and make the transformation pipeline easier to understand.

Structure

Use Clear Step Names

Rename important Applied Steps so future users can understand what each transformation does without reading the full M expression.

Maintainability

Keep Sources Predictable

Stable filenames, table names, column names, parameters, and schemas make recurring refreshes much more reliable.

High-Impact Practices

The most useful habits for reliable queries

01 Filter early

Reduce the number of rows before expensive transformations whenever the business logic allows it.

02 Keep only required columns

Avoid carrying unused fields through every later step.

03 Set data types intentionally

Correct types reduce unexpected comparisons, calculations, and refresh errors.

04 Avoid unnecessary sorting

Sort only when row order is genuinely needed by a later operation or final output.

05 Reuse queries

Reference existing cleaned queries instead of repeatedly rebuilding the same transformation logic.

06 Load only final outputs

Keep staging and helper queries connection-only when users do not need their results on worksheets.

Query Design

Prefer a focused transformation pipeline

A query is easier to maintain when every step has a clear purpose. Avoid adding temporary transformations that are immediately reversed or repeatedly recalculating the same result.

Less Efficient

Import 25 columns

↓

Sort all rows

↓

Add temporary columns

↓

Filter unwanted rows

↓

Remove 18 columns

Better Pattern

Import source

↓

Keep 7 required columns

↓

Filter required rows

↓

Transform values

↓

Load final result

Query Folding

Let the source do work when possible

With supported sources, Power Query may translate compatible transformations into operations that run directly at the source. This is called query folding.

Query folding is especially important with large database sources, because filtering and selecting columns at the source can reduce the amount of data transferred into Excel.

Database Millions of rows

→

Folded Filter Source performs filter

→

Power Query Only required rows returned

Performance Reference

Common choices that affect query performance

Practice	Why It Helps	Recommendation
Filter rows early	Reduces data processed by later steps	Apply meaningful filters near the beginning
Remove unused columns	Reduces table width and complexity	Keep only fields needed downstream
Avoid repeated sorts	Sorting can be expensive on large datasets	Sort only when required
Use references	Centralizes reusable cleaning logic	Create one clean base query and reference it
Use parameters	Separates changing values from query logic	Parameterize paths, dates, and reusable settings
Keep staging queries unloaded	Reduces worksheet clutter	Use connection-only for helper queries
Preserve query folding	Allows supported sources to perform transformations	Filter and reduce data before non-folding steps where practical
Avoid hard-coded fragile names	Source changes can break downstream steps	Use stable schemas and clear source contracts

Reuse Transformations

Reference a cleaned base query

If several outputs need the same cleaned source, create the cleaning logic once and build separate reference queries from it.

Base Query Clean Sales

Import + types + cleaning

Reference Regional Report

Reference Product Report

Reference Monthly Summary

Naming

Name queries and steps for humans

Clear names make debugging easier and reduce the chance of accidentally changing the wrong query or step.

Avoid Query1 Query2 Custom1

Prefer Sales_Raw Sales_Clean Sales_Report

Pre-Refresh Checklist

Before calling a Power Query workflow finished

✓

Source paths and credentials are valid.

✓

Only required rows and columns remain.

✓

Important columns use the correct data types.

✓

Merge keys have compatible types and clean values.

✓

Intermediate queries are loaded only when needed.

✓

Important Applied Steps have understandable names.

✓

The query has been tested with refreshed source data.

✓

Unexpected errors, nulls, and duplicate rows were reviewed.

Performance depends on the source: a technique that works well for a small Excel table may behave differently with millions of database rows. Always consider source size, connector behavior, query folding, network speed, and the transformations being used.

Best overall rule: keep the query as simple as possible, but no simpler. Remove unnecessary work while preserving transformations that make the output correct, understandable, and reliable when refreshed.

For broader advanced spreadsheet analysis, see our Advanced Excel & Google Sheets Cheat Sheet.

What is Power Query in Excel?

Power Query is Excel’s data import and transformation tool. It lets you connect to data sources, clean and reshape data, combine tables, and build repeatable transformation workflows that can be refreshed when the source data changes.

Where is Power Query in Excel?

In modern versions of Excel, most Power Query features are available from the Data tab through commands such as Get Data, From Table/Range, and the Queries & Connections pane.

Is Power Query the same as Excel formulas?

No. Excel worksheet formulas calculate values inside worksheet cells, while Power Query transforms data before it is loaded into Excel. Power Query is generally better suited to repeatable importing, cleaning, reshaping, and combining tasks.

Worksheet Formula =YEAR(A2)

Calculates inside an Excel cell.

Power Query M Date.Year([OrderDate])

Runs during the query transformation process.

What is Power Query M language?

M is the formula language used by Power Query. Actions performed in the Power Query Editor generate M expressions behind the scenes. You can inspect and edit them in the Formula Bar or Advanced Editor.

M is case-sensitive, so function and identifier capitalization can matter.

Do I need to learn M to use Power Query?

No. Many common transformations can be created entirely through the Power Query Editor. Learning basic M becomes useful when you need custom logic, reusable expressions, advanced transformations, or more control over generated steps.

What is the difference between Merge and Append in Power Query?

Merge combines tables horizontally by matching key columns, similar to a database join. Append combines tables vertically by stacking their rows.

Merge Table A + matching columns from Table B

Append Rows from Table A + rows from Table B

What is the difference between Merge Columns and Merge Queries?

Merge Columns combines values from multiple columns inside the same table, usually into one text column. Merge Queries joins two separate tables using one or more matching key columns.

What does Unpivot do in Power Query?

Unpivot converts multiple columns into attribute-and-value rows. It is especially useful when spreadsheet reports store categories such as months in separate columns and you need a normalized table for analysis.

Jan | Feb | Mar → Month | Sales

Does Power Query automatically update when source data changes?

Power Query does not continuously watch every source for changes. The transformation steps rerun when a refresh is triggered. Refresh behavior and available refresh settings depend on the source, connection, Excel version, and environment.

What does Refresh All do in Excel?

Refresh All tells Excel to update supported workbook connections and queries. For Power Query, this can reconnect to the source, retrieve current data, rerun the Applied Steps, and update the loaded result.

Can Power Query combine multiple Excel or CSV files?

Yes. A folder connection can be used to discover multiple files and apply a common transformation process to files with compatible structures. This is useful for recurring monthly or weekly exports.

Can Power Query remove duplicate rows?

Yes. Power Query can remove duplicates based on one or more selected columns. In M, the Table.Distinct function is commonly used for this task.

Remove duplicate Customer IDs

Table.Distinct(
    Source,
    {"CustomerID"}
)

Can Power Query replace Excel VBA?

Sometimes, but not completely. Power Query is excellent for importing, cleaning, reshaping, and combining data. VBA is a broader automation language that can control workbook actions, interfaces, formatting, events, and other Excel behavior. The right choice depends on the task.

Can Power Query handle large datasets?

Yes, but performance depends on the data source, workbook resources, transformations, connector behavior, and whether operations can be pushed back to the source through query folding.

Filtering unnecessary rows and removing unused columns early can help reduce the amount of data processed.

What is query folding?

Query folding occurs when Power Query translates supported transformation steps into operations that the external source can execute. This can improve performance because less data may need to be transferred into Power Query.

What is a connection-only query?

A connection-only query keeps the query available without loading its result into a worksheet table. This is useful for staging, intermediate transformations, and source queries that feed other queries.

Should I load every Power Query result to a worksheet?

No. Load the outputs users actually need. Intermediate or helper queries can often remain connection-only, while final reporting queries are loaded to worksheets or the Data Model.

Why does my Power Query refresh fail after a source file changes?

Refreshes can fail when files are moved, columns are renamed or removed, data types change, tables or sheets are renamed, credentials expire, or permissions are modified.

Inspect the Applied Steps from top to bottom and locate the first step that returns an error.

Are Power Query parameters secure for passwords or API keys?

Parameters are useful for reusable values such as paths, dates, and filters, but they should not be treated as a secure secret store. Avoid embedding passwords, private tokens, or sensitive API keys directly in parameters or M code.

When should I use Power Query instead of normal Excel formulas?

Use Power Query when the main task is importing, cleaning, restructuring, merging, appending, or repeatedly refreshing source data. Use worksheet formulas when the calculation belongs naturally inside the worksheet and should recalculate with cell values.

Quick Decision

Power Query or worksheet formula?

Use Power Query

Import recurring files

Clean inconsistent data

Merge or append tables

Unpivot reports

Create refreshable pipelines

Use Excel Formulas

Calculate directly in cells

Create worksheet-based logic

Reference nearby cell values

Build interactive sheet calculations

Recalculate with workbook changes

Simple rule: use Power Query to prepare the data and Excel formulas to calculate with the prepared data when that division makes the workbook easier to maintain.

Printable Reference

Excel Power Query Cheat Sheet

A compact printable companion to this guide, designed for quick reference while working in Excel Power Query.

✓

Power Query workflow reference

✓

Cleaning and transformation shortcuts

✓

Merge, append, pivot, and unpivot reference

✓

Essential Power Query M functions

✓

Common errors and troubleshooting

✓

Performance and best-practice checklist

PDF Coming Soon Back to Quick Reference

CHEATSHEETSILO.COM Power Query

Excel Power Query Cheat Sheet Transform · Clean · Combine · Refresh

IMPORT Excel CSV Folder

TRANSFORM Filter Clean Unpivot

COMBINE Merge Append Join

M LANGUAGE Table.* Text.* Date.*

QUICK M REFERENCE Table.SelectRows(...) Table.AddColumn(...) Table.NestedJoin(...)

How to Use It

Keep it beside Excel while you work

01 Download

Save the printable reference locally once the PDF is available.

02 Print

Use it as a desk reference for common Power Query tasks.

03 Reference

Quickly check transformations, M functions, and workflow patterns.

Tip: bookmark this web version as well. The online cheat sheet contains more examples and explanations than the compact printable PDF.

Official Documentation

Continue Learning Power Query

For detailed product documentation, connector information, advanced features, and the complete Power Query M language reference, visit the official Microsoft Power Query documentation .

Excel Power Query Cheat Sheet

Find a Power Query Task

Power Query Quick Reference

What Is Power Query?

Connect to Data

Clean & Transform

Combine Data

Load & Refresh

Manual data cleaning vs. Power Query

Cleaning a monthly sales export

Power Query Workflow

Connect to a Data Source

Clean & Transform

Merge or Append Data

Load the Result

Refresh When Data Changes

Power Query builds transformations step by step

Importing Data into Power Query

Excel Tables & Workbooks

CSV & Text Files

Multiple Files from a Folder

Databases

Web Data

Additional Connectors

From source to Power Query Editor

Which option should you choose?

Use when the data is already ready

Use when the data needs preparation

Power Query Editor

Queries Pane

Ribbon

Formula Bar

Data Preview

Query Settings

Applied Steps

Where common commands are located

Power Query Data Types

Check Types Early

IDs Are Often Text

Dates Must Be Real Dates

Watch Regional Formats

Change and convert data types

When dates or numbers use another regional format

Working with Columns & Rows

Remove Columns

Keep Selected Columns

Rename Columns

Reorder Columns

Keep Top Rows

Remove Top Rows

Keep Bottom Rows

Remove Duplicate Rows

Common column and row operations

Reduce a messy export to the fields you actually need

Filter & Sort Data

Filter Numeric Values

Filter Text Values

Filter Multiple Conditions

Filter Dates

Sort Ascending

Sort Descending

Common conditions in M

Sort by more than one column

Keep recent high-value orders and sort them by sales

Clean Data in Power Query

Remove Duplicate Rows

Remove Blank Rows

Filter Out Null Values

Remove Error Rows

Trim Extra Spaces

Remove Non-Printable Characters

Replace Values

Standardize Text Case

Common data-cleaning tasks

They are not the same thing

Clean customer data before a merge

Split, Merge & Extract Data

Split by Delimiter

Split by Number of Characters

Merge Columns