Home → Data Science & ML → Power Query - Complete M Language Guide

Power Query - Complete M Language Guide Cheat Sheet

Last Updated: November 21, 2025

M Language Basics

Concept Example
Let expression let
Source = Excel.CurrentWorkbook(),
Result = Source
in Result
Variables variableName = value
Comments // Single line comment
/* Multi-line
comment */
Step reference PreviousStepName
Text values "Text in quotes"
Number values 42 or 3.14
Logical values true or false
Null value null

Common Data Sources

Source M Code
Excel workbook Excel.Workbook(File.Contents("C:\path\file.xlsx"))
CSV file Csv.Document(File.Contents("C:\path\file.csv"))
Folder Folder.Files("C:\path\to\folder")
Web page Web.Page(Web.Contents("https://example.com"))
SQL Server Sql.Database("server", "database")
SharePoint folder SharePoint.Files("https://site.sharepoint.com/path")
Current workbook Excel.CurrentWorkbook(){[Name="TableName"]}[Content]
JSON Json.Document(File.Contents("C:\path\file.json"))

Column Transformations

Transformation M Code
Rename column Table.RenameColumns(Source, {{"OldName", "NewName"}})
Remove columns Table.RemoveColumns(Source, {"Column1", "Column2"})
Select columns Table.SelectColumns(Source, {"Column1", "Column2"})
Reorder columns Table.ReorderColumns(Source, {"Col3", "Col1", "Col2"})
Add custom column Table.AddColumn(Source, "NewCol", each [Col1] * 2)
Change type Table.TransformColumnTypes(Source, {{"Column1", type text}})
Duplicate column Table.DuplicateColumn(Source, "Column1", "Column1 - Copy")
Fill down Table.FillDown(Source, {"Column1"})
Fill up Table.FillUp(Source, {"Column1"})

Row Transformations

Transformation M Code
Filter rows Table.SelectRows(Source, each [Column1] > 100)
Remove top rows Table.Skip(Source, 5)
Keep top rows Table.FirstN(Source, 10)
Remove duplicates Table.Distinct(Source, {"Column1"})
Remove blank rows Table.SelectRows(Source, each [Column1] <> null)
Sort ascending Table.Sort(Source, {{"Column1", Order.Ascending}})
Sort descending Table.Sort(Source, {{"Column1", Order.Descending}})
Reverse rows Table.ReverseRows(Source)
Add index column Table.AddIndexColumn(Source, "Index", 1, 1)

Text Transformations

Function Example
Uppercase Text.Upper([Column1])
Lowercase Text.Lower([Column1])
Proper case Text.Proper([Column1])
Trim spaces Text.Trim([Column1])
Clean (remove non-printable) Text.Clean([Column1])
Replace text Text.Replace([Column1], "old", "new")
Extract length Text.Length([Column1])
Extract first N chars Text.Start([Column1], 5)
Extract last N chars Text.End([Column1], 3)
Extract middle Text.Middle([Column1], 2, 5)
Concatenate Text.Combine({[Col1], [Col2]}, " ")
Split by delimiter Text.Split([Column1], ",")

Number Transformations

Function Example
Round Number.Round([Column1], 2)
Round up Number.RoundUp([Column1])
Round down Number.RoundDown([Column1])
Absolute value Number.Abs([Column1])
Modulo Number.Mod([Column1], 10)
Power Number.Power([Column1], 2)
Square root Number.Sqrt([Column1])
Integer division Number.IntegerDivide([Col1], [Col2])
Convert to text Number.ToText([Column1])
From text Number.FromText([Column1])

Date & Time Functions

Function Example
Current date DateTime.LocalNow()
Current date only Date.From(DateTime.LocalNow())
Year Date.Year([DateColumn])
Month Date.Month([DateColumn])
Day Date.Day([DateColumn])
Day of week Date.DayOfWeek([DateColumn])
Week of year Date.WeekOfYear([DateColumn])
Add days Date.AddDays([DateColumn], 7)
Add months Date.AddMonths([DateColumn], 1)
Days between Duration.Days([EndDate] - [StartDate])
Start of month Date.StartOfMonth([DateColumn])
End of month Date.EndOfMonth([DateColumn])

Conditional Logic

Statement Syntax
If statement if [Column1] > 100 then "High" else "Low"
If-else if if [Column1] > 100 then "High"
else if [Column1] > 50 then "Medium"
else "Low"
And condition if [Col1] > 100 and [Col2] = "Yes" then "True" else "False"
Or condition if [Col1] > 100 or [Col2] = "Yes" then "True" else "False"
Not condition if not [Column1] = null then [Column1] else 0
Null check if [Column1] = null then 0 else [Column1]

Merge & Append Operations

Operation M Code
Append queries Table.Combine({Query1, Query2})
Merge (inner join) Table.NestedJoin(Table1, {"Key"}, Table2, {"Key"}, "NewColumn", JoinKind.Inner)
Left outer join Table.NestedJoin(Table1, {"Key"}, Table2, {"Key"}, "NewColumn", JoinKind.LeftOuter)
Right outer join Table.NestedJoin(Table1, {"Key"}, Table2, {"Key"}, "NewColumn", JoinKind.RightOuter)
Full outer join Table.NestedJoin(Table1, {"Key"}, Table2, {"Key"}, "NewColumn", JoinKind.FullOuter)
Expand merged column Table.ExpandTableColumn(Source, "NewColumn", {"Col1", "Col2"})

Pivot & Unpivot

Operation Purpose
Unpivot columns Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value")
Unpivot selected Table.Unpivot(Source, {"Col1", "Col2"}, "Attribute", "Value")
Pivot column Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Value", List.Sum)
Group by Table.Group(Source, {"Category"}, {{"Total", each List.Sum([Amount]), type number}})

Custom Functions

Example Code
Simple function (x) => x * 2
Multiple parameters (x, y) => x + y
Function with type (x as number) as number => x * 2
Named function let
MultiplyByTwo = (x) => x * 2
in MultiplyByTwo
Function in column Table.AddColumn(Source, "NewCol", each MyFunction([Column1]))

List Functions

Function Example
Create list {1, 2, 3, 4, 5}
List range {1..10}
List sum List.Sum({1, 2, 3, 4, 5})
List average List.Average({1, 2, 3, 4, 5})
List max List.Max({1, 2, 3, 4, 5})
List min List.Min({1, 2, 3, 4, 5})
List count List.Count({1, 2, 3, 4, 5})
List distinct List.Distinct({1, 2, 2, 3, 3})
List sort List.Sort({3, 1, 2})
List contains List.Contains({1, 2, 3}, 2)

Error Handling

Technique Code
Try-otherwise try [Column1] / [Column2] otherwise 0
Check for error if [Column1] is error then 0 else [Column1]
Remove errors Table.RemoveRowsWithErrors(Source, {"Column1"})
Replace errors Table.ReplaceErrorValues(Source, {{"Column1", 0}})
💡 Pro Tips:
  • Use "each" as shorthand for (x) => x when referencing current row
  • Reference column in current row with [ColumnName] inside each statement
  • Enable "Column quality" and "Column distribution" in View tab to spot data issues
  • Use Table.Buffer() to load entire table into memory for better performance on repeated references
  • Create parameters for dynamic file paths and connection strings
  • Use #shared to see all available M functions and their documentation
  • Right-click step in Applied Steps to see generated M code
  • Disable query load for intermediate queries to reduce Excel file size

Related Cheat Sheets

Browse all Data Science & ML →

← Back to Data Science & ML | Browse all categories | View all cheat sheets