Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
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.
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.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.
DateTime.DateReturns the date part of a date and time, with the time removed.DateTime.FixedLocalNowReturns the current local date and time as one fixed value for the whole evaluation of a query.DateTime.FromConverts a value, such as a date, a time zone value or text, to a date and time.DateTime.LocalNowReturns the current date and time in the local time zone of the machine running the query.DateTime.TimeReturns the time part of a date and time, with the date removed.DateTime.ToTextFormats a date and time as text using a format string, such as "yyyy-MM-dd HH:mm".DateTimeZone.SwitchZoneMoves a datetime with a time zone to a different fixed zone offset, given in hours and optional minutes.DateTimeZone.UtcNowReturns the current date and time in UTC, the universal reference time, with a zone offset of zero.Time.FromConverts a value, such as a datetime or text, to a time of day.
Duration.DaysReturns the whole number of days in a duration, such as the days between two dates.Duration.FromConverts a number to a duration, where the number is read as days.Duration.HoursReturns the hours part of a duration, from 0 to 23, not counting whole days.Duration.MinutesReturns the minutes part of a duration, from 0 to 59, after whole hours are taken out.Duration.TotalDaysReturns the whole duration as a number of days, including any part of a day as a decimal.Duration.TotalHoursReturns the whole duration as a number of hours, including any part of an hour as a decimal.
Error.RecordCreates an error value with a reason, a message and detail, which you raise with the error keyword.Type.TableSchemaReturns a table that describes the columns of a table type: their names and their types.Value.IsTests whether a value is of a given type, such as number or text, and returns true or false.Value.TypeReturns the type of a value, such as number, text or a table type, so you can see what Power Query thinks it holds.
eachA short way to write a function that takes one argument, named _, and uses it in an expression.errorRaises an error on purpose, using an error record, so that the query stops with a clear message.ifChooses one of two values based on a test, and always needs an else part.letNames a series of steps so that each one can be read, and then gives the result in the in clause.otherwiseGives a fallback value to use when the expression after try fails.tryEvaluates an expression and, instead of failing, returns a record that says whether it worked and what the value or error was.typeNames a type, such as number, text or date, so you can tell Power Query what a value or column holds.
List.ContainsReturns true if a value appears anywhere in a list.List.ContainsAnyReturns true if any of several values appears in a list.List.DatesBuilds a list of dates: a start date, a number of dates, and the gap between them.List.DifferenceReturns the items in the first list that are not in the second list.List.FirstReturns the first item in a list.List.FirstNReturns the first few items of a list, either a set number or all the items at the start that pass a test.List.IntersectReturns the values that appear in every one of the lists you give.List.LastReturns the last item in a list.List.LastNReturns the last few items of a list, either a set number or all the items at the end that pass a test.List.NumbersBuilds a list of numbers: a starting number, how many numbers, and an optional step between them.List.PositionsReturns the position number of every item in a list, starting from zero.List.RangeReturns a section of a list, starting at a position and running for a number of items.List.RepeatRepeats a list a set number of times and joins the copies into one list.List.SkipReturns a list with the first items removed, either a set number or the run of items at the start that pass a test.List.UnionCombines several lists into one list of the values that appear in any of them, with duplicates removed.
List.AllTrueReturns true only if every item in a list of logical values is true.List.AnyTrueReturns true if at least one item in a list of logical values is true.List.AverageReturns the mean (average) of the numbers in a list.List.CountReturns how many items are in a list, including any nulls.List.MaxReturns the largest value in a list.List.MedianReturns the middle value of a list of numbers, half of the items above it and half below.List.MinReturns the smallest value in a list.List.ProductMultiplies all the numbers in a list together.List.StandardDeviationReturns a standard deviation, a measure of how far the numbers in a list spread out from their average.List.SumAdds up all the numbers in a list.
List.AccumulateWalks through a list one item at a time, carrying a running value from each step to the next.List.BufferLoads a list into memory once, so later steps reuse the same values instead of recalculating them.List.CombineJoins a list of lists into one list, end to end.List.DistinctReturns a list with duplicate values removed, keeping one copy of each value.List.GenerateBuilds a list by starting from an initial value and repeatedly applying a rule until a condition fails.List.RemoveNullsRemoves every null (empty) item from a list.List.ReplaceValueReplaces each item that matches a value with a new value, using a replacer function to decide what counts as a match.List.ReverseReturns the items of a list in the opposite order.List.SelectKeeps only the items in a list for which a condition is true.List.SortReturns the items of a list in order, smallest to largest by default.List.TransformApplies a function to every item in a list and returns a new list of the results.List.ZipPairs up items from several lists by position, so the first items go together, then the second items, and so on.
Number.AbsReturns the absolute value of a number, which is its size without its sign.Number.FromConverts a value of another type, such as text or a logical, to a number.Number.FromTextReads a number from text, using the separators of the culture you name.Number.IntegerDivideDivides one number by another and keeps only the whole number of times it fits.Number.IsNaNTests whether a number is NaN, which stands for "not a number" and comes from an invalid calculation.Number.ModReturns the remainder left after dividing one number by another.Number.PowerRaises a number to a power, such as a growth factor for compounding over several periods.Number.RoundRounds a number to a set number of decimal places, using a rounding rule you can choose.Number.RoundDownRounds a number down to the next value at a set number of digits.Number.RoundUpRounds a number up to the next value at a set number of digits.Number.SignReturns -1 for a negative number, 1 for a positive number and 0 for zero.Number.SqrtReturns the square root of a number.Number.ToTextFormats a number as text, using a format string such as a fixed number of decimal places or a currency style.
Record.AddFieldReturns a copy of a record with one new field added.Record.CombineJoins a list of records into one record, with all their fields together.Record.FieldReturns the value of one named field in a record.Record.FieldNamesReturns the names of all the fields in a record, as a list of text.Record.FieldValuesReturns the values of all the fields in a record, as a list.Record.FromTableBuilds a record from a table with a Name column and a Value column.Record.HasFieldsReturns true if a record contains all of the fields you name.Record.RemoveFieldsReturns a copy of a record without the fields you name.Record.RenameFieldsReturns a copy of a record with some of its fields given new names.Record.SelectFieldsReturns a copy of a record that keeps only the fields you name.Record.ToTableConverts a record into a two-column table, with one row for each field, a Name column and a Value column.Record.TransformFieldsReturns a copy of a record in which chosen fields have been changed by a function.
Combiner.CombineTextByDelimiterReturns a combiner that joins several text values into one, with a chosen delimiter between them.Comparer.OrdinalIgnoreCaseA comparer that treats text as equal when the letters match, ignoring capital and lower case.Replacer.ReplaceTextReplaces part of a text value, every place where the old text appears, with the new text.Replacer.ReplaceValueReplaces a whole value with a new value when it matches exactly, such as turning "N/A" into null.Splitter.SplitByNothingReturns a splitter that does not split at all: the whole text stays as one piece.Splitter.SplitTextByDelimiterReturns a splitter that cuts text into pieces wherever a chosen delimiter appears, such as a comma, a tab or a semicolon.Splitter.SplitTextByEachDelimiterReturns a splitter that cuts text at each of several delimiters in turn, using a list of delimiter values.Splitter.SplitTextByPositionsReturns a splitter that cuts text at fixed character positions, for fixed-width files and layouts without delimiters.
Table.FromColumnsBuilds a table from a list of columns, where each column is a list of values.Table.FromListTurns a list into a table with one column, or splits each item into several columns with a splitter.Table.FromRecordsBuilds a table from a list of records, where each record is one row with named fields.Table.FromRowsBuilds a table from a list of rows, where each row is itself a list of values.Table.ToColumnsConverts a table into a list of columns, where each column is a list of its values.Table.ToListJoins each row into one text value, using a combiner you choose, and returns the list of those values.Table.ToRecordsConverts a table into a list of records, one record for each row, with the column names as field names.Table.ToRowsConverts a table into a list of rows, where each row is a list of its values.
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.CombineStacks several tables with matching columns into one longer table.Table.ExpandListColumnTurns each item in a column of lists into a row of its own.Table.ExpandRecordColumnOpens a column of records, such as the details inside a JSON file, and turns the chosen fields into columns.Table.ExpandTableColumnOpens a column of nested tables and turns the chosen columns into normal columns, one row for each nested row.Table.JoinJoins two tables on matching key columns and returns one flat table with the columns of both.Table.NestedJoinMatches rows from two tables and adds a column that holds the matching rows from the second table, which you can then expand.
Table.BufferLoads the table into memory once so that later steps reuse it, and fixes the row order from that point.Table.DemoteHeadersTurns the current column headers into the first row of data and gives the columns generic names.Table.PivotTurns the values of one column into new column headers, filled with the values from another column.Table.ProfileReturns a table of statistics about each column, such as counts of blank and distinct values.Table.PromoteHeadersUses the first row of the table as the column headers.Table.TransposeSwaps rows and columns, so each column becomes a row and each row becomes a column.Table.UnpivotTurns several columns into rows, with one row for each combination of a column and its value.Table.UnpivotOtherColumnsTurns every column except the ones you keep into rows.
Table.DistinctRemoves duplicate rows, or keeps the first row for each distinct value in the columns you name.Table.FirstNKeeps the first rows, either a set number or while a condition holds.Table.GroupGroups rows by one or more key columns and calculates totals or counts for each group.Table.IsEmptyReturns true if the table has no rows, and false if it has at least one.Table.LastNKeeps the last rows, either a set number or while a condition holds from the bottom.Table.MaxReturns the row that has the highest value in a column.Table.MinReturns the row that has the lowest value in a column.Table.RemoveFirstNRemoves the first rows, by a count or until a test stops being true.Table.RemoveLastNRemoves the last rows, by a count or while a test holds from the bottom up.Table.RemoveMatchingRowsRemoves every row that matches one of the sample records in a list.Table.RemoveRowsRemoves a block of rows by position, starting at the row position you give.Table.RemoveRowsWithErrorsDrops every row that has an error in one or more of the columns you check.Table.RowCountReturns how many rows a table has.Table.SelectRowsKeeps only the rows where a condition is true.Table.SelectRowsWithErrorsKeeps only the rows that contain an error in one or more columns.Table.SkipDrops the first rows, either a set number or while a condition holds, and keeps the rest.Table.SortPuts the rows in order by one or more columns, ascending or descending.
Table.FillDownFills blank cells in the listed columns with the value from the row above.Table.FillUpFills blank cells in the listed columns with the value from the row below.Table.ReplaceErrorValuesReplaces error values in the columns you name with a value you choose.Table.ReplaceValueReplaces every occurrence of one value with another, in the columns you list.
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.