Table of contents
TOC
Collapse the table of content
Expand the table of content

Power Query M Reference

Owen Duncan|Last Updated: 4/18/2018

Power Query M language specification

Power Query M type system

Power Query M function reference

Understanding Power Query M functions

Accessing data functions

Access.Database

ActiveDirectory.Domains

AdobeAnalytics.Cubes

AdoDotNet.DataSource

AdoDotNet.Query

AnalysisServices.Database

AnalysisServices.Databases

AzureStorage.BlobContents

AzureStorage.Blobs

AzureStorage.Tables

Csv.Document

CsvStyle.QuoteAfterDelimiter

CsvStyle.QuoteAlways

Cube.AddAndExpandDimensionColumn

Cube.AddMeasureColumn

Cube.ApplyParameter

Cube.AttributeMemberId

Cube.AttributeMemberProperty

Cube.CollapseAndRemoveColumns

Cube.Dimensions

Cube.DisplayFolders

Cube.Measures

Cube.Parameters

Cube.PropertyKey

Cube.ReplaceDimensions

Cube.Transform

DB2.Database

Excel.CurrentWorkbook

Excel.Workbook

Exchange.Contents

Facebook.Graph

File.Contents

Folder.Contents

Folder.Files

GoogleAnalytics.Accounts

Hdfs.Contents

HdInsight.Files

HdInsight.Containers

HdInsight.Contents

Hdfs.Files

Informix.Database

Json.Document

MySQL.Database

OData.Feed

Odbc.DataSource

Odbc.Query

OleDb.DataSource

OleDb.Query

Oracle.Database

PostgreSQL.Database

RData.FromBinary

Salesforce.Data

Salesforce.Reports

SapBusinessObjects.Universes

SapBusinessWarehouse.Cubes

SapHana.Database

SapHanaDistribution.All

SapHanaDistribution.Connection

SapHanaDistribution.Off

SapHanaDistribution.Statement

SapHanaRangeOperator.Equals

SapHanaRangeOperator.GreaterThan

SapHanaRangeOperator.GreaterThanOrEquals

SapHanaRangeOperator.LessThan

SapHanaRangeOperator.LessThanOrEquals

SapHanaRangeOperator.NotEquals

SharePoint.Contents

SharePoint.Files

SharePoint.Tables

Soda.Feed

Sql.Database

Sql.Databases

Sybase.Database

Teradata.Database

Web.Contents

Web.Page

WebMethod.Delete

WebMethod.Get

WebMethod.Head

WebMethod.Patch

WebMethod.Post

WebMethod.Put

Xml.Document

Xml.Tables

Cube.PropertyKey

Binary functions

Binary.Buffer

Binary.Combine

Binary.Compress

Binary.Decompress

Binary.From

Binary.FromList

Binary.FromText

Binary.Length

Binary.ToList

Binary.ToText

BinaryEncoding.Base64

BinaryEncoding.Hex

BinaryFormat.7BitEncodedSignedInteger

BinaryFormat.7BitEncodedUnsignedInteger

BinaryFormat.Binary

BinaryFormat.Byte

BinaryFormat.ByteOrder

BinaryFormat.Choice

BinaryFormat.Decimal

BinaryFormat.Double

BinaryFormat.Group

BinaryFormat.Length

BinaryFormat.List

BinaryFormat.Null

BinaryFormat.Record

BinaryFormat.SignedInteger16

BinaryFormat.SignedInteger32

BinaryFormat.SignedInteger64

BinaryFormat.Single

BinaryFormat.Text

BinaryFormat.Transform

BinaryFormat.UnsignedInteger16

BinaryFormat.UnsignedInteger32

BinaryFormat.UnsignedInteger64

BinaryOccurrence.Optional

BinaryOccurrence.Repeating

BinaryOccurrence.Required

ByteOrder.BigEndian

ByteOrder.LittleEndian

Compression.Deflate

Compression.GZip

Occurrence.Optional

Occurrence.Repeating

Occurrence.Required

Table.PartitionValues

#binary

Combiner functions

Combiner.CombineTextByDelimiter

Combiner.CombineTextByEachDelimiter

Combiner.CombineTextByLengths

Combiner.CombineTextByPositions

Combiner.CombineTextByRanges

Comparer functions

Comparer.Equals

Comparer.FromCulture

Comparer.Ordinal

Comparer.OrdinalIgnoreCase

Culture.Current

Date functions

Date.AddDays

Date.AddMonths

Date.AddQuarters

Date.AddWeeks

Date.AddYears

Date.Day

Date.DayOfWeek

Date.DayOfWeekName

Date.DayOfYear

Date.DaysInMonth

Date.EndOfDay

Date.EndOfMonth

Date.EndOfQuarter

Date.EndOfWeek

Date.EndOfYear

Date.From

Date.FromText

Date.IsInCurrentDay

Date.IsInCurrentMonth

Date.IsInCurrentQuarter

Date.IsInCurrentWeek

Date.IsInCurrentYear

Date.IsInNextDay

Date.IsInNextMonth

Date.IsInNextNDays

Date.IsInNextNMonths

Date.IsInNextNQuarters

Date.IsInNextNWeeks

Date.IsInNextNYears

Date.IsInNextQuarter

Date.IsInNextWeek

Date.IsInNextYear

Date.IsInPreviousDay

Date.IsInPreviousMonth

Date.IsInPreviousNDays

Date.IsInPreviousNMonths

Date.IsInPreviousNQuarters

Date.IsInPreviousNWeeks

Date.IsInPreviousNYears

Date.IsInPreviousQuarter

Date.IsInPreviousWeek

Date.IsInPreviousYear

Date.IsInYearToDate

Date.IsLeapYear

Date.Month

Date.MonthName

Date.QuarterOfYear

Date.StartOfDay

Date.StartOfMonth

Date.StartOfWeek

Date.StartOfQuarter

Date.StartOfYear

Date.ToRecord

Date.ToText

Date.WeekOfMonth

Date.WeekOfYear

Date.Year

Day.Friday

Day.Monday

Day.Saturday

Day.Sunday

Day.Thursday

Day.Tuesday

Day.Wednesday

#date

DateTime functions

DateTime.AddZone

DateTime.Date

DateTime.FixedLocalNow

DateTime.From

DateTime.FromFileTime

DateTime.FromText

DateTime.IsInCurrentHour

DateTime.IsInCurrentMinute

DateTime.IsInCurrentSecond

DateTime.IsInNextHour

DateTime.IsInNextMinute

DateTime.IsInNextNHours

DateTime.IsInNextNMinutes

DateTime.IsInNextNSeconds

DateTime.IsInNextSecond

DateTime.IsInPreviousHour

DateTime.IsInPreviousMinute

DateTime.IsInPreviousNHours

DateTime.IsInPreviousNMinutes

DateTime.IsInPreviousNSeconds

DateTime.IsInPreviousSecond

DateTime.LocalNow

DateTime.Time

DateTime.ToRecord

DateTime.ToText

#datetime

DateTimeZone functions

DateTimeZone.FixedLocalNow

DateTimeZone.FixedUtcNow

DateTimeZone.From

DateTimeZone.FromFileTime

DateTimeZone.FromText

DateTimeZone.LocalNow

DateTimeZone.RemoveZone

DateTimeZone.SwitchZone

DateTimeZone.ToLocal

DateTimeZone.ToRecord

DateTimeZone.ToText

DateTimeZone.ToUtc

DateTimeZone.UtcNow

DateTimeZone.ZoneHours

DateTimeZone.ZoneMinutes

#datetimezone

Duration functions

Duration.Days

Duration.From

Duration.FromText

Duration.Hours

Duration.Minutes

Duration.Seconds

Duration.ToRecord

Duration.TotalDays

Duration.TotalHours

Duration.TotalMinutes

Duration.TotalSeconds

Duration.ToText

#duration

Error handling

Diagnostics.ActivityId

Diagnostics.Trace

Error.Record

TraceLevel.Critical

TraceLevel.Error

TraceLevel.Information

TraceLevel.Verbose

TraceLevel.Warning

Expression functions

Expression.Constant

Expression.Evaluate

Expression.Identifier

Function values

Function.From

Function.Invoke

Function.InvokeAfter

Function.IsDataSource

Lines functions

Lines.FromBinary

Lines.FromText

Lines.ToBinary

Lines.ToText

List functions

List.Accumulate

List.AllTrue

List.Alternate

List.AnyTrue

List.Average

List.Buffer

List.Combine

List.Contains

List.ContainsAll

List.ContainsAny

List.Count

List.Covariance

List.Dates

List.DateTimes

List.DateTimeZones

List.Difference

List.Distinct

List.Durations

List.FindText

List.First

List.FirstN

List.Generate

List.InsertRange

List.Intersect

List.IsDistinct

List.IsEmpty

List.Last

List.LastN

List.MatchesAll

List.MatchesAny

List.Max

List.MaxN

List.Median

List.Min

List.MinN

List.Mode

List.Modes

List.NonNullCount

List.Numbers

List.PositionOf

List.PositionOfAny

List.Positions

List.Product

List.Random

List.Range

List.RemoveFirstN

List.RemoveItems

List.RemoveLastN

List.RemoveMatchingItems

List.RemoveNulls

List.RemoveRange

List.Repeat

List.ReplaceMatchingItems

List.ReplaceRange

List.ReplaceValue

List.Reverse

List.Select

List.Single

List.SingleOrDefault

List.Skip

List.Sort

List.StandardDeviation

List.Sum

List.Times

List.Transform

List.TransformMany

List.Union

List.Zip

Logical functions

Logical.From

Logical.FromText

Logical.ToText

Number functions

Byte.From

Currency.From

Decimal.From

Double.From

Int8.From

Int16.From

Int32.From

Int64.From

Number.Abs

Number.Acos

Number.Asin

Number.Atan

Number.Atan2

Number.BitwiseAnd

Number.BitwiseNot

Number.BitwiseOr

Number.BitwiseShiftLeft

Number.BitwiseShiftRight

Number.BitwiseXor

Number.Combinations

Number.Cos

Number.Cosh

Number.E

Number.Epsilon

Number.Exp

Number.Factorial

Number.From

Number.FromText

Number.IntegerDivide

Number.IsEven

Number.IsNaN

Number.IsOdd

Number.Ln

Number.Log

Number.Log10

Number.Mod

Number.NaN

Number.NegativeInfinity

Number.Permutations

Number.PI

Number.PositiveInfinity

Number.Power

Number.Random

Number.RandomBetween

Number.Round

Number.RoundAwayFromZero

Number.RoundDown

Number.RoundTowardZero

Number.RoundUp

Number.Sign

Number.Sin

Number.Sinh

Number.Sqrt

Number.Tan

Number.Tanh

Number.ToText

Percentage.From

RoundingMode.AwayFromZero

RoundingMode.Down

RoundingMode.ToEven

RoundingMode.TowardZero

RoundingMode.Up

Single.From

Record functions

MissingField.Error

MissingField.Ignore

MissingField.UseNull

Record.AddField

Record.Combine

Record.Field

Record.FieldCount

Record.FieldNames

Record.FieldOrDefault

Record.FieldValues

Record.FromList

Record.FromTable

Record.HasFields

Record.RemoveFields

Record.RenameFields

Record.ReorderFields

Record.SelectFields

Record.ToList

Record.ToTable

Record.TransformFields

Replacer functions

Replacer.ReplaceText

Replacer.ReplaceValue

Splitter functions

QuoteStyle.Csv

QuoteStyle.None

Splitter.SplitByNothing

Splitter.SplitTextByAnyDelimiter

Splitter.SplitTextByDelimiter

Splitter.SplitTextByEachDelimiter

Splitter.SplitTextByLengths

Splitter.SplitTextByPositions

Splitter.SplitTextByRanges

Splitter.SplitTextByRepeatedLengths

Splitter.SplitTextByWhitespace

Table functions

ExtraValues.Error

ExtraValues.Ignore

ExtraValues.List

GroupKind.Global

GroupKind.Local

ItemExpression.From

ItemExpression.Item

JoinAlgorithm.Dynamic

JoinAlgorithm.LeftHash

JoinAlgorithm.LeftIndex

JoinAlgorithm.PairwiseHash

JoinAlgorithm.RightHash

JoinAlgorithm.RightIndex

JoinAlgorithm.SortMerge

JoinKind.FullOuter

JoinKind.Inner

JoinKind.LeftAnti

JoinKind.LeftOuter

JoinKind.RightAnti

JoinKind.RightOuter

JoinSide.Left

JoinSide.Right

Occurrence.All

Occurrence.First

Occurrence.Last

Order.Ascending

Order.Descending

RowExpression.Column

RowExpression.From

RowExpression.Row

Table.AddColumn

Table.AddIndexColumn

Table.AddJoinColumn

Table.AddKey

Table.AggregateTableColumn

Table.AlternateRows

Table.Buffer

Table.Column

Table.ColumnCount

Table.ColumnNames

Table.ColumnsOfType

Table.Combine

Table.CombineColumns

Table.Contains

Table.ContainsAll

Table.ContainsAny

Table.DemoteHeaders

Table.Distinct

Table.DuplicateColumn

Table.ExpandListColumn

Table.ExpandRecordColumn

Table.ExpandTableColumn

Table.FillDown

Table.FillUp

Table.FilterWithDataTable

Table.FindText

Table.First

Table.FirstN

Table.FirstValue

Table.FromColumns

Table.FromList

Table.FromPartitions

Table.FromRecords

Table.FromRows

Table.FromValue

Table.Group

Table.HasColumns

Table.InsertRows

Table.IsDistinct

Table.IsEmpty

Table.Join

Table.Keys

Table.Last

Table.LastN

Table.MatchesAllRows

Table.MatchesAnyRows

Table.Max

Table.MaxN

Table.Min

Table.MinN

Table.NestedJoin

Table.Partition

Table.Pivot

Table.PositionOf

Table.PositionOfAny

Table.Profile

Table.PrefixColumns

Table.PromoteHeaders

Table.Range

Table.RemoveColumns

Table.RemoveFirstN

Table.RemoveLastN

Table.RemoveMatchingRows

Table.RemoveRows

Table.RemoveRowsWithErrors

Table.RenameColumns

Table.ReorderColumns

Table.Repeat

Table.ReplaceErrorValues

Table.ReplaceKeys

Table.ReplaceMatchingRows

Table.ReplaceRelationshipIdentity

Table.ReplaceRows

Table.ReplaceValue

Table.ReverseRows

Table.RowCount

Table.Schema

Table.SelectColumns

Table.SelectRows

Table.SelectRowsWithErrors

Table.SingleRow

Table.Skip

Table.Sort

Table.SplitColumn

Table.ToColumns

Table.ToList

Table.ToRecords

Table.ToRows

Table.TransformColumnNames

Table.TransformColumns

Table.TransformColumnTypes

Table.TransformRows

Table.Transpose

Table.Unpivot

Table.UnpivotOtherColumns

Table.View

Table.ViewFunction

Tables.GetRelationships

#table

Text functions

Character.FromNumber

Character.ToNumber

Guid.From

Json.FromValue

RelativePosition.FromEnd

RelativePosition.FromStart

Text.AfterDelimiter

Text.At

Text.BeforeDelimiter

Text.BetweenDelimiters

Text.Clean

Text.Combine

Text.Contains

Text.End

Text.EndsWith

Text.Format

Text.From

Text.FromBinary

Text.Insert

Text.Length

Text.Lower

Text.Middle

Text.NewGuid

Text.PadEnd

Text.PadStart

Text.PositionOf

Text.PositionOfAny

Text.Proper

Text.Range

Text.Remove

Text.RemoveRange

Text.Repeat

Text.Replace

Text.ReplaceRange

Text.Select

Text.Split

Text.SplitAny

Text.Start

Text.StartsWith

Text.ToBinary

Text.ToList

Text.Trim

Text.TrimEnd

Text.TrimStart

Text.Upper

TextEncoding.Ascii

TextEncoding.BigEndianUnicode

TextEncoding.Unicode

TextEncoding.Utf8

TextEncoding.Utf16

TextEncoding.Windows

Time functions

Time.EndOfHour

Time.From

Time.FromText

Time.Hour

Time.Minute

Time.Second

Time.StartOfHour

Time.ToRecord

Time.ToText

#time

Type functions

Type.AddTableKey

Type.ClosedRecord

Type.Facets

Type.ForFunction

Type.ForRecord

Type.FunctionParameters

Type.FunctionRequiredParameters

Type.FunctionReturn

Type.Is

Type.IsNullable

Type.IsOpenRecord

Type.ListItem

Type.NonNullable

Type.OpenRecord

Type.RecordFields

Type.ReplaceFacets

Type.ReplaceTableKeys

Type.TableColumn

Type.TableKeys

Type.TableRow

Type.TableSchema

Type.Union

Uri functions

Uri.BuildQueryString

Uri.Combine

Uri.EscapeDataString

Uri.Parts

Value functions

DirectQueryCapabilities.From

Embedded.Value

Precision.Decimal

Precision.Double

SqlExpression.SchemaFrom

SqlExpression.ToExpression

Value.As

Value.Add

Value.Compare

Value.Divide

Value.Equals

Value.Firewall

Value.FromText

Value.Is

Value.Metadata

Value.Multiply

Value.NullableEquals

Value.NativeQuery

Value.RemoveMetadata

Value.ReplaceMetadata

Value.ReplaceType

Value.Subtract

Value.Type

Variable.Value

Quick tour of the Power Query M formula language

Expressions, values, and let expression

Comments

Evaluation model

Operators

Type conversion

Metadata

Errors

© 2018 Microsoft