How to Use M Language Power Query in Power BI: Simple Guide

Use M language in Power BI's Power Query editor to transform and shape your data by writing or editing queries. You can access the Advanced Editor to write M code that defines data import, transformation steps, and output. This lets you customize data processing beyond the graphical interface.

Syntax

The basic structure of an M query includes let and in keywords. let defines variables or steps, and in returns the final result. Each step is separated by commas and can reference previous steps.

m

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\Sales.xlsx")),
    SalesTable = Source{[Name="Sales"]}[Data],
    FilteredRows = Table.SelectRows(SalesTable, each [Amount] > 1000)
in
    FilteredRows

Example

This example loads an Excel file, selects a table named "Sales", and filters rows where the Amount is greater than 1000. It shows how to chain steps in M language to transform data.

m

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\Sales.xlsx")),
    SalesTable = Source{[Name="Sales"]}[Data],
    FilteredRows = Table.SelectRows(SalesTable, each [Amount] > 1000)
in
    FilteredRows

Output

A table showing only rows from the Sales table where the Amount column is greater than 1000.

Common Pitfalls

• Forgetting to use in to return the final step causes errors.
• Referencing steps before they are defined leads to errors.
• Incorrect syntax like missing commas or brackets breaks the query.
• File paths must be correct and accessible.

m

Wrong:
let
    Step1 = Table1
    Step2 = Table.SelectRows(Step1, each [Value] > 10)
in
    Step2

Right:
let
    Step1 = Table1,
    Step2 = Table.SelectRows(Step1, each [Value] > 10)
in
    Step2

Quick Reference

M Language Function	Purpose
let ... in	Defines steps and returns result
Table.SelectRows(table, condition)	Filters rows by condition
File.Contents(path)	Reads file content from path
Excel.Workbook(content)	Loads Excel workbook from content
Table.TransformColumns(table, transform)	Changes columns using a function

Key Takeaways

Use the let-in structure to define and return query steps in M language.

Access Power Query's Advanced Editor in Power BI to write or edit M code.

Chain transformation steps by referencing previous steps with commas.

Check syntax carefully to avoid errors like missing commas or wrong references.

Use built-in M functions to load, filter, and transform data efficiently.