Downloads

Power Query M functions

The formula language behind Power Query in Excel, Power BI and Fabric: shape and clean data before it loads. 226 functions, grouped by what you use them for.

Data sources

Csv.DocumentReads comma-separated (or other delimited) text from a file or binary content and returns a table.Excel.CurrentWorkbookReads the tables inside the Excel workbook that holds the query, so you can use data already typed in the same file.Excel.WorkbookReads an Excel file, given as binary content, and returns a table listing its sheets and tables, from which you pick the data.File.ContentsReads one file from disk and returns its content as binary, ready to be read by a file-type function.Folder.ContentsLists everything in a folder, both files and subfolders, so you can see the folder's structure before you choose what to load.Folder.FilesLists the files in a folder, one row per file, with the file's name, extension, dates and its content as binary.Json.DocumentReads JSON text, from a file or a web response, and returns it as lists and records that you can expand into a table.Lines.FromBinaryReads binary content, such as a plain text file, and returns it as a list of lines, one item for each line.OData.FeedConnects to an OData service, a web data feed used by many business applications, and returns the tables it publishes.Sql.DatabaseConnects to a SQL Server database and returns a navigation table from which you pick the tables or views to load.Sql.DatabasesLists the databases on a SQL Server, so you can see what is there before you connect to one.Value.NativeQuerySends a SQL statement you write to a database connection and returns the result as a table.Web.ContentsFetches the content of a web address (a web page, file or API response) as binary, with optional query parameters, headers and timeout.Web.PageReads an HTML web page and returns the tables it contains, so you can pick the one you need.

Date

Date.AddDaysAdds a number of days to a date, or subtracts them when the number is negative.Date.AddMonthsAdds a number of months to a date. When the day does not exist in the new month, the result is the last day of that month.Date.AddQuartersAdds a number of quarters to a date, where one quarter is three months.Date.AddWeeksAdds a number of weeks to a date, or subtracts them when the number is negative.Date.AddYearsAdds a number of years to a date. A February 29 that falls in a non-leap year becomes February 28.Date.DayReturns the day of the month of a date or datetime, from 1 to 31.Date.DayOfWeekReturns the day of the week of a date as a number, counted from the day you name as the first day of the week.Date.DayOfWeekNameReturns the name of the day of the week of a date, such as "Thursday".Date.DayOfYearReturns the position of a date in its year, from 1 on January 1 to 365 or 366 on December 31.Date.DaysInMonthReturns the number of days in the month that contains a date.Date.EndOfMonthReturns the last day of the month that contains a date.Date.EndOfQuarterReturns the last day of the calendar quarter that contains a date.Date.EndOfYearReturns December 31 of the year that contains a date.Date.FromConverts a value such as a date and time, or text, to a date with no time part.Date.FromTextReads a date from text, using a format and culture you specify.Date.IsLeapYearTests whether the year of a date is a leap year, which has 366 days.Date.MonthReturns the calendar month number of a date or datetime, from 1 for January to 12 for December.Date.MonthNameReturns the name of the month of a date, such as "October".Date.QuarterOfYearReturns the calendar quarter of a date, from 1 for January to March through 4 for October to December.Date.StartOfMonthReturns the first day of the month that contains a date.Date.StartOfQuarterReturns the first day of the calendar quarter that contains a date.Date.StartOfWeekReturns the first day of the week that contains a date, using the day you name as the first day of the week.Date.StartOfYearReturns January 1 of the year that contains a date.Date.ToTextFormats a date as text using a format string, such as "yyyy-MM-dd" or "MMM yyyy".Date.WeekOfYearReturns the week number of a date within its year.Date.YearReturns the calendar year of a date or datetime.

Date and time

Duration

Errors and types

Language

List: pick and build

List: summarize

List: transform and filter

Number

Record

Splitters and combiners

Table: build and convert

Table: columns

Table.AddColumnAdds a new column whose value is calculated for each row.Table.AddIndexColumnAdds a column that numbers the rows, from 0 by default, or from a start value and step you choose.Table.ColumnCountReturns how many columns a table has.Table.ColumnNamesReturns the column names of a table as a list.Table.CombineColumnsJoins several columns into one new column, using a combiner such as Combiner.CombineTextByDelimiter.Table.DuplicateColumnCopies a column into a new column with a name you choose.Table.PrefixColumnsAdds a prefix and a dot to every column name in the table.Table.RemoveColumnsDeletes one or more columns from a table.Table.RenameColumnsChanges the names of columns, leaving the values as they are.Table.ReorderColumnsMoves the named columns to the front, in the order you list them.Table.SchemaReturns a table that describes each column, including its name, its type and whether it allows blanks.Table.SelectColumnsKeeps only the columns you name, in the order you name them.Table.SplitColumnSplits one column into several columns, by a delimiter, by position or by a splitter you define.Table.TransformColumnNamesChanges every column name with one function, such as trimming spaces or changing capital letters.Table.TransformColumnsChanges the values in one or more columns, using a function you choose for each value.Table.TransformColumnTypesSets the data type of one or more columns, such as text, number, date or whole number.Table.TransformRowsApplies a function to each row, given as a record, and returns a list of the results.

Table: combine and join

Table: headers and shape

Table: rows

Table: values and fill

Text

Text.AfterDelimiterReturns the part of a text value that comes after a delimiter.Text.BeforeDelimiterReturns the part of a text value that comes before a delimiter.Text.BetweenDelimitersReturns the text that sits between a start delimiter and an end delimiter.Text.CleanRemoves non-printing control characters, such as line breaks and tabs, from a text value.Text.CombineJoins a list of text values into one text value, with a separator between them.Text.ContainsTests whether a text value contains a given piece of text.Text.EndReturns the last characters of a text value.Text.EndsWithTests whether a text value ends with a given piece of text.Text.FormatBuilds a text message by putting values into numbered placeholders.Text.FromConverts a number, date, time or logical value to text.Text.InsertInserts a piece of text into a value at a zero-based position.Text.LengthCounts the characters in a text value.Text.LowerConverts all letters in a text value to lower case.Text.MiddleReturns characters from the middle of a text value, starting at a zero-based position.Text.PadEndAdds characters to the end of a text value until it reaches a set length.Text.PadStartAdds characters to the start of a text value until it reaches a set length.Text.PositionOfReturns the zero-based position where a piece of text first (or last) appears in a value.Text.ProperCapitalizes the first letter of each word and lower cases the rest.Text.RemoveRemoves every occurrence of the listed characters from a text value.Text.ReplaceReplaces every occurrence of one piece of text with another.Text.ReverseReturns the characters of a text value in reverse order.Text.SelectKeeps only the listed characters from a text value and removes the rest.Text.SplitSplits a text value into a list of pieces at each separator.Text.StartReturns the first characters of a text value.Text.StartsWithTests whether a text value begins with a given piece of text.Text.ToListSplits a text value into a list with one character in each item.Text.TrimRemoves spaces, tabs and line breaks from the start and end of a text value.Text.TrimEndRemoves spaces from the end of a text value only.Text.TrimStartRemoves spaces from the start of a text value only, leaving the end alone.Text.UpperConverts all letters in a text value to upper case.