Skip to main content
HomeCheat sheetsPower BI

Data Transformation with Power Query M in Power BI

Learn how to transform data with Power Query M in Power BI in this convenient cheat sheet!
Jul 2023  · 9 min read

Have this cheat sheet at your fingertips

Download PDF

Definitions

Power Query is a tool for extract-transform-load (ETL). That is, it lets you import and prepare your data for use in Microsoft data platforms including Power BI, Excel, Azure Data Lake Storage and Dataverse.

Power Query Editor is the graphical user interface to Power Query.

Power Query M ("M" for short) is the functional programming language used in Power Query.

DAX is the other programming language available for Power BI. DAX is used for data analysis rather than ETL. Learn more about it in the DataCamp DAX cheat sheet.

An expression is a single formula that returns a value.

A query is a sequence of expressions used to define more complex data transformations. Queries are defined using let-in code blocks.

Accessing M in Power BI

M code can be seen in Power Query Editor. In the ribbon, click on 'Transform data' to open the Power Query Editor.

image1.png

M code is shown in the Formula Bar.

image3.png

M code can also be seen in the Advanced Editor window. Click 'Advanced Editor' to open the Advanced Editor window.

image4.png

Creating Values

// Define a number
999 

// Define a logical value
true 

// Define a text value
"DataCamp" 

// Define a null (missing value)
null 

// Define a date with #date()
#date(2023, 12, 31) 

// Define a datetime with #datetime()
#datetime(2022, 9, 8, 15, 10, 0)

Variables

// Variables are assigned by writing a query with let-in
let
  // Intermediate calculations
  TempF = 50
  TempC = 5 / 9 * (TempF - 32)
in
  // Resulting variable
  TempC
// By convention, variable names are UpperCamelCase
HeightM

// Quote variable names and prefix with # for non-standard names
#"Height in Meters"

Operators

Arithmetic operators

102 + 37 // Add two numbers with +
102 - 37 // Subtract a number with -
4 * 6    // Multiply two numbers with *
22 / 7   // Divide a number by another with /

Numeric comparison operators

3 = 3  // Test for equality with =
3 <> 3 // Test for inquality with <>
3 > 1  // Test greater than with >
3 >= 3 // Test greater than or equal to with >=
3 < 4  // Test less than with <
3 <= 4 // Test less than or equal to with <=

Logical Operators

not (2 = 2)          // Logical NOT with not
(1 <> 1) and (1 < 1) // Logical AND with and
(1 >= 1) or (1 < 1)  // Logical OR with or

Text Operators

// Combine text with &
"fish" & " & " & "chips"

Numbers

Arithmetic

Number.Power(3, 4) // Raise to the power with Power()
Number.IntegerDivide(22, 7) // Integer divide a number with IntegerDivide()
Number.Mod(22, 7) // Get the remainder after division with Mod()
Value.Equals(1.999999, 2, Precision.Double) // Check number close to equal with Equals()

Math functions

Number.Ln(10) // Calculate natural logarithm with Ln()
Number.Exp(3) // Calculate exponential with Exp()
Number.Round(12.3456, 2) // Round to n decimal places with Round()
Number.Abs(-3) // Calculate absolute values with Abs()
Number.Sqrt(49) // Calculate the square root with Sqrt()
Number.IsNaN(Number.NaN) // Returns true if not a number

Text Values

Creating text

// Text values are double-quoted, and can span multiple lines
"M is a programming 
language for ETL"

// Embed quotes in strings by doubling them
"""M is magnificent"", mentioned Mike."

// Include control characters with #()
"Split text with a tab character, #(tab), or start a new line with carriage-return line feed, #(cr,lf)"

// Embed # in strings with #(#)
"Hex codes for colors start with #(#)"

Indexing

// Get the number of characters in text with Length()
Text.Length("How long will dinner be? About 25cm.") // Returns 36

// Get a substring with Middle()
Text.Middle("Zip code: 10018", 10, 5)

Splitting and combining text

// Combine text, optionally separated with Combine()
Text.Combine({"fish", "chips"}, " & ") // Returns "fish & chips"

// Split text on a delimiter with Split()
Text.Split("fish & chips", " & ") // Returns {"fish", "chips"}

Mutating text

// Convert text to upper case with Upper()
Text.Upper("iN cAsE oF eMeRgEnCy") // Returns "IN CASE OF EMERGENCY"

// Convert text to LOWer case with Lower()
Text.Lower("iN cAsE oF eMeRgEnCy") // Returns "in case of emergency"

// Convert text to title case with Proper()
Text.Proper("iN cAsE oF eMeRgEnCy") // Returns "In Case Of Emergency"

// Replace characters in text with Replace()
Text.Replace("Have a nice trip", " n", "n ") // Returns "Have an ice trip"

Type Conversion

// Convert value to number with Number.From()
Number.From(true) // Returns 1

// Dates and datetimes given as time in days since 1899-12-30
Number.From(#datetime(1969, 7, 21, 2, 56, 0)) // Returns 25405.12

// Convert text to number with Number.FromText()
Number.FromText("4.5E3") // Returns 4500

// Convert number to text with Number.ToText()
// Formats: "D": integer digits, "E": exponential, "F": fixed, 
//   "G": general, "N": number, "P": percent
Number.ToText(4500, "E") // Returns "4.5E3"

// Convert value to logical with Logical.From()
Logical.From(2)

Functions

// Define a function with (args) => calculations
let
  Hypotenuse = (x, y) => Number.Sqrt(Number.Power(x, 2) + Number.Power(y, 2))
in
  Hypotenuse
// each is syntactic sugar for a function with 1 arg named _
// Use it to iteraete over lists and tables
each Number.Power(_, 2) // Same as (_) => Number.Power(_, 2)

Lists

Creation

// Define a list with {}
//You can include different data types including null
{999, true, "DataCamp", null}

// Define a sequence of numbers with m..n
{-1..3, 100} // Equivalent to {-1, 0, 1, 2, 3, 100}

// Lists can be nested 
{"outer", {"inner"}} 

// Concatenate lists with &
{1, 4} & {4, 9} // Returns {1, 4, 4, 9}

Example lists

let
  Fruits = {"apple", null, "cherry"}
in 
  Fruits

let 
  Menage = {1, -1, 0, 1, 2, 13, 80, 579}
in
  Menage

Counting

// Get the number of elements of a list with Count()
List.Count(Fruits) // Returns 3

// Get the number of non-null elements with NonNullCount()
List.NonNullCount(Fruits) // Returns 2

Selection

// Access list elements with {}, zero-indexed
Fruits{0} // 1st element; returns "apple"

// Accessing elements outside the range throws an error
Fruits{3} // Throws an Expression.Error

// Append ? to return null if the index is out of range
Fruits{3}? // Returns null

// Get the first few elements with FirstN()
List.FirstN(Fruits, 2) // Returns {"apple", null}

// Get the last few elements with LastN()
List.LastN(Fruits, 2) // Returns {null, "cherry"}

// Get unique elements with Distinct()
List.Distinct(Menage) // Returns {1, -1, 0, 2, 13, 80, 579}

// Get elements that match a criteria with Select()
List.Select(Menage, each _ > 1) // Returns {2, 13, 80, 579}

// Return true if all elements match a criteria with MatchesAll()
List.MatchesAll(Menage, each _ > 1) // Returns false

// Return true if any elements match a criteria with MatchesAny()
List.MatchesAny(Menage, each _ > 1) // Returns true

// Get value from list of length 1, or return default, with SingleOrDefault()
List.SingleOrDefault(Menage, -999) // Returns -999

Manipulation

Remove*, Transform, Accumulate
// Sort items in ascending order with Sort()
List.Sort(Menage) // Returns {-1, 0, 1, 1, 2, 13, 80, 579}

// Sort items in descending order
List.Sort(Menage, Order.Descending) // Returns {579, 80, 13, 2, 1, 1, 0, -1}

// Reverse the order of items in a list with Reverse()
List.Reverse(Menage) Returns {579, 80, 13, 2, 1, 0, -1, 1}

// Get non-null values with RemoveNulls()
List.RemoveNulls(Fruits) // Returns {"apple", "cherry"}

// Remove items by position with RemoveRange()
List.RemoveRange(Menage, 2, 3) // Returns {1, -1, 13, 80, 579}

// Repeat elements with Repeat()
List.Repeat({"one", "two"}, 2) // Returns {"one", "two", "one", "two"}

// Split list into lists of specified size with Split()
List.Split(Menage, 2) // Returns {{1, -1}, {0, 1}, {2, 13}, {80, 579}}

// Flatten lists by removing 1 level of nesting with Combine()
List.Combine({{"alpha"}, {"bravo", {"charlie", "delta"}}})
    // Returns {"alpha", "bravo", {"charlie", "delta"}}

Equality & membership

// Lists are equal if they contain the same elements in the same order
{1, 2} = {1, 2} // true
{1, 2} = {2, 1} // false
{1, 2} = {1, 2, 3} // false

Calculations

// Get the minimum value in a list with Min()
List.Min({0, 7, -3, 2, 1}) // Returns -3

// Get the minimum value in a list with Max()
List.Max({0, 7, -3, 2, 1}) // Returns 7

// Get quantile values from a list with Percentile()
List.Percentile(
  {0, 7, -3, 2, 1}, {0.25, 0.5, 0.75}, 
  [PercentileMode=PercentileMode.SqlDisc]
) // Returns {0, 1, 2}

// Get the sum of values in a list with Sum()
List.Sum({0, 7, -3, 2, 1}) // Returns 7

// Get the product of values in a list with Product()
List.Product({0, 7, -3, 2, 1}) // Returns 0

// Get the mean of values in a list with Average()
List.Average({0, 7, -3, 2, 1}) // Returns 1.4

// Get the standard deviation of values in a list with StandardDeviation()
List.StandardDeviation({0, 7, -3, 2, 1}) // Returns 3.64692

Generation

// Generate random numbers between 0 and 1 with Random()
List.Random(3) // Returns, e.g., {0.170602, 0.991010, 0.676363}

// Generate a sequence of numbers with Numbers()
List.Numbers(1, 5, 2) // Returns {1, 3, 5, 7, 9}

// Generate a sequence of dates with Dates()
List.Dates(#date(2023, 1, 1), 3, #duration(7, 0, 0, 0)) 
// Returns {#date(2023, 1, 1), #date(2023, 1, 8), #date(2023, 1, 15)}

// Mimic a for loop with Generate()
List.Generate(
  () => 2,
  each _ < 20,
  each Number.Power(_, 2)
) // Returns {2, 4, 16}

Set operations

// Get values in all inputs with Intersect()
List.Intersect({{1, 3, 6, 10, 15}, {21, 15, 9, 3}, {0, 3, 6}}) // Returns {3}

// Get values in any inputs with Union()
List.Union({{1, 3, 6, 10, 15}, {21, 15, 9, 3}, {0, 3, 6}}) // Returns {0, 1, 3, 6, 9, 10, 15, 21}

// Get value in one set but not the other with Difference()
List.Difference({1, 3, 6, 10, 15}, {21, 15, 9, 3}) // Returns {1, 6, 10}
Topics
Related

Top 10 Business Intelligence Conferences in 2024

An overview of the top 10 international business intelligence conferences planned for 2024, including their dates, participation fees, formats, hosting cities, main focus, key discussion topics, and target audience.
Elena Kosourova's photo

Elena Kosourova

5 min

Becoming Remarkable with Guy Kawasaki, Author and Chief Evangelist at Canva

Richie and Guy explore the concept of being remarkable, growth, grit and grace, the importance of experiential learning, imposter syndrome, finding your passion, how to network and find remarkable people, measuring success through benevolent impact and much more. 
Richie Cotton's photo

Richie Cotton

55 min

Mastering SWITCH in DAX for Power BI: A Comprehensive Guide

Explore the Power BI SWITCH function, what it is, how to use it, and some common pitfalls and best practices.
Joleen Bothma's photo

Joleen Bothma

10 min

A Step-By-Step Guide to Visualizing KPIs in Power BI

Learn how to effectively visualize KPIs in Power BI using visuals and DAX measures.
Joleen Bothma's photo

Joleen Bothma

8 min

Power BI Slicer Tutorial: A Comprehensive Guide to Enhancing Your Reports

Unlock the power of Power BI slicers with our step-by-step tutorial. Learn to create, customize, and optimize slicers for better reports.
Joleen Bothma's photo

Joleen Bothma

7 min

Complete Guide to Power BI Conditional Formatting

Learn how to use conditional formatting to boost your Power BI reports.
Joleen Bothma's photo

Joleen Bothma

7 min

See MoreSee More