learn.fttgsolutions.com · Cheat Sheets
Extract. Transform. Load.
| Power Query | — ETL tool | Power Query is a tool for extract-transform-load (ETL). It lets you import and prepare data for use in Power BI, Excel, Azure Data Lake Storage, and Dataverse. |
| Power Query M | — functional language | M (short for 'Mashup') is the functional programming language used in Power Query. Case-sensitive. Used for data transformation, not data analysis (that's DAX). |
| Expression | 1 + 1
"Hello" & " World" | A single formula that returns a value. All M code is composed of expressions. |
| Query (let...in) | let
Source = Csv.Document(File.Contents("data.csv")),
Result = Table.PromoteHeaders(Source)
in
Result | A query is a sequence of expressions defined with let...in. Each step can reference previous steps. The in expression is what the query returns. |
| Comments | // Single-line comment
/* Multi-line
comment */ | // for single-line comments. /* */ for multi-line comments. Use them to document steps in the Advanced Editor. |
| Number | 999
3.14
-42 | Integer or decimal number literals. M has no distinction between int and float at the language level. |
| Text | "DataCamp"
"It's a \"quote\"" | Text values are double-quoted. Embed a double-quote by doubling it: "". |
| Logical | true
false | Boolean literals. Lowercase only — True and False are not valid in M. |
| Null | null | Represents a missing or unknown value. Many functions return null instead of throwing an error on empty input. |
| Date | #date(2025, 12, 31) | Date literal using #date(year, month, day). Can also use #datetime, #datetimezone, #duration, #time. |
| Datetime | #datetime(2025, 6, 15, 9, 30, 0) | Datetime literal: #datetime(year, month, day, hour, minute, second). |
| Duration | #duration(days, hours, minutes, seconds)
#duration(1, 0, 30, 0) // 1 day 30 min | Represents an elapsed time span. Use with Date.AddDays or subtraction between two datetime values. |
| List | {1, 2, 3}
{"a", "b", null}
{1..10} | A list is an ordered sequence of values in curly braces. Can contain mixed types. {1..10} creates a range. |
| Record | [Name = "Alice", Age = 30] | A record is a set of named fields in square brackets. Like a single row with column names. |
| Table | #table({"Name","Age"}, {{"Alice",30},{"Bob",25}}) | Creates a table from a list of column names and a list of row lists. |
| let...in | let
TempF = 85,
TempC = (TempF - 32) * 5 / 9
in
TempC | let assigns named intermediate values. Each name can reference earlier names. in specifies the final returned value. |
| Naming conventions | // Standard: UpperCamelCase
HeightMeters
// Non-standard (spaces etc): use #""
#"Height in Meters" | Variable names are UpperCamelCase by convention. Wrap names with spaces or special characters in #"...". |
| Step reference | let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Typed = Table.TransformColumnTypes(Source, {{"Date", type date}})
in
Typed | Each step name becomes a reference. Steps are the building blocks of every Power Query transformation. |
| Arithmetic | 102 + 37 // Add
102 - 37 // Subtract
4 * 6 // Multiply
22 / 7 // Divide | Standard math operators. Use Number.Mod for modulo and Number.IntegerDivide for integer division. |
| Comparison | 3 = 3 // true (equal)
3 <> 4 // true (not equal)
3 > 1 // true
3 >= 3 // true
3 < 4 // true
3 <= 4 // true | Returns a logical value (true/false). M comparison is case-sensitive for text. |
| Logical | not (2 = 2) // false
(1 < 1) and (1 >= 1) // false
(1 >= 1) or (1 < 1) // true | and, or, not operators for combining logical conditions. |
| Text concatenation & | "fish" & " " & "chips"
// Returns "fish chips" | The & operator joins text values. Equivalent to Text.Combine for two strings. |
| List concatenation & | {1, 4} & {4, 9}
// Returns {1, 4, 4, 9} | The & operator also concatenates two lists end-to-end. |
| is / as (type) | 1 is number // true
"hello" is text // true
Value.Is(x, type number) | is checks if a value matches a type. as casts a value to a type (errors if it doesn't match). |
| ? (optional) | Record.Field(rec, "Name")?
// Returns null instead of error if field missing | The ? operator after an expression converts errors into null rather than stopping the query. |
| Text.Length | Text.Length("Hello World") // 11 | Returns the number of characters in a text string. |
| Text.Upper / Lower / Proper | Text.Upper("hello") // "HELLO"
Text.Lower("HELLO") // "hello"
Text.Proper("hello world") // "Hello World" | Converts text to uppercase, lowercase, or title case (first letter of each word capitalised). |
| Text.Start / End | Text.Start("DataCamp", 4) // "Data"
Text.End("DataCamp", 4) // "Camp" | Extracts characters from the start or end of a string. |
| Text.Middle | Text.Middle("Zip: 10018", 5, 5) // "10018" | Extracts a substring starting at a zero-based position for the given length. |
| Text.Range | Text.Range("DataCamp", 0, 4) // "Data" | Extracts a substring by zero-based start index and length. Similar to Text.Middle. |
| Text.Split | Text.Split("a,b,c", ",")
// {"a", "b", "c"} | Splits a text value on a delimiter and returns a list of text values. |
| Text.Combine | Text.Combine({"fish","chips"}, " & ")
// "fish & chips" | Joins a list of text values with a separator string. |
| Text.Replace | Text.Replace("Have a nice trip", "trip", "day")
// "Have a nice day" | Replaces all occurrences of a substring with a new substring. |
| Text.Contains | Text.Contains("Power Query", "Query") // true | Returns true if the text string contains the specified substring. |
| Text.StartsWith / EndsWith | Text.StartsWith("DataCamp", "Data") // true
Text.EndsWith("DataCamp", "Camp") // true | Checks whether a text value starts or ends with a given substring. |
| Text.Trim / TrimStart / TrimEnd | Text.Trim(" hello ") // "hello"
Text.TrimStart(" hello") // "hello"
Text.TrimEnd("hello ") // "hello" | Removes whitespace (or specified characters) from both ends, the start, or the end of a string. |
| Text.PadStart / PadEnd | Text.PadStart("42", 6, "0") // "000042"
Text.PadEnd("42", 6, "0") // "420000" | Pads a string to a minimum length using a fill character on the left or right. |
| Text.From | Text.From(42) // "42"
Text.From(true) // "true"
Text.From(#date(2025,1,1)) // "1/1/2025" | Converts any value to its text representation. |
| Text.ToList | Text.ToList("abc") // {"a", "b", "c"} | Splits a text string into a list of individual characters. |
| Number.Abs | Number.Abs(-42) // 42 | Returns the absolute (positive) value of a number. |
| Number.Round | Number.Round(12.3456, 2) // 12.35 | Rounds to the specified number of decimal places using standard half-up rounding. |
| Number.RoundUp / Down | Number.RoundUp(1.1, 0) // 2
Number.RoundDown(1.9, 0) // 1 | Always rounds away from or toward zero, regardless of the digit value. |
| Number.IntegerDivide | Number.IntegerDivide(22, 7) // 3 | Returns the integer quotient — discards the remainder. |
| Number.Mod | Number.Mod(22, 7) // 1 | Returns the remainder after integer division. |
| Number.Power | Number.Power(3, 4) // 81 | Raises a number to the given power. |
| Number.Sqrt | Number.Sqrt(49) // 7 | Returns the positive square root of a number. |
| Number.Ln / Exp | Number.Ln(10) // 2.302...
Number.Exp(1) // 2.718... | Natural logarithm and exponential (e^n). |
| Number.Log | Number.Log(100, 10) // 2 | Returns the logarithm of a number to the specified base. |
| Number.IsNaN | Number.IsNaN(0/0) // true | Returns true if the value is NaN (Not a Number). Useful after division that may produce undefined results. |
| Number.From | Number.From("3.14") // 3.14
Number.From(true) // 1
Number.From(#date(2025,1,1)) // days since 1899-12-30 | Converts text, logical, or date values to a number. |
| Number.ToText | Number.ToText(4500, "E") // "4.5E+3"
Number.ToText(0.45, "P") // "45.00%" | Formats a number as text. Format codes: G (general), F (fixed), N (number), P (percent), E (exponential). |
| Value.Equals | Value.Equals(1.999999, 2, Precision.Double) // true | Compares two values for equality with optional precision control — useful for floating-point comparisons. |
| Date.From | Date.From("2025-06-15")
Date.From(#datetime(2025,6,15,9,0,0)) | Converts text or datetime values to a date. |
| DateTime.LocalNow | DateTime.LocalNow() | Returns the current local date and time. Refreshes on every query evaluation. |
| Date.Year / Month / Day | Date.Year(#date(2025,6,15)) // 2025
Date.Month(#date(2025,6,15)) // 6
Date.Day(#date(2025,6,15)) // 15 | Extracts the year, month (1–12), or day of month from a date value. |
| Date.DayOfWeek | Date.DayOfWeek(#date(2025,6,15), Day.Monday)
// 6 (Sunday, 0=Monday start) | Returns the day of the week as a number. Second argument sets the first day: Day.Monday, Day.Sunday. |
| Date.WeekOfYear | Date.WeekOfYear(#date(2025,6,15)) | Returns the week number within the year for the given date. |
| Date.QuarterOfYear | Date.QuarterOfYear(#date(2025,6,15)) // 2 | Returns the quarter number (1–4) for a date. |
| Date.AddDays / Months / Years | Date.AddDays(#date(2025,1,1), 30)
Date.AddMonths(#date(2025,1,1), 3)
Date.AddYears(#date(2025,1,1), 1) | Adds the specified number of days, months, or years to a date. |
| Date.StartOfMonth / EndOfMonth | Date.StartOfMonth(#date(2025,6,15)) // 2025-06-01
Date.EndOfMonth(#date(2025,6,15)) // 2025-06-30 | Returns the first or last date of the month containing the given date. |
| Date.StartOfYear / EndOfYear | Date.StartOfYear(#date(2025,6,15)) // 2025-01-01
Date.EndOfYear(#date(2025,6,15)) // 2025-12-31 | Returns the first or last date of the year containing the given date. |
| Duration.Days / TotalHours | Duration.Days(#duration(3, 12, 0, 0)) // 3
Duration.TotalHours(#duration(1, 6, 0, 0)) // 30 | Extracts a component from a duration. TotalHours/TotalMinutes/TotalSeconds return the full span, not just the component. |
| Date difference | Duration.Days(#date(2025,12,31) - #date(2025,1,1))
// 364 | Subtracting two dates returns a duration. Wrap in Duration.Days / TotalHours etc. to get a numeric count. |
| List creation | {1, 2, 3}
{1..10} // range
{0, 2..10} // step range | Lists are created with curly braces. Use .. for ranges. Mixed types are allowed. |
| List.Count | List.Count({10, 20, 30}) // 3 | Returns the number of items in a list. |
| List.First / Last | List.First({"a",null,"c"}, 2) // "a"
List.Last({"a",null,"c"}, 2) // "c" | Returns the first or last element. Optional second argument is a default value if the list is empty. |
| List.FirstN / LastN | List.FirstN({1,2,3,4,5}, 3) // {1,2,3}
List.LastN({1,2,3,4,5}, 2) // {4,5} | Returns the first or last N items from a list. |
| List.Distinct | List.Distinct({1,2,2,3,3}) // {1,2,3} | Returns a list with duplicate values removed, preserving the first occurrence. |
| List.Select | List.Select({1,-1,0,2,13}, each _ > 1)
// {2, 13} | Returns a new list containing only items that satisfy the condition. Uses each with _ as the current item. |
| List.Transform | List.Transform({1,2,3,4}, each _ * 2)
// {2, 4, 6, 8} | Applies a function to every item and returns the resulting list. |
| List.Sort | List.Sort({3,1,2}) // {1,2,3}
List.Sort({3,1,2}, Order.Descending) // {3,2,1} | Returns a sorted copy of the list. Default is ascending order. |
| List.Reverse | List.Reverse({1,2,3}) // {3,2,1} | Returns a new list with the items in reverse order. |
| List.Contains / ContainsAny / All | List.Contains({1,2,3}, 2) // true
List.ContainsAny({1,2,3}, {3,4}) // true
List.ContainsAll({1,2,3}, {1,2}) // true | Checks whether a list contains a single value, any from a second list, or all from a second list. |
| List.MatchesAny / All | List.MatchesAny({1,2,13}, each _ > 10) // true
List.MatchesAll({1,2,13}, each _ > 0) // false | Returns true if any or all items satisfy the condition. |
| List.RemoveNulls | List.RemoveNulls({"a",null,"c"}) // {"a","c"} | Returns a new list with all null values removed. |
| List.RemoveRange | List.RemoveRange({1,2,3,4,5}, 1, 2)
// {1,4,5} — remove 2 items starting at index 1 | Removes a range of items by zero-based start index and count. |
| List.Repeat | List.Repeat({"a","b"}, 3)
// {"a","b","a","b","a","b"} | Repeats the list the specified number of times. |
| List.Combine | List.Combine({{1,2},{3,4}}) // {1,2,3,4} | Flattens one level of nesting — takes a list of lists and returns a single flat list. |
| List.Split | List.Split({1,2,3,4,5,6}, 2)
// {{1,2},{3,4},{5,6}} | Splits a list into sub-lists of the given size. |
| List.Min / Max / Sum / Average | List.Min({10,7,-3,2}) // -3
List.Max({10,7,-3,2}) // 10
List.Sum({10,7,-3,2}) // 16
List.Average({10,7,-3,2}) // 4 | Aggregate functions over a list. All ignore null values. |
| List.Product / StandardDeviation | List.Product({10,7,-3,2}) // -420
List.StandardDeviation({10,7,-3,2}) // ... | Multiplies all items together, or returns the standard deviation of a numeric list. |
| List.Union / Intersect / Difference | List.Union({{1,2},{2,3}}) // {1,2,3}
List.Intersect({{1,2,3},{2,3,4}}) // {2,3}
List.Difference({1,2,3},{2,3}) // {1} | Set operations on lists. Union merges unique values; Intersect keeps common values; Difference keeps items only in the first list. |
| List.Numbers | List.Numbers(1, 5, 2)
// {1, 3, 5, 7, 9} | Generates a list starting at start, with count items, incrementing by step. |
| List.Dates | List.Dates(#date(2025,1,1), 7, #duration(1,0,0,0))
// 7 dates starting Jan 1, 2025 | Generates a list of dates starting at a date, for a count of items, with a duration step. |
| List.Generate | List.Generate(
() => 1,
each _ < 20,
each _ * 2
)
// {1,2,4,8,16} | Generates a list by repeatedly applying a function until a condition is false. Arguments: initial, condition, next. |
| List.SingleOrDefault | List.SingleOrDefault({42}, -1) // 42
List.SingleOrDefault({1,2}, -1) // -1 (more than one item) | Returns the single item, or the default value if the list has zero or more than one item. |
| Record creation | [Name = "Alice", Age = 30] | Records are created with square brackets using Name = Value pairs. Used to represent a single row of data. |
| Record.Field | Record.Field([Name="Alice",Age=30], "Name")
// "Alice" | Retrieves a field value by name. Equivalent to record[FieldName] shorthand. |
| record[FieldName] | let r = [City = "Lagos", Pop = 15000000]
in r[City] // "Lagos" | Dot-style field access. Raises an error if the field doesn't exist — use Record.Field with ? for safe access. |
| Record.FieldNames / Values | Record.FieldNames([A=1,B=2]) // {"A","B"}
Record.FieldValues([A=1,B=2]) // {1,2} | Returns a list of the record's field names or field values. |
| Record.HasFields | Record.HasFields([A=1,B=2], "B") // true
Record.HasFields([A=1,B=2], {"A","C"}) // false | Returns true if the record contains a field or all fields in a list. |
| Record.AddField | Record.AddField([A=1], "B", 2)
// [A=1, B=2] | Returns a new record with an additional field. Original record is not mutated. |
| Record.RemoveFields | Record.RemoveFields([A=1,B=2,C=3], "B")
// [A=1, C=3] | Returns a new record with the specified field(s) removed. |
| Record.RenameFields | Record.RenameFields([OldName=1], {{"OldName","NewName"}})
// [NewName=1] | Returns a new record with specified fields renamed. Argument is a list of {old, new} name pairs. |
| Record.ToTable | Record.ToTable([A=1,B=2])
// Table with Name/Value columns | Converts a record to a two-column table with Name and Value columns. |
| Record.FromList | Record.FromList({1, 2}, {"A", "B"})
// [A=1, B=2] | Creates a record from a list of values and a matching list of field names. |
| Table.FromRecords | Table.FromRecords({
[Name="Alice", Age=30],
[Name="Bob", Age=25]
}) | Creates a table from a list of records. Each record becomes a row; field names become column headers. |
| Table.RowCount / ColumnCount | Table.RowCount(myTable) // number of rows
Table.ColumnCount(myTable) // number of columns | Returns the number of rows or columns in a table. |
| Table.ColumnNames | Table.ColumnNames(myTable)
// {"Name","Age","Region"} | Returns a list of the table's column names in order. |
| Table.SelectRows | Table.SelectRows(myTable, each [Region] = "North")
Table.SelectRows(myTable, each [Sales] > 1000) | Filters a table to rows where the condition is true. Uses each with field access via [ColumnName]. |
| Table.AddColumn | Table.AddColumn(
myTable,
"Profit",
each [Revenue] - [Cost],
type number
) | Adds a new column computed from each row. Fourth argument is the optional type annotation. |
| Table.TransformColumns | Table.TransformColumns(myTable, {
{"Name", Text.Upper},
{"Sales", each _ * 1.1}
}) | Applies a transformation function to one or more columns by name. |
| Table.TransformColumnTypes | Table.TransformColumnTypes(myTable, {
{"Date", type date},
{"Amount", type number}
}) | Changes the data type of one or more columns. The most common step after Source in any query. |
| Table.RemoveColumns | Table.RemoveColumns(myTable, {"Col1","Col2"}) | Returns the table without the specified columns. |
| Table.SelectColumns | Table.SelectColumns(myTable, {"Name","Sales"}) | Returns only the specified columns, in the order listed. |
| Table.RenameColumns | Table.RenameColumns(myTable, {
{"OldName", "NewName"}
}) | Renames one or more columns. Argument is a list of {old, new} name pairs. |
| Table.ReorderColumns | Table.ReorderColumns(myTable, {"Sales","Name","Date"}) | Returns the table with columns in the specified order. Unlisted columns are dropped. |
| Table.Sort | Table.Sort(myTable, {
{"Sales", Order.Descending},
{"Name", Order.Ascending}
}) | Sorts a table by one or more columns. Order.Ascending (default) or Order.Descending. |
| Table.Group | Table.Group(
myTable,
{"Region"},
{{"Total", each List.Sum([Sales]), type number}}
) | Groups rows by one or more key columns and aggregates each group. Similar to GROUP BY in SQL. |
| Table.PromoteHeaders | Table.PromoteHeaders(myTable) | Promotes the first row of data to become the column headers. |
| Table.DemoteHeaders | Table.DemoteHeaders(myTable) | Converts the current column headers into the first data row, then assigns generic column names. |
| Table.FillDown / FillUp | Table.FillDown(myTable, {"Region"})
Table.FillUp(myTable, {"Region"}) | Fills null values downward or upward from the nearest non-null value in the specified columns. |
| Table.Pivot | Table.Pivot(
myTable,
List.Distinct(myTable[Month]),
"Month", "Sales"
) | Rotates unique values of one column into new column headers, aggregating the value column. Same as Pivot Column in the UI. |
| Table.Unpivot / UnpivotOtherColumns | Table.Unpivot(myTable, {"Jan","Feb","Mar"}, "Month", "Sales")
Table.UnpivotOtherColumns(myTable, {"Product"}, "Month", "Sales") | Converts column headers into rows. UnpivotOtherColumns keeps the specified columns fixed and unpivots everything else. |
| Table.ExpandListColumn | Table.ExpandListColumn(myTable, "Tags") | Expands a column containing lists — each list item becomes a separate row. |
| Table.ExpandRecordColumn | Table.ExpandRecordColumn(
myTable, "Details",
{"City","Zip"}
) | Expands a column containing records — each specified field becomes its own column. |
| Table.NestedJoin | Table.NestedJoin(
left, {"ID"},
right, {"ID"},
"Joined",
JoinKind.Inner
) | Joins two tables on a key column. Returns the right-side matches as a nested table in a new column. JoinKind: Inner, LeftOuter, RightOuter, FullOuter, LeftAnti, RightAnti. |
| Table.Join | Table.Join(
left, {"ID"},
right, {"ID"},
JoinKind.LeftOuter
) | Flat join that returns combined columns directly, without a nested table step. |
| Table.Distinct | Table.Distinct(myTable)
Table.Distinct(myTable, {"Region"}) | Removes duplicate rows. Optional second argument specifies which columns to consider for uniqueness. |
| Table.Buffer | Table.Buffer(myTable) | Forces the table to be fully evaluated and cached in memory. Prevents repeated evaluation of expensive upstream steps. |
| Function definition | let
Hypotenuse = (x, y) =>
Number.Sqrt(Number.Power(x,2) + Number.Power(y,2))
in
Hypotenuse(3, 4) // 5 | Functions are defined with (params) => expression. They can be assigned to names with let and called by name. |
| each shorthand | each _ + 1
// same as: (_) => _ + 1
each Number.Power(_, 2)
// same as: (x) => Number.Power(x, 2) | each is syntactic sugar for a one-argument function where the argument is named _. Used heavily in List, Table, and Record functions. |
| each with record fields | each [Sales] > 1000
// same as: (row) => row[Sales] > 1000 | Inside Table functions, each automatically receives the current row as a record. Access columns with [ColumnName]. |
| Type annotation | (x as number, y as number) as number =>
x + y | Add type constraints to function parameters and return values with as. M checks types at runtime, not compile time. |
| Optional parameters | (text as text, optional delimiter as nullable text) =>
Text.Split(text, delimiter ?? ",") | Mark parameters optional with the optional keyword. Use ?? to provide a default if null is passed. |
| ?? (null coalescing) | x ?? defaultValue | Returns the left value if it is not null, otherwise returns the right value. Cleaner than if x = null then default else x. |