Cheat Sheets

Power Query M Cheat Sheet

Quick reference for Power Query M — the functional language behind Power BI and Excel data transformations. Covers values, operators, text, numbers, dates, lists, records, and tables.

SQLPythonPower BIDAXM CodeMicrosoft FabricSnowflakeDatabricksGitVS CodeVimHomebrewHTMLCSS3JSONJavaJavaScriptAI PromptsMarkdownData Warehouse

Extract. Transform. Load.

Core Concepts
Power QueryPower Query MExpressionQuery (let...in)Comments
Creating Values
NumberTextLogicalNullDateDatetimeDurationListRecordTable
Variables & Queries
let...inNaming conventionsStep reference
Operators
ArithmeticComparisonLogicalText concatenation &List concatenation &is / as (type)? (optional)
Text Functions
Text.LengthText.Upper / Lower / ProperText.Start / EndText.MiddleText.RangeText.SplitText.CombineText.ReplaceText.ContainsText.StartsWith / EndsWithText.Trim / TrimStart / TrimEndText.PadStart / PadEndText.FromText.ToList
Number Functions
Number.AbsNumber.RoundNumber.RoundUp / DownNumber.IntegerDivideNumber.ModNumber.PowerNumber.SqrtNumber.Ln / ExpNumber.LogNumber.IsNaNNumber.FromNumber.ToTextValue.Equals
Date & Time Functions
Date.FromDateTime.LocalNowDate.Year / Month / DayDate.DayOfWeekDate.WeekOfYearDate.QuarterOfYearDate.AddDays / Months / YearsDate.StartOfMonth / EndOfMonthDate.StartOfYear / EndOfYearDuration.Days / TotalHoursDate difference
List Functions
List creationList.CountList.First / LastList.FirstN / LastNList.DistinctList.SelectList.TransformList.SortList.ReverseList.Contains / ContainsAny / AllList.MatchesAny / AllList.RemoveNullsList.RemoveRangeList.RepeatList.CombineList.SplitList.Min / Max / Sum / AverageList.Product / StandardDeviationList.Union / Intersect / DifferenceList.NumbersList.DatesList.GenerateList.SingleOrDefault
Record Functions
Record creationRecord.Fieldrecord[FieldName]Record.FieldNames / ValuesRecord.HasFieldsRecord.AddFieldRecord.RemoveFieldsRecord.RenameFieldsRecord.ToTableRecord.FromList
Table Functions
Table.FromRecordsTable.RowCount / ColumnCountTable.ColumnNamesTable.SelectRowsTable.AddColumnTable.TransformColumnsTable.TransformColumnTypesTable.RemoveColumnsTable.SelectColumnsTable.RenameColumnsTable.ReorderColumnsTable.SortTable.GroupTable.PromoteHeadersTable.DemoteHeadersTable.FillDown / FillUpTable.PivotTable.Unpivot / UnpivotOtherColumnsTable.ExpandListColumnTable.ExpandRecordColumnTable.NestedJoinTable.JoinTable.DistinctTable.Buffer
Functions & Each
Function definitioneach shorthandeach with record fieldsType annotationOptional parameters?? (null coalescing)