How to Use Power Query Editor in Power BI: Step-by-Step Guide

Open Power Query Editor in Power BI by selecting Transform Data. Use it to clean and shape your data by applying steps like filtering, renaming columns, and merging tables visually without coding.

Syntax

The Power Query Editor uses a step-by-step approach to transform data. Each action you take creates a step recorded in the Applied Steps pane. The syntax behind these steps is called M language, which is functional and case-sensitive.

Basic syntax elements include:

• let ... in ...: Defines variables and returns a result.
• Source = ...: Loads data source.
• Table.TransformColumnTypes: Applies transformations to columns.

However, most users interact through the graphical interface, and the code updates automatically.

m

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\Sales.xlsx"), null, true),
    Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
    ChangedType = Table.TransformColumnTypes(Sales_Sheet,{{"Date", type date}, {"Amount", type number}})
in
    ChangedType

Example

This example shows how to load an Excel file, filter rows where sales amount is greater than 100, and rename a column.

m

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\Sales.xlsx"), null, true),
    Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
    ChangedType = Table.TransformColumnTypes(Sales_Sheet,{{"Date", type date}, {"Amount", type number}}),
    FilteredRows = Table.SelectRows(ChangedType, each [Amount] > 100),
    RenamedColumns = Table.RenameColumns(FilteredRows,{{"Amount", "Sales Amount"}})
in
    RenamedColumns

Output

A table showing only rows where Sales Amount > 100 with columns Date and Sales Amount renamed.

Common Pitfalls

Common mistakes when using Power Query Editor include:

• Not setting correct data types, which can cause errors in calculations.
• Applying transformations in the wrong order, leading to unexpected results.
• Forgetting to click Close & Apply to save changes back to Power BI.
• Using case-sensitive column names incorrectly in custom formulas.

Always preview your data after each step to catch issues early.

m

/* Wrong: Filtering before changing data type can cause errors */
let
    Source = Excel.Workbook(File.Contents("C:\\Data\\Sales.xlsx"), null, true),
    Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
    FilteredRows = Table.SelectRows(Sales_Sheet, each [Amount] > 100),
    ChangedType = Table.TransformColumnTypes(FilteredRows,{{"Amount", type number}})
in
    ChangedType

/* Right: Change data type before filtering */
let
    Source = Excel.Workbook(File.Contents("C:\\Data\\Sales.xlsx"), null, true),
    Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
    ChangedType = Table.TransformColumnTypes(Sales_Sheet,{{"Amount", type number}}),
    FilteredRows = Table.SelectRows(ChangedType, each [Amount] > 100)
in
    FilteredRows

Quick Reference

Here are some quick tips for using Power Query Editor effectively:

• Load Data: Use Home > Get Data to import sources.
• Transform Data: Use ribbon commands or right-click menus to filter, sort, and change types.
• Applied Steps: Review and reorder steps to control transformation flow.
• Advanced Editor: View or edit the M code directly for complex tasks.
• Close & Apply: Save changes and load data into Power BI model.

Key Takeaways

Open Power Query Editor via Transform Data to clean and shape your data visually.

Each transformation creates a step recorded in Applied Steps, editable in Advanced Editor.

Always set correct data types before filtering or calculations to avoid errors.

Use the ribbon and right-click menus for easy transformations without coding.

Remember to Close & Apply to save changes back to your Power BI report.