M functions

Every Power Query M function that Open Query, OpenBI’s M engine, runs. The list is generated from the engine’s own library. Each has a page of its own: its syntax and parameters, what it returns, how it compares with Power Query, and examples with the results Open Query gives them. They work as in Power BI, apart from the differences their pages name; OpenBI’s own, marked OpenBI, run in OpenBI only.

To write queries, see Transform data. Steps that can be done in SQL are folded into the query DuckDB runs. Fuzzy matching, new in 0.3.75, has a section of its own below: Fuzzy matching.

A function given no culture writes and reads numbers and dates in the query’s culture (Culture.Current): the model’s Locale for import, else the model’s own culture, else en-US, as in Power BI. Conversions to and from local time use the time zone of the computer that runs the query: the studio’s, or the server’s when a model refreshes there.

OpenBI Studio 0.3.79's M engine has 424 functions in 15 groups: 415 of Power Query M's and 9 of OpenBI's own (OpenBI). Each name opens the function's page: its syntax and parameters, what it returns, how it compares with Power Query, and examples with the results OpenBI's M engine gives them.

Table

100 functions.

Table.AddColumn: Adds a column whose value a function works out from each row.
Table.AddFuzzyClusterColumn: Adds a column that gives each spelling of a text the spelling its group of look-alikes is known by.
Table.AddIndexColumn: Numbers the rows in a new column: from 0 by ones, or from a start and by a step you choose.
Table.AddKey: Marks one or more columns as a key of the table: their values are said to tell the rows apart.
Table.AddRankColumn: Ranks the rows by one or more columns and puts each row's rank in a new column.
Table.AggregateTableColumn: Sums, counts or otherwise sums up each row's nested table into new columns, one per aggregation.
Table.AlternateRows: Keeps the first rows, then skips and keeps rows in turn, a set number of each, to the end of the table.
Table.ApproximateRowCount: Counts a table's rows, for sources where an estimate is good enough.
Table.Buffer: Reads all of a table's cells and keeps them in memory, giving the same table back.
Table.Column: A column's values, top to bottom, as a list.
Table.ColumnCount: How many columns a table has.
Table.ColumnNames: The names of a table's columns, in their order, as a list of texts.
Table.ColumnsOfType: The names of the columns whose type is one of the types you list.
Table.Combine: Puts the rows of several tables one after the other in a single table.
Table.CombineColumns: Merges several columns into one, with a function that makes one value of the row's values.
Table.CombineColumnsToRecord: Gathers several columns into one column of records, a field per column.
Table.Contains: Tells whether a table has a row with the values of a record.
Table.ContainsAll: Tells whether a table has a matching row for every record of a list.
Table.ContainsAny: Tells whether a table has a matching row for at least one record of a list.
Table.DemoteHeaders: Moves the column names down into a first row and names the columns Column1, Column2 and so on.
Table.Distinct: Removes duplicate rows: whole rows, or rows that repeat the values of the columns you name.
Table.DuplicateColumn: Copies a column into a new one with another name, at the end of the table.
Table.ExpandListColumn: Gives each item of a column of lists a row of its own, the row's other columns repeated.
Table.ExpandRecordColumn: Spreads a column of records into one column per field you name.
Table.ExpandTableColumn: Spreads a column of nested tables into ordinary columns: each nested row becomes a row of its own.
Table.FillDown: Fills the nulls of the columns you name with the value above them.
Table.FillUp: Fills the nulls of the columns you name with the value below them.
Table.FindText: Keeps the rows where some cell has the text you look for in it.
Table.First: The first row of a table, as a record.
Table.FirstN: The first rows of a table: a number of them, or as many as meet a condition from the top.
Table.FirstValue: The value in the first column of a table's first row.
Table.FromColumns: Makes a table from lists, each list a column.
Table.FromList: Makes a table from a list, splitting each item into the row's columns.
Table.FromRecords: Makes a table from a list of records: each record a row, each field a column.
Table.FromRows: Makes a table from lists, each list a row.
Table.FromValue: Makes a table out of any value: a single value, a list, or a record's fields.
Table.FuzzyGroup: Groups rows whose key texts are spelled alike, not only the same, and works out a column per group.
Table.FuzzyJoin: Joins two tables on key texts that are spelled alike rather than the same, into one flat table.
Table.FuzzyNestedJoin: Joins two tables on key texts that are spelled alike and puts each row's matches in a new column, as a nested table.
Table.Group: Groups rows that share the values of key columns and works out a column per group, such as a total or a count.
Table.HasColumns: Tells whether a table has a column, or every column of a list.
Table.InsertRows: Puts new rows into a table at a position you give.
Table.IsDistinct: Tells whether a table has no duplicate rows, either whole or by the columns you name.
Table.IsEmpty: Tells whether a table has no rows.
Table.Join: Joins two tables on key columns into one flat table: the columns of both, side by side.
Table.Keys: Lists the keys a table has been given, each as a record of its columns and whether it is primary.
Table.Last: The last row of a table, as a record.
Table.LastN: The last rows of a table: a number of them, or as many as meet a condition from the bottom up.
Table.MatchesAllRows: Tells whether every row of a table meets a condition.
Table.MatchesAnyRows: Tells whether at least one row of a table meets a condition.
Table.Max: The row with the highest value of a column, or of anything worked out from a row, as a record.
Table.MaxN: The top rows by a column or by a value worked out per row: a number of them, or as many as meet a condition.
Table.Min: The row with the lowest value of a column, or of anything worked out from a row, as a record.
Table.MinN: The bottom rows by a column or by a value worked out per row: a number of them, or as many as meet a condition.
Table.NestedJoin: Joins two tables on key columns and puts each row's matches from the second table in a new column, as a table of their own.
Table.Partition: Shares a table's rows out over a set number of tables, by a number worked out from one column's value.
Table.Pivot: Turns the values of one column into new columns, filled from another column: the opposite of unpivoting.
Table.PositionOf: Where a row is in a table, counting from 0: the first match, the last one, or all of them.
Table.PrefixColumns: Puts a prefix and a dot in front of every column name.
Table.PromoteHeaders: Makes the first row the header: its values name the columns, and the row itself goes.
Table.Range: The rows from a position on: all of them to the end, or a number of them.
Table.RemoveColumns: The table without the columns you name.
Table.RemoveFirstN: Drops rows from the top: a number of them, or as long as a condition holds.
Table.RemoveLastN: Drops rows from the bottom: a number of them, or as long as a condition holds.
Table.RemoveMatchingRows: The table without the rows equal to any of the rows you list.
Table.RemoveRows: Takes out a run of rows at a position: one row, or a number of them.
Table.RemoveRowsWithErrors: The table without the rows that have an error in a cell: in any column, or in the columns you name.
Table.RenameColumns: Gives columns new names, from pairs of old name and new name.
Table.ReorderColumns: Puts the columns in the order you list them.
Table.Repeat: Stacks copies of a table's rows below each other, as many copies as you ask for.
Table.ReplaceErrorValues: Puts a value in place of every error in the columns you name.
Table.ReplaceKeys: Sets a table's keys, in place of any it had.
Table.ReplaceMatchingRows: Swaps every row that equals a given row for another row.
Table.ReplaceRows: Takes out a run of rows at a position and puts other rows in their place.
Table.ReplaceValue: Finds a value, or a piece of text, in the columns you name and puts another in its place.
Table.ReverseRows: Turns a table upside down: the last row first, the first row last.
Table.RowCount: How many rows a table has.
Table.Schema: A table that describes a table's columns: a row per column with its name, place, type and whether it takes null.
Table.SelectColumns: Keeps only the columns you name, in the order you name them.
Table.SelectMatchingRows: Keeps only the rows equal to one of the rows you list.
Table.SelectRows: Keeps the rows for which a condition is true.
Table.SelectRowsWithErrors: Keeps only the rows that have an error in a cell: in any column, or in the columns you name.
Table.Skip: The rows after the first ones: after a number of them, or after the top rows for which a condition holds.
Table.Sort: Puts the rows in order by one or more columns, each ascending or descending.
Table.Split: Cuts a table into tables of a given number of rows each.
Table.SplitAt: Cuts a table in two after a number of rows.
Table.SplitColumn: Splits one column into several, with a splitter such as one for a delimiter or for positions.
Table.StopFolding: Gives back the same table; in Power Query, a mark that keeps the later steps from folding into the data source's query.
Table.ToColumns: A table's columns as a list of lists: one list of values per column.
Table.ToList: One value per row: each row's values made into one by a function you give.
Table.ToRecords: Each row of a table as a record, in a list; the column names become the field names.
Table.ToRows: A table's rows as a list of lists, each row's values in column order.
Table.TransformColumnNames: Renames every column with a function of its name.
Table.TransformColumns: Changes the values of columns, with a function for each column and, if you like, one for all the others.
Table.TransformColumnTypes: Gives columns a type and converts their values to it: text to numbers, dates, logicals and more.
Table.TransformRows: A list made by a function of each row: one value per row, in the table's order.
Table.Transpose: Swaps rows and columns: each row becomes a column and each column a row.
Table.Unpivot: Turns the columns you name into rows of two columns: the old column's name and its value.
Table.UnpivotOtherColumns: Turns every column except those you name into rows of two columns: the old column's name and its value.
#table: Builds a table in M from its column names (or a table type) and its rows, each a list of values.

List

58 functions.

List.Accumulate: Walks a list from start to end and carries a running value along, item by item.
List.AllTrue: Whether every item of a list of logical values is true.
List.Alternate: Every so many items of a list: skip some, keep some, over and over.
List.AnyTrue: Whether at least one item of a list of logical values is true.
List.Average: The mean of a list's values: numbers, durations, dates or datetimes, nulls left out.
List.Buffer: A list worked out once and kept in memory, so later steps read the same values without working them out again.
List.Combine: Several lists joined end to end into one.
List.Contains: Whether a value is among a list's items.
List.ContainsAll: Whether every one of several values is in a list.
List.ContainsAny: Whether at least one of several values is in a list.
List.Count: How many items a list holds, nulls included.
List.Dates: A list of dates from a start, a set count of them, each a step from the last.
List.DateTimes: A list of datetimes from a start, a set count of them, each a step from the last.
List.DateTimeZones: A list of datetimes with a time zone from a start, a set count of them, each a step from the last.
List.Difference: The items of one list that a second list does not take away, one for one.
List.Distinct: A list with each value once, in the order the values first appear.
List.Durations: A list of durations from a start, a set count of them, each a step from the last.
List.First: The first item of a list, or a default when the list is empty.
List.FirstN: The first items of a list: a set number of them, or as long as a condition holds.
List.Generate: Makes a list one value at a time, from a start, for as long as a condition holds.
List.InsertRange: A list with other items put in at a position.
List.Intersect: The values found in every one of several lists.
List.IsEmpty: Whether a list has no items at all.
List.Last: The last item of a list, or a default when the list is empty.
List.LastN: The last items of a list: a set number of them, or as long as a condition holds from the end.
List.MatchesAll: Whether every item of a list meets a condition.
List.MatchesAny: Whether at least one item of a list meets a condition.
List.Max: The largest value of a list, nulls left out, or a default for an empty list.
List.Median: The middle value of a list once it is sorted, nulls left out.
List.Min: The smallest value of a list, nulls left out, or a default for an empty list.
List.NonNullCount: How many items of a list are not null.
List.Numbers: A list of numbers from a start, a set count of them, each a step from the last.
List.PositionOf: Where a value sits in a list: its position counted from 0, or every position it has.
List.Product: All the numbers of a list multiplied together, nulls left out.
List.Random: A list of random numbers from 0 up to 1, new ones on each run unless you give a seed.
List.Range: A run of items from the middle of a list: from a position, a set number of them or all the rest.
List.RemoveFirstN: A list without its first items: a set number of them, or as long as a condition holds.
List.RemoveItems: A list without any item that appears in a second list.
List.RemoveLastN: A list without its last items: a set number of them, or as long as a condition holds from the end.
List.RemoveMatchingItems: A list without any item that matches one of a second list, optionally by your own rule of equality.
List.RemoveNulls: A list without its null items.
List.RemoveRange: A list without a run of items: from a position, one item or a set number of them.
List.Repeat: A list repeated a number of times, end to end.
List.ReplaceValue: A list with a value replaced, by a replacer that says how.
List.Reverse: A list in the opposite order.
List.Select: The items of a list that meet a condition, in their order.
List.Single: The one item of a list that must hold exactly one.
List.SingleOrDefault: The one item of a list, or a default when the list is empty.
List.Skip: A list without its first items: a set number of them, or as long as a condition holds.
List.Sort: A list in order: ascending by default, descending, by a key or by a comparer.
List.Split: A list cut into pages of a set size.
List.StandardDeviation: How far a list's numbers spread around their mean: the sample standard deviation.
List.Sum: The total of a list of numbers or durations, nulls left out.
List.Times: A list of times of day from a start, a set count of them, each a step from the last.
List.Transform: A new list with a function applied to each item.
List.TransformMany: Each item expanded into several, then each pair of item and new value turned into one result.
List.Union: Several lists put together so that a value they share isn't doubled, in the order the values first appear.
List.Zip: Several lists read side by side: a list per position, holding each list's item at that position.

Text and characters

41 functions.

Character.FromNumber: The character with a given Unicode number, as a text of one character.
Character.ToNumber: The Unicode number of a character: the reverse of Character.FromNumber.
Text.AfterDelimiter: What comes after a delimiter in a text: after its first occurrence, or after the one you choose.
Text.At: The character at a position of a text, counted from 0.
Text.BeforeDelimiter: What comes before a delimiter in a text: before its first occurrence, or before the one you choose.
Text.BetweenDelimiters: The part of a text between a start delimiter and the end delimiter that follows it.
Text.Clean: A text without its control characters: line breaks, tabs and the other invisible codes below a space.
Text.Combine: Texts from a list joined into one, with a separator between them if you give one.
Text.Contains: Whether a text has a piece of text anywhere in it.
Text.End: The last characters of a text.
Text.EndsWith: Whether a text ends with a given piece of text.
Text.Format: A text with placeholders filled in from a list or a record: #{0} takes the first item, #[Name] a field.
Text.From: Any simple value written as text: numbers, dates, times, durations, true and false.
Text.FromBinary: Bytes read as text in an encoding, UTF-8 unless you say otherwise.
Text.Insert: A text with another text put in at a position.
Text.Length: How many characters a text has.
Text.Lower: A text in small letters.
Text.Middle: Characters from the middle of a text, from a position on, forgiving a count that runs past the end.
Text.NewGuid: A new random identifier (a GUID) as text, different on each call.
Text.PadEnd: A text made a given length by adding a character at the end.
Text.PadStart: A text made a given length by adding a character in front, such as zeros before a number.
Text.PositionOf: Where a piece of text is found in a text, counted from 0: the first place, the last, or every place.
Text.Proper: A text with each word starting in a capital and the rest in small letters.
Text.Range: Characters from a position of a text on, strictly inside the text.
Text.Remove: A text with every occurrence of the characters you name taken out.
Text.RemoveRange: A text with a run of characters cut out at a position.
Text.Repeat: A text written a number of times in a row.
Text.Replace: A text with every occurrence of one piece of text replaced by another.
Text.ReplaceRange: A text with the characters at a position replaced by another text, which may be longer or shorter.
Text.Reverse: A text with its characters in the opposite order.
Text.Select: A text with only the characters you name kept: the opposite of Text.Remove.
Text.Split: A text cut into a list of pieces at every occurrence of a separator.
Text.SplitAny: A text cut into a list of pieces wherever any of the given characters appears.
Text.Start: The first characters of a text.
Text.StartsWith: Whether a text begins with a given piece of text.
Text.ToBinary: A text as bytes in an encoding, UTF-8 unless you say otherwise.
Text.ToList: A text as a list of its characters.
Text.Trim: A text without the white space, or other characters you name, at its start and end.
Text.TrimEnd: A text without the white space, or other characters you name, at its end.
Text.TrimStart: A text without the white space, or other characters you name, at its start.
Text.Upper: A text in capital letters.

Record

17 functions.

Record.AddField: A record with one more field, added at the end.
Record.Combine: Several records merged into one; where two have the same field, the later record's value wins.
Record.Field: The value of a record's field by its name, a name that may be worked out in the query.
Record.FieldCount: How many fields a record has.
Record.FieldNames: The names of a record's fields, in the record's order.
Record.FieldOrDefault: The value of a record's field by name, or a default when the record has no such field.
Record.FieldValues: The values of a record's fields as a list, in the record's order.
Record.FromList: A record made from a list of values and the names to give them.
Record.FromTable: A record built from a table of Name and Value columns: the reverse of Record.ToTable.
Record.HasFields: Whether a record has a field of a given name, or every one of several names.
Record.RemoveFields: A record without the fields you name.
Record.RenameFields: A record with some fields given new names, their values and places kept.
Record.ReorderFields: A record with the fields you name put in the order you give.
Record.SelectFields: A record with only the fields you name, in the order you name them.
Record.ToList: A record's values as a list, without the field names.
Record.ToTable: A record turned into a table of two columns, Name and Value, a row per field.
Record.TransformFields: A record with some fields' values changed by functions you give, one function per field.

Number

37 functions.

Number.Abs: A number without its minus sign: how far it lies from zero.
Number.Acos: The angle, in radians, whose cosine is the given number.
Number.Asin: The angle, in radians, whose sine is the given number.
Number.Atan: The angle, in radians, whose tangent is the given number: the angle of a slope from its steepness.
Number.BitwiseAnd: Combines two whole numbers bit by bit, keeping only the bits set in both.
Number.BitwiseOr: Combines two whole numbers bit by bit, keeping every bit set in either.
Number.BitwiseShiftLeft: Moves a whole number's bits to the left: each step doubles it.
Number.BitwiseShiftRight: Moves a whole number's bits to the right: each step halves it, dropping what falls off.
Number.BitwiseXor: Combines two whole numbers bit by bit, keeping the bits set in one of them but not both.
Number.Combinations: How many ways there are to choose a number of items from a set, when the order of the choice doesn't matter.
Number.Cos: The cosine of an angle given in radians.
Number.Exp: The number e, about 2.718, raised to a power: growth that compounds continuously.
Number.Factorial: 1 × 2 × 3 … up to the number: how many orders that many things can be put in.
Number.From: Turns a value into a number: text, a logical, a date or datetime, a time or a duration.
Number.FromText: Reads a number written as text, in the query's culture or in one you name.
Number.IntegerDivide: How many whole times one number goes into another, the fraction cut off.
Number.IsEven: true when a number is even: divisible by 2 once its fraction is dropped.
Number.IsNaN: true when a number is NaN, the "not a number" that zero divided by zero gives.
Number.IsOdd: true when a number is odd: it leaves 1 over when divided by 2, once its fraction is dropped.
Number.Ln: The natural logarithm: the power e must be raised to for the number.
Number.Log: The logarithm of a number to a base you choose; to base e without one.
Number.Log10: The base-10 logarithm: the power 10 must be raised to for the number.
Number.Mod: What is left over when one number is divided by another a whole number of times.
Number.Permutations: How many ways there are to pick a number of items from a set in order, when the order counts.
Number.Power: A number multiplied by itself a number of times: x to the power of y.
Number.Random: A random number from 0 up to, but not including, 1.
Number.RandomBetween: A random number between a bottom and a top you give, fractions included.
Number.Round: Rounds a number to whole numbers or to a number of decimal places, halves to the even neighbour unless you pick another rule.
Number.RoundAwayFromZero: Rounds away from zero: positive numbers up, negative numbers down, to whole numbers or decimal places.
Number.RoundDown: Rounds down to the whole number, or the step of a decimal place, at or below a number: toward minus infinity.
Number.RoundTowardZero: Drops the fraction of a number, or the decimals past a place: positive numbers round down, negative ones up.
Number.RoundUp: Rounds up to the next whole number, or to the next step of a decimal place: toward plus infinity.
Number.Sign: Which side of zero a number is on: -1, 0 or 1.
Number.Sin: The sine of an angle given in radians.
Number.Sqrt: The square root of a number: the value that multiplied by itself gives the number.
Number.Tan: The tangent of an angle given in radians: how steep a slope at that angle is.
Number.ToText: Writes a number as text, as it is or in a format such as thousands separators or a fixed number of decimals.

Dates and times

75 functions.

Date.AddDays: Moves a date, datetime or datetimezone forward or back by a number of days.
Date.AddMonths: Moves a date, datetime or datetimezone by calendar months, keeping the day of the month where it can.
Date.AddQuarters: Moves a date, datetime or datetimezone by quarters of three calendar months.
Date.AddWeeks: Moves a date, datetime or datetimezone by whole weeks, seven days each.
Date.AddYears: Moves a date, datetime or datetimezone by whole calendar years.
Date.Day: The day of the month, 1 to 31, of a date, datetime or datetimezone.
Date.DayOfWeek: Where a day falls in its week, as a number from 0 (the week's first day) to 6.
Date.DayOfWeekName: The name of the weekday a date falls on, in the culture's language.
Date.DayOfYear: The day's number within its year: 1 for 1 January, up to 365 or 366.
Date.DaysInMonth: How many days the month of a date has: 28 to 31.
Date.EndOfDay: The last moment of the same day, for a datetime or datetimezone; a date comes back as it is.
Date.EndOfMonth: The last day of the month a date falls in.
Date.EndOfQuarter: The last day of the calendar quarter a date falls in.
Date.EndOfWeek: The last day of the week a date falls in.
Date.EndOfYear: 31 December of the year a date falls in.
Date.From: Turns a value into a date: a datetime loses its time, a number counts days, text is read.
Date.FromText: Reads a date from text: ISO dates, the culture's own way of writing them, or a format you give.
Date.IsLeapYear: Whether the year of a date has 366 days.
Date.Month: The month, 1 to 12, of a date, datetime or datetimezone.
Date.MonthName: The name of a date's month, in the culture's language.
Date.QuarterOfYear: The calendar quarter, 1 to 4, a date falls in.
Date.StartOfDay: Midnight at the start of the same day, for a datetime or datetimezone; a date comes back as it is.
Date.StartOfMonth: The first day of the month a date falls in.
Date.StartOfQuarter: The first day of the calendar quarter a date falls in.
Date.StartOfWeek: The first day of the week a date falls in.
Date.StartOfYear: 1 January of the year a date falls in.
Date.ToText: Writes a date as text, in the culture's short form or in a format you give.
Date.WeekOfMonth: Which week of its month a date falls in, counting the week that holds the 1st as week 1.
Date.WeekOfYear: Which week of its year a date falls in, counting the week that holds 1 January as week 1.
Date.Year: Which year a date, datetime or datetimezone falls in, such as 2024.
DateTime.AddZone: Gives a datetime an offset from UTC, turning it into a datetimezone with the same clock time.
DateTime.Date: The date part of a datetime or datetimezone, without the time of day.
DateTime.FixedLocalNow: The computer's date and time, read once and then the same for the whole evaluation.
DateTime.From: Turns a value into a datetime: a date gets midnight, a number counts days and parts of days, text is read.
DateTime.FromText: Reads a date and time from text: ISO form, the culture's own form, or a format you give.
DateTime.LocalNow: The computer's date and time right now, without a time zone.
DateTime.Time: The time of day of a datetime or datetimezone, without the date.
DateTime.ToText: Writes a datetime as text, in the culture's form or in a format you give.
DateTimeZone.FixedLocalNow: The computer's date, time and offset, read once and then the same for the whole evaluation.
DateTimeZone.FixedUtcNow: The date and time in UTC, read once and then the same for the whole evaluation.
DateTimeZone.From: Turns a value into a datetimezone; text with an offset is read with it.
DateTimeZone.FromText: Reads a date, time and offset from text, such as an ISO timestamp from an API.
DateTimeZone.LocalNow: The computer's date and time right now, with its offset from UTC.
DateTimeZone.RemoveZone: Drops the offset of a datetimezone, keeping its clock time as a datetime.
DateTimeZone.SwitchZone: Shows the same instant in another time zone: the clock time moves with the offset.
DateTimeZone.ToLocal: Shows a datetimezone's instant in the computer's own time zone, with the offset it has there.
DateTimeZone.ToText: Writes a datetimezone as text, offset included, in the culture's form or a format you give.
DateTimeZone.ToUtc: Shows a datetimezone's instant in UTC, with the offset +00:00.
DateTimeZone.UtcNow: The date and time right now in UTC, with the offset +00:00.
DateTimeZone.ZoneHours: The hours of a datetimezone's offset from UTC.
DateTimeZone.ZoneMinutes: The minutes part of a datetimezone's offset from UTC.
Duration.Days: The whole days of a duration, leaving out the hours, minutes and seconds beyond them.
Duration.From: Turns a value into a duration: a number is read as days, text as days, hours, minutes and seconds.
Duration.FromText: Reads a length of time from text such as 2.04:30:00 (2 days, 4 hours 30 minutes).
Duration.Hours: The hours part of a duration, 0 to 23, beyond its whole days.
Duration.Minutes: The minutes part of a duration, 0 to 59, beyond its whole hours.
Duration.Seconds: The seconds part of a duration, beyond its whole minutes, with any fraction of a second.
Duration.TotalDays: A duration's whole length counted in days, fractions included.
Duration.TotalHours: A duration's whole length counted in hours, fractions included.
Duration.TotalMinutes: A duration's whole length counted in minutes, fractions included.
Duration.TotalSeconds: A duration's whole length counted in seconds, fractions included.
Duration.ToText: Writes a duration as text: days, a point, then hours:minutes:seconds.
Time.EndOfHour: The last moment of the hour a time falls in, a tick before the next hour.
Time.From: Turns a value into a time of day: a datetime loses its date, a number's fraction is a part of a day.
Time.FromText: Reads a time of day from text, on a 24-hour clock or with AM and PM.
Time.Hour: The hour, 0 to 23, of a time, datetime or datetimezone.
Time.Minute: The minute, 0 to 59, of a time, datetime or datetimezone.
Time.Second: The second of a time, datetime or datetimezone, with any fraction of a second.
Time.StartOfHour: The start of the hour a time falls in: the minutes and seconds set to zero.
Time.ToText: Writes a time of day as text, in the culture's short form or a format you give.
#date: Builds a calendar date out of three numbers: year, month, day of the month.
#datetime: Makes a datetime, a date with a time of day and no time zone, from six numbers.
#datetimezone: Makes a datetimezone: a date and time together with its offset from UTC.
#duration: Builds a length of time out of four numbers: days, hours, minutes, seconds.
#time: Builds a clock time out of three numbers: hour, minute, second.

Values, types and errors

28 functions.

Byte.From: A value as a byte, a whole number from 0 to 255 (Byte.Type), its fraction rounded away.
Currency.From: A value as a fixed decimal number with four decimal places, the kind Currency.Type columns hold.
Decimal.From: A value as a decimal number (Decimal.Type), read in the query's culture or in one you name.
Double.From: A value as a double-precision floating-point number (Double.Type), the kind M uses for numbers by default.
Error.Record: Makes the record an error carries, ready to raise with error: a reason, a message and details.
Int16.From: A value as a 16-bit whole number (Int16.Type), its fraction rounded away.
Int32.From: A value as a 32-bit whole number (Int32.Type), its fraction rounded away.
Int64.From: A value as a whole number of the Int64.Type kind, the one Power Query's Whole number columns hold.
Int8.From: A value as a signed 8-bit whole number (Int8.Type), its fraction rounded away.
Logical.From: A value as true or false: numbers by whether they are zero, text by its words.
Logical.FromText: Reads the text true or false, in any mix of capitals, as a logical.
Logical.ToText: Turns true or false into text, for a label or a file.
Percentage.From: A value as a percentage number (Percentage.Type): 12.5 % is 0.125.
Single.From: A value as a single-precision floating-point number (Single.Type).
Value.Add: Adds two values the way the + operator does, in double or in decimal arithmetic.
Value.Compare: Which of two values comes first: -1, 0 or 1.
Value.Divide: Divides the first value by the second, as the / operator does, in double or in decimal arithmetic.
Value.Equals: true when two values match, by the same rules as the = operator.
Value.Is: Whether a value is of a type: number, text, date, list, table and the others.
Value.Metadata: The metadata record attached to a value with meta.
Value.Multiply: Multiplies two values as the * operator does, in double or in decimal arithmetic.
Value.NullableEquals: Compares two values as Value.Equals does, but answers null when either of them is null.
Value.RemoveMetadata: A value with its metadata taken off.
Value.ReplaceType: Gives a value a new type without changing its contents; for a table, new column names and types.
Value.Subtract: Subtracts the second value from the first, as the - operator does, in double or in decimal arithmetic.
Value.Type: The type of a value: type number, type text, a table's type with its columns, and so on.
Value.VersionIdentity: Which version of a versioned table a value reads: its version as text, or null for the table as it is now.
Value.Versions: The versions of a versioned table, such as a Delta table or a table of an OpenBI lake, each with its data.

Splitters, combiners, replacers and comparers

13 functions.

Combiner.CombineTextByDelimiter: Makes a function that joins a list of texts into one, with a separator between them.
Comparer.Equals: Asks a comparer whether two values are the same: true when it answers 0.
Comparer.FromCulture: Makes a comparer that orders and matches text by a culture's rules, with or without regard to case.
Comparer.Ordinal: Compares two values exactly, text character by character by its code: capitals before small letters.
Comparer.OrdinalIgnoreCase: Compares two values exactly, except that capitals and small letters count as the same.
Replacer.ReplaceText: Replaces every occurrence of a piece of text inside a text: the replacer for parts of values.
Replacer.ReplaceValue: Replaces a value when it equals the old one as a whole: the replacer for whole cells.
Splitter.SplitByNothing: Makes a function that does not split: it puts any value whole into a list of one.
Splitter.SplitTextByAnyDelimiter: Makes a function that cuts text wherever any of several separators occurs.
Splitter.SplitTextByDelimiter: Makes a function that cuts text into pieces at every occurrence of a separator, such as a comma.
Splitter.SplitTextByEachDelimiter: Makes a function that cuts text once at each separator of a list, in the order the list gives them.
Splitter.SplitTextByLengths: Makes a function that cuts text into pieces of given lengths, one after the other: fixed-width fields.
Splitter.SplitTextByPositions: Makes a function that cuts text at given character positions, each piece running to the next position.

Binary and lines

11 functions.

Binary.Buffer: A binary read once and kept in memory, so later steps don't fetch it from its source again.
Binary.Decompress: Compressed bytes unpacked: a GZip file or a raw Deflate stream.
Binary.FromList: Bytes put together from whole numbers of 0 to 255, each number one byte, in the list's order.
Binary.FromText: Bytes written as text, in Base64 or hexadecimal, turned back into a binary.
Binary.Length: How many bytes a binary has.
Binary.ToList: The bytes of a binary as a list of numbers, each from 0 to 255.
Binary.ToText: A binary written as text, in Base64 or hexadecimal, so it can travel where only text may go.
Lines.FromBinary: Bytes read as text and cut into a list of lines.
Lines.FromText: A text cut into a list of lines.
Lines.ToText: A list of lines joined into one text, each line followed by a line break.
#binary: A binary value written out in a query, from a list of byte values.

Files and documents

12 functions.

AzureStorage.BlobContents: One blob of Azure Blob Storage, by its address, as a binary to read with Csv.Document, Parquet.Document or another reader.
AzureStorage.Blobs: An Azure storage account's containers, or one container's blobs, as navigation tables.
AzureStorage.DataLake: The files of an Azure Data Lake Storage Gen2 container or folder, listed as Folder.Files lists a folder's.
AzureStorage.DataLakeContents: One file of Azure Data Lake Storage Gen2, by its address, as a binary for a reader.
Csv.Document: A CSV file or text split into a table: one row per line, one column per field, every value as text.
Excel.Workbook: An Excel workbook's sheets as a navigation table: pick a sheet by its name and take its Data.
File.Contents: A file on this computer or a share, as a binary for a reader such as Csv.Document or Excel.Workbook.
Folder.Contents: A folder's files and folders, one level only: a folder's Content is its own listing, to open in turn.
Folder.Files: Every file in a folder and in the folders below it, a row each, with its bytes in Content.
Json.Document: JSON read into M: objects become records, arrays lists, and numbers, texts, logicals and null as they are.
Parquet.Document: A Parquet file as a table, its columns typed as the file stores them.
Web.Contents: What a web address answers, as a binary: a file to read with Csv.Document, Json.Document or another reader.

Databases and lakes

11 functions, 2 of them OpenBI's own.

AmazonAthena.Databases: Amazon Athena's catalogs, databases and tables, through an Athena ODBC data source on this computer.
AnalysisServices.Database: The rows of a DAX query run on a semantic model: SQL Server or Azure Analysis Services, or a Power BI or Fabric semantic model.
DeltaLake.Table: A Delta table read from its folder: the rows of its latest version, typed as Power BI types Delta's columns.
DuckDB.Query OpenBI: A table from DuckDB SQL, run by the project's own engine: files, joins and DuckDB's functions in one query.
MySQL.Database: A MySQL or MariaDB database's tables and views to navigate, or the rows of a query in its own SQL.
Odbc.DataSource: Any database an ODBC driver on this computer reaches: its databases, schemas, tables and views to navigate.
Odbc.Query: The rows of a SQL statement that an ODBC data source runs, in its own SQL.
OpenBI.Lake OpenBI: An OpenBI lake's tables and views to navigate, as they are now or as they were at a version or a moment.
PostgreSQL.Database: A PostgreSQL database's tables and views to navigate, or the rows of a query in PostgreSQL's SQL.
Sql.Database: A SQL Server or Azure SQL database: its tables and views to navigate, or the rows of a SQL query you write.
Sql.Databases: The databases on a SQL Server that your sign-in may open, each with its tables and views to navigate.

Online services

15 functions, 3 of them OpenBI's own.

ActiveDirectory.Domains: An Active Directory forest's domains, their object categories and each category's objects with their attributes.
AzureDataExplorer.Contents: Azure Data Explorer (Kusto) and Fabric KQL databases: the databases on a cluster, the tables in a database, or the rows of a KQL query.
Bloomberg.HistoricalData OpenBI: Bloomberg's history of fields for securities between two dates, as Excel's BDH gives it.
Bloomberg.Query: The answer to a BQL query, Bloomberg's query language, as a table.
Bloomberg.ReferenceData OpenBI: Bloomberg's current values of fields for securities, as Excel's BDP gives them.
BQL.Query: The earlier name of Bloomberg.Query: a BQL query's answer as a table.
CommonDataService.Database: A Dataverse environment's tables (Dynamics 365, Power Apps) to navigate, read through its Web API.
Exchange.Contents: An Exchange Online mailbox's mail, calendar, contacts, tasks and meeting requests as tables.
GoogleAnalytics.Accounts: Your Google Analytics 4 properties, each with the dimensions and metrics it offers.
GoogleAnalytics.Report OpenBI: A Google Analytics 4 report: the dimensions and metrics you name, for a property, between two dates.
Kusto.Contents: Kusto's name for AzureDataExplorer.Contents: the databases on a cluster, the tables in a database, or the rows of a KQL query.
OData.Feed: An OData service: its entity sets to navigate, or one entity set's rows as a table.
Salesforce.Data: A Salesforce org's objects you may query, each object's records as a table.
Salesforce.Reports: A Salesforce org's reports, each report's detail rows as a table.
SharePoint.Tables: A SharePoint Online site's lists and libraries, each list's items as a table of its visible columns.

Python and R

2 functions.

Python.Execute: The data frames a Python script leaves behind, as a navigation table: the script runs in this computer's Python.
R.Execute: The data frames an R script leaves behind, as a navigation table: the script runs in this computer's R.

Images and colours

3 functions, all OpenBI's own.

Color.Name OpenBI: The name of the named colour nearest to a colour, given as #RRGGBB or as red, green and blue.
Image.Color OpenBI: One dominant colour of a picture, written #RRGGBB: the first, or the one at the rank you ask for.
Image.Colors OpenBI: The dominant colours of a picture, as a table: rank, colour, name, how many pixels and their share.

Privacy

1 function, all OpenBI's own.

Privacy.Anonymize OpenBI: Personal data in a table's columns found and replaced, redacted, masked or hashed as the table loads, so it never reaches the model.

Constants and types

88 values the library names, as Power Query M has them: Any.Type, Binary.Type, BinaryEncoding.Base64, BinaryEncoding.Hex, Byte.Type, Compression.Deflate, Compression.GZip, Compression.None, CsvStyle.QuoteAfterDelimiter, CsvStyle.QuoteAlways, Culture.Current, Currency.Type, Date.Type, DateTime.Type, DateTimeZone.Type, Day.Friday, Day.Monday, Day.Saturday, Day.Sunday, Day.Thursday, Day.Tuesday, Day.Wednesday, Decimal.Type, Double.Type, Duration.Type, ExtraValues.Error, ExtraValues.Ignore, ExtraValues.List, Function.Type, GroupKind.Global, GroupKind.Local, Int16.Type, Int32.Type, Int64.Type, Int8.Type, JoinKind.FullOuter, JoinKind.Inner, JoinKind.LeftAnti, JoinKind.LeftOuter, JoinKind.RightAnti, JoinKind.RightOuter, List.Type, Logical.Type, MissingField.Error, MissingField.Ignore, MissingField.UseNull, None.Type, Null.Type, Number.Epsilon, Number.NaN, Number.NegativeInfinity, Number.PI, Number.PositiveInfinity, Number.Type, Occurrence.All, Occurrence.First, Occurrence.Last, Order.Ascending, Order.Descending, Percentage.Type, Precision.Decimal, Precision.Double, QuoteStyle.Csv, QuoteStyle.None, RankKind.Competition, RankKind.Dense, RankKind.Ordinal, Record.Type, RelativePosition.FromEnd, RelativePosition.FromStart, RoundingMode.AwayFromZero, RoundingMode.Down, RoundingMode.ToEven, RoundingMode.TowardZero, RoundingMode.Up, Single.Type, Table.Type, Text.Type, TextEncoding.Ascii, TextEncoding.BigEndianUnicode, TextEncoding.Unicode, TextEncoding.Utf16, TextEncoding.Utf8, TextEncoding.Windows, Time.Type, Type.Type, #infinity, #nan.

Fuzzy matching

New in 0.3.75 Table.FuzzyJoin, Table.FuzzyNestedJoin, Table.FuzzyGroup and Table.AddFuzzyClusterColumn match texts that are spelled alike rather than the same, such as “BIO SUR” and “BIOSUR LTDA”. They take Power BI’s options: IgnoreCase, IgnoreSpace, Threshold (0.80 unless you give another), NumberOfMatches, SimilarityColumnName and TransformationTable; Culture and ConcurrentRequests are accepted and change nothing. Their similarities are Power BI Desktop’s: 1,453 cases were run in Power BI Desktop 2.159 and in OpenBI, with the same answers. A similarity column shows two decimals, cut rather than rounded (0.875 shows as 0.87), as in Desktop.

How two texts are compared, in short: each text is split into words, keeping letters and digits and dropping accents, with punctuation and spaces between words, and case ignored unless IgnoreCase is false; two words match when they are equal or a few letters apart; a word most rows of the right table have, such as “SA” or “LTDA”, weighs less than a rare one; and neighbouring words may be joined (“BIO SUR” and “BIOSUR”) or one word split in two. Two texts match once their similarity is at or above the threshold. These steps are never folded into SQL: M does them once the tables are read.

Known differences from Power BI Desktop

A few pairs close to the threshold come out otherwise than in Power BI Desktop 2.159. Seen there, and not reproduced:

From a threshold of 0.80 up, Desktop leaves out some pairs whose right text has one word more than the left: “aa bb” and “aa bb cc” (similarity 0.83) match at 0.79 there, not at 0.80. OpenBI matches every pair whose similarity reaches the threshold.
Below 0.80, Desktop’s search for candidates needs a share of the left text’s words that, for some numbers of rows in the right table, differs: “x q” and “x”, where no right row has q, is held to 0.5 with 2, 4, 9, 10, 12 or 25 right rows and not with 6, 8, 13, 20 or 40. OpenBI holds it by the words’ weights. Also “biosur ltda” and “BIO-SUR” at a threshold of 0.70.
A similarity exactly equal to the threshold, 0.80 or 0.675: “aa bb cc” and “dd ee aa bb cc” is 0.8, and doesn’t match at 0.80 in Desktop. It does in OpenBI.
Two words read as one: with ten rows in the right table, Desktop scores “Copec S.A.” against “COPEC” 0.74, OpenBI 0.75.
Groups and clusters of values of several words near the threshold, and their order: at 0.70 Desktop keeps “BIOSUR LTDA” apart from “BIO SUR” in one table and puts “biosur ltda” (0.79) with it in another.
A cluster column whose empty text stands for a null: Desktop gives its column names and fails only when its rows are read; OpenBI fails at once.
NumberOfMatches of -1, or ConcurrentRequests of 0 or 9: Desktop never answers; OpenBI says it is an error.

Check a fuzzy step’s result on your own data before a report relies on it, above all with a threshold close to your pairs’ similarities.

Last updated 8 November 2026 · OpenBI Studio 0.3.79