Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Power Query (informally known as "M") formula categories

Power Query (informally known as "M") formula categories

 

This topic applies to the Power Query Formula Language (informally known as "M") which can be used with Power Query and Power BI Designer to build queries that mashup data. See the list of function categories.

The following tables describe the Power Query formula categories that can be used to manually create or modify queries. These formula functions can also be used in the Power BI Designer, in the Query view.

Function

  

Description

  

Number.NaN

  

Represents 0/0.

  

Number.NegativeInfinity

  

Represents -1/0.

  

Number.PositiveInfinity

  

Represents 1/0.

  

Number.Epsilon

  

Returns the smallest possible number.

  

Number.E

  

Returns 2.7182818284590451, the value of e up to 16 decimal digits.

  

Number.PI

  

Returns 3.1415926535897931, the value for Pi up to 16 decimal digits.

  

                     

Function

Description

 Number.IsNaN 

Returns true if a value is Number.NaN.

 Number.IsEven 

Returns true if a value is an even number.

 Number.IsOdd 

Returns true if a value is an odd number.

                    

Function

Description

 Number.FromText 

Returns a number value from a text value.

 Number.ToText 

Returns a text value from a number value.

 Number.From 

Returns a number value from a value.

Byte.From

Returns a 8-bit integer number value from the given value.

Int8.From

Returns a signed 8-bit integer number value from the given value.

Int16.From

Returns a 16-bit integer number value from the given value.

Int32.From

Returns a 32-bit integer number value from the given value.

Int64.From

Returns a 64-bit integer number value from the given value.

Single.From

Returns a Single number value from the given value.

Double.From

Returns a Double number value from the given value.

Decimal.From

Returns a decimal number value from the given value.

Currency.From

Returns a currency value from the given value.

                             

Function

Description

 Number.RoundDown 

Returns the largest integer less than or equal to a number value.

 Number.RoundUp 

Returns the larger integer greater than or equal to a number value.

 Number.RoundTowardZero 

Returns Number.RoundDown(x) when x >= 0 and Number.RoundUp(x) when x < 0.

 Number.RoundAwayFromZero 

Returns Number.RoundUp(value) when value >= 0 and Number.RoundDown(value) when value < 0.

 Number.Round 

Returns a nullable number (n) if value is an integer.

                                                             

Function

Description

 Number.Abs 

Returns the absolute value of a number.

 Number.Sign 

Returns 1 for positive numbers, -1 for negative numbers or 0 for zero.

 Number.IntegerDivide 

Divides two numbers and returns the whole part of the resulting number.

 Number.Mod 

Divides two numbers and returns the remainder of the resulting number.

 Number.Power 

Returns a number raised by a power.

 Number.Sqrt 

Returns the square root of a number.

 Number.Exp 

Returns a number representing e raised to a power.

 Number.Ln 

Returns the natural logarithm of a number.

 Number.Log 

Returns the logarithm of a number to the base.

 Number.Log10 

Returns the base-10 logarithm of a number.

 Number.Factorial 

Returns the factorial of a number.

 Number.Combinations 

Returns the number of combinations of a given number of items for the optional combination size.

 Number.Permutations 

Returns the number of total permutatons of a given number of items for the optional permutation size.

                 

Function

Description

 Number.Random 

Returns a random fractional number between 0 and 1.

 Number.RandomBetween 

Returns a random number between the two given number values.

                                                 

Function

Description

 Number.Acos 

Returns the arccosine of a number.

 Number.Asin 

Returns the arcsine of a number.

 Number.Atan 

Returns the arctangent of a number.

 Number.Atan2 

Returns the arctangent of the division of two numbers.

 Number.Cos 

Returns the cosine of a number.

 Number.Cosh 

Returns the hyperbolic cosine of a number.

 Number.Sin 

Returns the sine of a number.

 Number.Sinh 

Returns the hyperbolic sine of a number.

 Number.Tan 

Returns the tangent of a number.

 Number.Tanh 

Returns the hyperbolic tangent of a number.

                                 

Function

Description

 Number.BitwiseAnd 

Returns the result of a bitwise AND operation on the provided operands.

 Number.BitwiseNot 

Returns the result of a bitwise NOT operation on the provided operands.

 Number.BitwiseOr 

Returns the result of a bitwise OR operation on the provided operands.

 Number.BitwiseShiftLeft 

Returns the result of a bitwise shift left operation on the operands.

 Number.BitwiseShiftRight 

Returns the result of a bitwise shift right operation on the operands.

 Number.BitwiseXor 

Returns the result of a bitwise XOR operation on the provided operands.

             

Function

Description

 Text.Length 

Returns the number of characters in a text value.

                                         

Function

Description

 Character.FromNumber 

Returns a number to its character value.

 Character.ToNumber 

Returns a character to its number value.

 Text.From 

Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value. If a value is null, Text.From returns null. The optional culture parameter is used to format the text value according to the given culture.

 Text.FromBinary 

Decodes data from a binary value in to a text value using an encoding.

 Text.NewGuid 

Returns a Guid value as a text value.

 Text.ToBinary 

Encodes a text value into binary value using an encoding.

 Text.ToList 

Returns a list of characters from a text value.

 Value.FromText 

Decodes a value from a textual representation, value, and interprets it as a value with an appropriate type. Value.FromText takes a text value and returns a number, a logical value, a null value, a DateTime value, a Duration value, or a text value. The empty text value is interpreted as a null value.

                     

Function

Description

 Text.At 

Returns a character starting at a zero-based offset.

 Text.Range 

Returns a number of characters from a text value starting at a zero-based offset and for count number of characters.

 Text.Start 

Returns the count of characters from the start of a text value.

              

Function

Description

 Text.End 

Returns the number of characters from the end of a text value.

                             

Function

Description

 Text.Insert 

Returns a text value with newValue inserted into a text value starting at a zero-based offset.

 Text.Remove 

Removes all occurrences of a character or list of characters from a text value. The removeChars parameter can be a character value or a list of character values.

 Text.RemoveRange 

Removes count characters at a zero-based offset from a text value.

 Text.Replace 

Replaces all occurrences of a substring with a new text value.

 Text.ReplaceRange 

Replaces length characters in a text value starting at a zero-based offset with the new text value.

                             

Function

Description

 Text.Contains 

Returns true if a text value substring was found within a text value string; otherwise, false.

 Text.EndsWith 

Returns a logical value indicating whether a text value substring was found at the end of a string.

 Text.PositionOf 

Returns the first occurrence of substring in a string and returns its position starting at startOffset.

 Text.PositionOfAny 

Returns the first occurrence of a text value in list and returns its position starting at startOffset.

 Text.StartsWith 

Returns a logical value indicating whether a text value substring was found at the beginning of a string.

                                                             

Function

Description

 Text.Clean 

Returns the original text value with non-printable characters removed.

 Text.Combine 

Returns a text value that is the result of joining all text values with each value separated by a separator.

 Text.Lower 

Returns the lowercase of a text value.

 Text.PadEnd 

Returns a text value padded at the end with pad to make it at least length characters.

 Text.PadStart 

Returns a text value padded at the beginning with pad to make it at least length characters. If pad is not specified, whitespace is used as pad.

 Text.Proper 

Returns a text value with first letters of all words converted to uppercase.

 Text.Repeat 

Returns a text value composed of the input text value repeated a number of times.

 Text.Split 

Returns a list containing parts of a text value that are delimited by a separator text value.

 Text.SplitAny 

Returns a list containing parts of a text value that are delimited by any separator text values.

 Text.Trim 

Removes any occurrences of characters in trimChars from text.

 Text.TrimEnd 

Removes any occurrences of the characters specified in trimChars from the end of the original text value.

 Text.TrimStart 

Removes any occurrences of the characters in trimChars from the start of the original text value.

 Text.Upper 

Returns the uppercase of a text value.

                     

Function

Description

 Logical.From 

Returns a logical value from a value.

 Logical.FromText 

Returns a logical value of true or false from a text value.

 Logical.ToText 

Returns a text value from a logical value.

                                                                                                                                                                                 

Function

Description

Date.AddDays

Returns a Date/DateTime/DateTimeZone value with the day portion incremented by the number of days provided. It also handles incrementing the month and year potions of the value as appropriate.

 Date.AddMonths 

Returns a DateTime value with the month portion incremented by n months.

Date.AddQuarters

Returns a Date/DateTime/DateTimeZone value incremented by the number of quarters provided. Each quarter is defined as a duration of three months. It also handles incrementing the year potion of the value as appropriate.

Date.AddWeeks

Returns a Date/DateTime/DateTimeZone value incremented by the number of weeks provided. Each week is defined as a duration of seven days. It also handles incrementing the month and year potions of the value as appropriate.

 Date.AddYears 

Returns a DateTime value with the year portion incremented by n years.

 Date.Day 

Returns the day for a DateTime value.

 Date.DayOfWeek 

Returns a number between 0 and 6 representing the day of the week from a DateTime value.

 Date.DayOfYear 

Returns a number that represents the day of the year from a DateTime value.

 Date.DaysInMonth 

Returns the number of days in the month from a DateTime value.

 Date.EndOfDay 

Returns a DateTime value for the end of the day.

 Date.EndOfMonth 

Returns a DateTime value for the end of the month.

Date.EndOfQuarter

Returns a Date/DateTime/DateTimeZone value representing the end of the quarter. The date and time portions are reset to their terminating values for the quarter. The timezone information is persisted.

 Date.EndOfWeek 

Returns a DateTime value for the end of the week.

 Date.EndOfYear 

Returns a DateTime value for the end of the year.

 Date.From 

Returns a date value from a value.

 Date.FromText 

Returns a Date value from a set of date formats and culture value.

Date.IsInCurrentMonth

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the current month, as determined by the current date and time on the system.

Date.IsInCurrentQuarter

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the current quarter, as determined by the current date and time on the system.

Date.IsInCurrentWeek

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the current week, as determined by the current date and time on the system.

Date.IsInCurrentYear

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the current year, as determined by the current date and time on the system.

Date.IsInNextMonth

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the next month, as determined by the current date and time on the system.

Date.IsInNextQuarter

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the next quarter, as determined by the current date and time on the system.

Date.IsInNextWeek

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the next week, as determined by the current date and time on the system.

Date.IsInNextYear

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the next year, as determined by the current date and time on the system.

Date.IsInPreviousMonth

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the previous month, as determined by the current date and time on the system.

Date.IsInPreviousQuarter

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the previous quarter, as determined by the current date and time on the system.

Date.IsInPreviousWeek

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the previous week, as determined by the current date and time on the system.

Date.IsInPreviousYear

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred during the previous year, as determined by the current date and time on the system.

Date.IsInYearToDate

Returns a logical value indicating whether the given Date/DateTime/DateTimeZone occurred in the period starting January 1st of the current year and ending on the current day, as determined by the current date and time on the system.

 Date.IsLeapYear 

Returns a logical value indicating whether the year portion of a DateTime value is a leap year.

 Date.Month 

Returns the month from a DateTime value.

 Date.QuarterOfYear 

Returns a number between 1 and 4 for the quarter of the year from a DateTime value.

 Date.StartOfDay 

Returns a DateTime value for the start of the day.

 Date.StartOfMonth 

Returns a DateTime value representing the start of the month.

Date.StartOfQuarter

Returns a DateTime value representing the start of the quarter.

 Date.StartOfWeek 

Returns a DateTime value representing the start of the week.

 Date.StartOfYear 

Returns a DateTime value representing the start of the year.

 Date.ToRecord 

Returns a record containing parts of a Date value.

 Date.ToText 

Returns a text value from a Date value.

 Date.WeekOfMonth 

Returns a number for the count of week in the current month.

 Date.WeekOfYear 

Returns a number for the count of week in the current year.

 Date.Year 

Returns the year from a DateTime value.

                                             

Function

Description

 Time.EndOfHour 

Returns a DateTime value from the end of the hour.

 Time.From 

Returns a time value from a value.

 Time.FromText 

Returns a Time value from a set of date formats.

 Time.Hour 

Returns an hour value from a DateTime value.

 Time.Minute 

Returns a minute value from a DateTime value.

 Time.Second 

Returns a second value from a DateTime value

 Time.StartOfHour 

Returns the first value of the hour from a time value.

 Time.ToRecord 

Returns a record containing parts of a Date value.

 Time.ToText 

Returns a text value from a Time value.

                                                 

Function

Description

 DateTime.AddZone 

Adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.

 DateTime.Date 

Returns a date part from a DateTime value

DateTime.FixedLocalNow

Returns a DateTime value set to the current date and time on the system.

 DateTime.From 

Returns a datetime value from a value.

 DateTime.FromFileTime 

Returns a DateTime value from the supplied number.

 DateTime.FromText 

Returns a DateTime value from a set of date formats and culture value.

 DateTime.LocalNow 

Returns a datetime value set to the current date and time on the system.

 DateTime.Time 

Returns a time part from a DateTime value.

 DateTime.ToRecord 

Returns a record containing parts of a DateTime value.

 DateTime.ToText 

Returns a text value from a DateTime value.

                                                                     

Function

Description

DateTimeZone.FixedLocalNow

Returns a DateTimeZone value set to the current date, time, and timezone offset on the system.

DateTimeZone.FixedUtcNow

Returns the current date and time in UTC (the GMT timezone).

 DateTimeZone.From 

Returns a datetimezone value from a value.

 DateTimeZone.FromFileTime 

Returns a DateTimeZone from a number value.

 DateTimeZone.FromText 

Returns a DateTimeZone value from a set of date formats and culture value.

 DateTimeZone.LocalNow 

Returns a DateTime value set to the current system date and time.

 DateTimeZone.RemoveZone 

Returns a datetime value with the zone information removed from the input datetimezone value.

 DateTimeZone.SwitchZone 

Changes the timezone information for the input DateTimeZone.

 DateTimeZone.ToLocal 

Returns a DateTime value from the local time zone.

 DateTimeZone.ToRecord 

Returns a record containing parts of a DateTime value.

 DateTimeZone.ToText 

Returns a text value from a DateTime value.

 DateTimeZone.ToUtc 

Returns a DateTime value to the Utc time zone.

 DateTimeZone.UtcNow 

Returns a DateTime value set to the current system date and time in the Utc timezone.

 DateTimeZone.ZoneHours 

Returns a time zone hour value from a DateTime value.

 DateTimeZone.ZoneMinutes 

Returns a time zone minute value from a DateTime value.

                                                         

Function

Description

 Duration.Days 

Returns the day component of a Duration value.

 Duration.From 

Returns a duration value from a value.

 Duration.FromText 

Returns a Duration value from a text value.

 Duration.Hours 

Returns an hour component of a Duration value.

 Duration.Minutes 

Returns a minute component of a Duration value.

 Duration.Seconds 

Returns a second component of a Duration value.

 Duration.ToRecord 

Returns a record with parts of a Duration value.

 Duration.TotalDays 

Returns the total magnitude of days from a Duration value.

 Duration.TotalHours 

Returns the total magnitude of hours from a Duration value.

 Duration.TotalMinutes 

Returns the total magnitude of minutes from a Duration value.

 Duration.TotalSeconds 

Returns the total magnitude of seconds from a duration value.

 Duration.ToText 

Returns a text value from a Duration value.

                 

Function

Description

 Record.FieldCount 

Returns the number of fields in a record.

 Record.HasFields 

Returns true if the field name or field names are present in a record.

                                 

Function

Description

 Record.AddField 

Adds a field from a field name and value.

 Record.Combine 

Combines the records in a list.

 Record.TransformFields 

Transforms fields by applying transformOperations. For more more information about values supported by transformOperations, see Parameter Values.

 Record.RemoveFields 

Returns a new record that reorders the given fields with respect to each other. Any fields not specified remain in their original locations.

 Record.ReorderFields 

Returns a new record that reorders fields relative to each other. Any fields not specified remain in their original locations. Requires two or more fields.

 Record.RenameFields 

Returns a new record that renames the fields specified. The resultant fields will retain their original order. This function supports swapping and chaining field names. However, all target names plus remaining field names must constitute a unique set or an error will occur.

                             

Function

Description

 Record.Field 

Returns the value of the given field. This function can be used to dynamically create field lookup syntax for a given record. In that way it is a dynamic verison of the record[field] syntax.

 Record.FieldValues 

Returns a list of field values in order of the record's fields.

 Record.FieldNames 

Returns a list of field names in order of the record's fields.

 Record.FieldOrDefault 

Returns the value of a field from a record, or the default value if the field does not exist.

 Record.SelectFields 

Returns a new record that contains the fields selected from the input record. The original order of the fields is maintained.

                 

Function

Description

 Record.ToTable 

Returns a table of records containing field names and values from an input record.

 Record.FromTable 

Returns a record from a table of records containing field names and values.

 Record.ToTable 

Returns a record from a list of field values and a set of field names.

The following type definitions are used to describe the parameter values that are referenced in Record functions above.

MissingField option

MissingField.Error = 0
MissingField.Ignore = 1
MissingField.UseNull = 2

Transform operations

Transform operations can be specified by either of the following values:

  • A list value of two items, first item being the field name and the second item being the transformation function applied to that field to produce a new value.

  • A list of transformations can be provided by providing a list value, and each item being the list value of 2 items as described above.

For examples, see description of Record.TransformFields

Rename operations

Rename operations for a record can be specified as either of:

  • A single rename operation, which is represented by a list of two field names, old and new.

For examples, see description of Record.RenameFields.

These functions deal with list of values.

                     

Function

Description

 List.Count 

Returns the number of items in a list.

 List.NonNullCount 

Returns the number of items in a list excluding null values

 List.IsEmpty 

Returns whether a list is empty.

                                                                                 

Function

Description

 List.Alternate 

Returns a list with the items alternated from the original list based on a count, optional repeatInterval, and an optional offset.

 List.Distinct 

Filters a list down by removing duplicates. An optional equation criteria value can be specified to control equality comparison. The first value from each equality group is chosen.

 List.IsDistinct 

Returns whether a list is distinct.

 List.Select 

Selects the items that match a condition.

 List.First 

Returns the first value of the list or the specified default if empty. Returns the first item in the list, or the optional default value, if the list is empty. If the list is empty and a default value is not specified, the function returns.

 List.FirstN 

Returns the first set of items in the list by specifying how many items to return or a qualifying condition provided by countOrCondition.

 List.InsertRange 

Inserts items from values at the given index in the input list.

 List.Last 

Returns the last set of items in the list by specifying how many items to return or a qualifying condition provided by countOrCondition.

 List.LastN 

Returns the last set of items in a list by specifying how many items to return or a qualifying condition.

 List.MatchesAll 

Returns true if all items in a list meet a condition.

 List.MatchesAny 

Returns true if any item in a list meets a condition.

 List.Positions 

Returns a list of positions for an input list.

 List.Range 

Returns a count items starting at an offset.

 List.Single 

Returns the single item of the list or throws an Expression.Error if the list has more than one item.

 List.SingleOrDefault 

Returns a single item from a list.

 List.Skip 

Skips the first item of the list. Given an empty list, it returns an empty list. This function takes an optional parameter countOrCondition to support skipping multiple values.

 List.Buffer 

Buffers the list in memory. The result of this call is a stable list, which means it will have a determinimic count, and order of items.

 List.FindText 

Searches a list of values, including record fields, for a text value.

                                                             

Function

Description

 List.Accumulate 

Accumulates a result from the list. Starting from the initial value seed this function applies the accumulator function and returns the final result.

 List.Combine 

Merges a list of lists into single list.

 List.Transform 

Performs the function on each item in the list and returns the new list.

 List.TransformMany 

Returns a list whose elements are projected from the input list.

 List.Repeat 

Returns a list that repeats the contents of an input list count times.

 List.ReplaceRange 

Returns a list that replaces count values in a list with a replaceWith list starting at an index.

 List.RemoveRange 

Returns a list that removes count items starting at offset. The default count is 1.

List.RemoveFirstN

Returns a list with the specified number of elements removed from the list starting at the first element. The number of elements removed depends on the optional countOrCondition parameter.

List.RemoveLastN

Returns a list with the specified number of elements removed from the list starting at the last element. The number of elements removed depends on the optional countOrCondition parameter.

 List.Reverse 

Returns a list that reverses the items in a list.

 List.RemoveMatchingItems 

Removes all occurrences of the given values in the list.

 List.RemoveItems 

Removes items from list1 that are present in list2, and returns a new list.

 List.RemoveNulls 

Removes null values from a list.

 List.ReplaceMatchingItems 

Replaces occurrences of existing values in the list with new values using the provided equationCriteria. Old and new values are provided by the replacements parameters. An optional equation criteria value can be specified to control equality comparisons. For details of replacement operations and equation criteria, see Parameter Values.

 List.ReplaceValue 

Searches a list of values for the value and replaces each occurrence with the replacement value.

Since all values can be tested for equality, these functions can operate over heterogeneous lists.

                                     

Function

Description

 List.Contains 

Returns true if a value is found in a list.

 List.ContainsAll 

Returns true if all items in values are found in a list.

 List.ContainsAny 

Returns true if any item in values is found in a list.

 List.PositionOf 

Finds the first occurrence of a value in a list and returns its position.

 List.PositionOfAny 

Finds the first occurrence of any value in values and returns its position.

 List.AnyTrue 

Returns true if any expression in a list in true

 List.AllTrue 

Returns true if all expressions in a list are true

                     

Function

Description

 List.Difference 

Returns the items in list 1 that do not appear in list 2. Duplicate values are supported.

 List.Intersect 

Returns a list from a list of lists and intersects common items in individual lists. Duplicate values are supported.

 List.Union 

Returns a list from a list of lists and unions the items in the individual lists. The returned list contains all items in any input lists. Duplicate values are matched as part of the Union.

Ordering functions perform comparisons. All values that are compared must be comparable with each other. This means they must all come from the same datatype (or include null, which always compares smallest). Otherwise, an Expression.Error is thrown.

Comparable data types

Number
Duration
DateTime
Text
Logical
Null
                                 

Function

Description

 List.Sort 

Returns a sorted list using comparison criterion.

 List.Max 

Returns the maximum item in a list, or the optional default value if the list is empty.

 List.MaxN 

Returns the maximum values in the list. After the rows are sorted, optional parameters may be specified to further filter the result

 List.Min 

Returns the minimum item in a list, or the optional default value if the list is empty.

 List.MinN 

Returns the minimum values in a list.

 List.Median 

Returns the median item from a list.

These functions operate over homogeneous lists of Numbers, DateTimes, and Durations.

                         

Function

Description

 List.Average 

Returns an average value from a list in the datatype of the values in the list.

 List.StandardDeviation 

Returns the standard deviation from a list of values. List.StandardDeviation performs a sample based estimate. The result is a number for numbers, and a duration for DateTimes and Durations.

 List.Mode 

Returns an item that appears most commonly in a list.

 List.Modes 

Returns all items that appear with the same maximum frequency.

These functions work over homogeneous lists of Numbers or Durations.

             

Function

Description

 List.Sum 

Returns the sum from a list.

These functions only work over numbers.

                 

Function

Description

 List.Covariance 

Returns the covariance from two lists as a number.

 List.Product 

Returns the product from a list of numbers.

These functions generate list of values.

                                     

Function

Description

 List.DateTimes 

Returns a list of datetime values from size count, starting at start and adds an increment to every value.

 List.Dates 

Returns a list of date values from size count, starting at start and adds an increment to every value.

 List.DateTimeZones 

Returns a list of of datetimezone values from size count, starting at start and adds an increment to every value.

 List.Durations 

Returns a list of durations values from size count, starting at start and adds an increment to every value.

 List.Generate 

Generates a list from a value function, a condition function, a next function, and an optional transformation function on the values.

 List.Numbers 

Returns a list of numbers from size count starting at initial, and adds an increment. The increment defaults to 1.

 List.Random 

Returns a list of count random numbers, with an optional seed parameter.

Occurrence.First = 0
Occurrence.Last = 1
Occurrence.All = 2

Order.Ascending = 0
Order.Descending = 1

Equation criteria for list values can be specified as either a

  • A function value that is either

    • A key selector that determines the value in the list to apply the equality criteria, or

    • A comparer function that is used to specify the kind of comparison to apply. Built in comparer functions can be specified, see section for Comparer functions.

  • A list value which has

    • Exactly two items

    • The first element is the key selector as specified above

    • The second element is a comparer as specified above.

For more information and examples, see List.Distinct.

Comparison criterion can be provided as either of the following values:

  • A number value to specify a sort order. For more inforarmtion, see sort order in Parameter values.

  • To compute a key to be used for sorting, a function of 1 argument can be used.

  • To both select a key and control order, comparison criterion can be a list containing the key and order.

  • To completely control the comparison, a function of 2 arguments can be used that returns -1, 0, or 1 given the relationship between the left and right inputs. Value.Compare is a method that can be used to delegate this logic.

For more information and examples, see List.Sort.

Replacement operations are specified by a list value, each item of this list must be

  • A list value of exactly two items

  • Fist item is the old value in the list, to be replaced

  • Second item is the new which should replace all occurrences of the old value in the list

These functions deal with Table values.

                             

Function

Description

 Table.FromRows 

Creates a table from the list where each element of the list is a list that contains the column values for a single row.

 Table.FromList 

Converts a list into a table by applying the specified splitting function to each item in the list.

 Table.FromColumns 

Returns a table from a list containing nested lists with the column names and values.

 Table.FromRecords 

Returns a table from a list of records.

 Table.FromValue 

Returns a table with a column containing the provided value or list of values.

                         

Function

Description

 Table.ToColumns 

Returns a list of nested lists each representing a column of values in the input table.

 Table.ToList 

Returns a table into a list by applying the specified combining function to each row of values in a table.

 Table.ToRecords 

Returns a list of records from an input table.

 Table.ToRows 

Returns a nested list of row values from an input table.

                       

Function

Description

 Table.IsEmpty 

Returns true if the table does not contain any rows.

 Table.RowCount 

Returns the number of rows in a table.

 Table.ColumnCount 

Returns the number of columns in a table.

Table.PartitionValues

Returns information about how a table is partitioned.

                                                                                                        

Function

Description

 Table.AlternateRows 

Returns a table containing an alternating pattern of the rows from a table.

 Table.Combine 

Returns a table that is the result of merging a list of tables. The tables must all have the same row type structure.

 Table.FirstN 

Returns the first row(s) of a table, depending on the countOrCondition parameter.

 Table.First 

Returns the first row from a table.

 Table.InsertRows 

Returns a table with the list of rows inserted into the table at an index. Each row to insert must match the row type of the table..

 Table.LastN 

Returns the last row(s) from a table, depending on the countOrCondition parameter.

 Table.Last 

Returns the last row of a table.

 Table.MatchesAllRows 

Returns true if all of the rows in a table meet a condition.

 Table.MatchesAnyRows 

Returns true if any of the rows in a table meet a condition.

 Table.Partition 

Partitions the table into a list of groups number of tables, based on the value of the column of each row and a hash function. The hash function is applied to the value of the column of a row to obtain a hash value for the row. The hash value modulo groups determines in which of the returned tables the row will be placed.

Table.FromPartitions

Returns a table that is the result of combining a set of partitioned tables into new columns. The type of the column can optionally be specified, the default is any.

 Table.Range 

Returns the specified number of rows from a table starting at an offset.

 Table.RemoveRows 

Returns a table with the specified number of rows removed from the table starting at an offset.

 Table.RemoveFirstN

Returns a table with the specified number of rows removed from the table starting at the first row. The number of rows removed depends on the optional countOrCondition parameter.

 Table.RemoveLastN

Returns a table with the specified number of rows removed from the table starting at the last row. The number of rows removed depends on the optional countOrCondition parameter.

 Table.RemoveRowsWithErrors 

Returns a table with all rows removed from the table that contain an error in at least one of the cells in a row.

 Table.Repeat 

Returns a table containing the rows of the table repeated the count number of times.

 Table.ReplaceRows 

Returns a table where the rows beginning at an offset and continuing for count are replaced with the provided rows.

 Table.ReverseRows 

Returns a table with the rows in reverse order.

 Table.SelectRows

Returns a table containing only the rows that match a condition.

 Table.SelectRowsWithErrors 

Returns a table with only the rows from table that contain an error in at least one of the cells in a row.

 Table.SingleRow 

Returns a single row from a table.

 Table.Skip 

Returns a table that does not contain the first row or rows of the table.

 Table.FindText 

Returns a table containing only the rows that have the specified text within one of their cells or any part thereof.

                                                        

Function

Description

 Table.Column 

Returns the values from a column in a table.

 Table.ColumnNames 

Returns the names of columns from a table.

 Table.ColumnsOfType 

Returns a list with the names of the columns that match the specified types.

 Table.HasColumns 

Returns true if a table has the specified column or columns.

 Table.PrefixColumns 

Returns a table where the columns have all been prefixed with a text value.

 Table.PromoteHeaders 

Promotes the first row of the table into its header or column names.

 Table.DemoteHeaders 

Demotes the header row down into the first row of a table.

 Table.RemoveColumns 

Returns a table without a specific column or columns.

 Table.ReorderColumns 

Returns a table with specific columns in an order relative to one another.

 Table.RenameColumns 

Returns a table with the columns renamed as specified.

 Table.SelectColumns 

Returns a table that contains only specific columns.

 Table.Pivot 

Given a table and attribute column containing pivotValues, creates new columns for each of the pivot values and assigns them values from the valueColumn. An optional aggregationFunction can be provided to handle multiple occurrence of the same key value in the attribute column.

 Table.Unpivot 

Given a list of table columns, transforms those columns into attribute-value pairs.

 Table.UnpivotOtherColumns 

Translates all columns other than a specified set into attribute-value pairs, combined with the rest of the values in each row.

Parameters for Group options

GroupKind.Global = 0
GroupKind.Local = 1

Parameters for Join kinds

JoinKind.Inner = 0
JoinKind.LeftOuter = 1
JoinKind.RightOuter = 2
JoinKind.FullOuter = 3
JoinKind.LeftAnti = 4
JoinKind.RightAnti = 5

Join Algorithm

The following JoinAlgorithm values can be specified to Table.Join

JoinAlgorithm.Dynamic  = 0
JoinAlgorithm.PairwiseHash = 1
JoinAlgorithm.SortMerge = 2
JoinAlgorithm.LeftHash = 3
JoinAlgorithm.RightHash  = 4
JoinAlgorithm.LeftIndex = 5
JoinAlgorithm.RightIndex = 6

Example data

The following tables are used by the examples in this section.

Customers table

Customers = Table.FromRecords({
  [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
  [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
  [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
  [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
}

Orders table

Orders = Table.FromRecords({
  [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
  [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
  [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
  [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
  [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
  [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
  [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25],
  [OrderID = 8, CustomerID = 5, Item = "Fishing Rod", Price = 100.0],
  [OrderID = 9, CustomerID = 6, Item = "Bait", Price = 3.25]
})
                                                                                                

Function

Description

 Table.AddColumn 

Adds a column named newColumnName to a table.

 Table.AddIndexColumn 

Returns a table with a new column with a specific name that, for each row, contains an index of the row in the table.

 Table.AddJoinColumn 

Performs a nested join between table1 and table2 from specific columns and produces the join result as a newColumnName column for each row of table1.

 Table.AddKey 

Add a key to table.

 Table.AggregateTableColumn 

Aggregates tables nested in a specific column into multiple columns containing aggregate values for those tables.

 Table.ExpandListColumn 

Given a column of lists in a table, create a copy of a row for each value in its list.

 Table.ExpandRecordColumn 

Expands a column of records into columns with each of the values.

 Table.ExpandTableColumn 

Expands a column of records or a column of tables into multiple columns in the containing table.

 Table.FillDown 

Replaces null values in the specified column or columns of the table with the most recent non-null value in the column.

 Table.FillUp

Returns a table from the table specified where the value of the next cell is propagated to the null values cells above in the column specified.

 Table.Group 

Groups table rows by the values of key columns for each row.

 Table.Join 

Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by table1, key1 and table2, key2.

 Table.NestedJoin 

Joins the rows of the tables based on the equality of the keys. The results are entered into a new column.

 Table.Keys 

Returns a list of key column names from a table.

 Table.ReplaceKeys 

Returns a new table with new key information set in the keys argument.

 Table.CombineColumns 

Table.CombineColumns merges columns using a combiner function to produce a new column. Table.CombineColumns is the inverse of Table.SplitColumns.

 Table.SplitColumn 

Returns a new set of columns from a single column applying a splitter function to each value.

 Table.TransformColumns 

Transforms columns from a table using a function.

 Table.TransformColumnTypes 

Transforms the column types from a table using a type.

 Table.TransformRows 

Transforms the rows from a table using a transform function.

 Table.Transpose 

Returns a table with columns converted to rows and rows converted to columns from the input table.

 Table.ReplaceValue 

Replaces oldValue with newValue in specific columns of a table, using the provided replacer function, such as text.Replace or Value.Replace.

 Table.ReplaceErrorValues

Replaces the error values in the specified columns with the corresponding specified value.

Parameters for membership checks

Occurrence specification

Occurrence.First  = 0
Occurrence.Last   = 1
Occurrence.All    = 2
                                              

Function

Description

 Table.Contains 

Determines whether the a record appears as a row in the table.

 Table.ContainsAll 

Determines whether all of the specified records appear as rows in the table.

 Table.ContainsAny 

Determines whether any of the specified records appear as rows in the table.

 Table.Distinct 

Removes duplicate rows from a table, ensuring that all remaining rows are distinct.

 Table.IsDistinct 

Determines whether a table contains only distinct rows.

 Table.PositionOf 

Determines the position or positions of a row within a table.

 Table.PositionOfAny 

Determines the position or positions of any of the specified rows within the table.

 Table.RemoveMatchingRows 

Removes all occurrences of rows from a table.

 Table.ReplaceMatchingRows 

Replaces specific rows from a table with the new rows.

Example data

The following tables are used by the examples in this section.

Employees table

Employees = Table.FromRecords(
    {[Name="Bill",   Level=7,  Salary=100000],
     [Name="Barb",   Level=8,  Salary=150000],
     [Name="Andrew", Level=6,  Salary=85000],
     [Name="Nikki",  Level=5,  Salary=75000],
     [Name="Margo",  Level=3,  Salary=45000],
     [Name="Jeff",   Level=10, Salary=200000]},
type table [
    Name = text,
    Level = number,
    Salary = number
])
                    

Function

Description

 Table.MaxN 

Returns the largest N rows from a table. After the rows are sorted, the countOrCondition parameter must be specified to further filter the result.

 Table.Max 

Returns the largest row or rows from a table using a comparisonCriteria.

 Table.MinN 

Returns the smallest N rows in the given table. After the rows are sorted, the countOrCondition parameter must be specified to further filter the result.

 Table.Min 

Returns the smallest row or rows from a table using a comparisonCriteria.

 Table.Sort 

Sorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.

   

Function

Description

 Table.Buffer 

Buffers a table into memory, isolating it from external changes during evaluation.

This parameter is a list of text values specifying the column names of the resulting table. This parameter is generally used in the Table construction functions, such as Table.FromRows and Table.FromList.

Comparison criterion can be provided as either of the following values:

  • A number value to specify a sort order. See sort order in the parameter values section above.

  • To compute a key to be used for sorting, a function of 1 argument can be used.

  • To both select a key and control order, comparison criterion can be a list containing the key and order.

  • To completely control the comparison, a function of 2 arguments can be used that returns -1, 0, or 1 given the relationship between the left and right inputs. Value.Compare is a method that can be used to delegate this logic.

For examples, see description of Table.Sort.

This criteria is generally used in ordering or row operations. It determines the number of rows returned in the table and can take two forms, a number or a condition:

  • A number indicates how many values to return inline with the appropriate function

  • If a condition is specified, the rows containing values that initially meet the condition is returned. Once a value fails the condition, no further values are considered.

See Table.FirstN or Table.MaxN.

This is used to indicate how the function should handle extra values in a row. This parameter is specified as a number, which maps to the options below.

ExtraValues.List = 0
ExtraValues.Error = 1
ExtraValues.Ignore = 2

For more information, see Table.FromList.

This is used to indicate how the function should handle missing columns. This parameter is specified as a number, which maps to the options below.

MissingField.Error = 0;
MissingField.Ignore = 1;
MissingField.UseNull = 2;

This is used in column or transformation operations. For Examples, see Table.TransformColumns.

This is used to indicate how the results should be sorted. This parameter is specified as a number, which maps to the options below.

Order.Ascending = 0
Order.Descending = 1

Equation criteria for tables can be specified as either a

  • A function value that is either

    • A key selector that determines the column in the table to apply the equality criteria, or

    • A comparer function that is used to specify the kind of comparison to apply. Built in comparer functions can be specified, see section for Comparer functions.

  • A list of the columns in the table to apply the equality criteria

For examples, look at description for Table.Distinct.

                     

Function

Description

 Value.Compare 

Returns 1, 0, or -1 based on value1 being greater than, equal to, or less than the value2. An optional comparer function can be provided.

 Value.Equals 

Returns whether two values are equal.

 Value.NullableEquals 

Returns a logical value or null based on two values .

                     

Function

Description

 Value.Add 

 

 Value.Subtract 

 

 Value.Divide 

 

 Value.Multiply 

 

                         

Type

Description

 Value.As 

Value.As is the function corresponding to the as operator in the formula language. The expression value as type asserts that the value of a value argument is compatible with type as per the is operator. If it is not compatible, an error is raised.

 Value.Is 

Value.Is is the function corresponding to the is operator in the formula language. The expression value is type returns true if the ascribed type of vlaue is compatible with type, and returns false if the ascribed type of value is incompatible with type.

 Value.ReplaceType 

A value may be ascribed a type using Value.ReplaceType. Value.ReplaceType either returns a new value with the type ascribed or raises an error if the new type is incompatible with the value’s native primitive type. In particular, the function raises an error when an attempt is made to ascribe an abstract type, such as any. When replacing a the type of a record, the new type must have the same number of fields, and the new fields replace the old fields by ordinal position, not by name. Similarly, when replacing the type of a table, the new type must have the same number of columns, and the new columns replace the old columns by ordinal position.

 Value.Type 

The ascribed type of a value may be obtained using Value.Type.

                     

Function

Description

 Value.Metadata 

Returns a record containing the input’s metadata.

 Value.RemoveMetadata 

Removes the metadata on the value and returns the original value.

 Value.ReplaceMetadata 

Replaces the metadata on a value with the new metadata record provided and returns the original value with the new metadata attached.

The following deals with external data.

Return values

Functions in this section access data and return table values. Most of these functions return a table value that is called a navigation table. A navigation table is a two column table. The first column contains the name of an item and the corresponding second column contains the value of that item. This shape is primarily used by the Power Query user interface to provide navigation experience over the potentially large hierarchical data returned.

                                                                                                                                                 

Function

Description

 Access.Database 

Returns a structural representation of an Microsoft Access database. The database argument is The return value is a record, where each field represents a table in the Access database.

 ActiveDirectory.Domains 

Returns a table with Domain information available in the current domain or optional Active Directory forest.

 Marketplace.Subscriptions 

Returns feeds offered by the Microsoft Azure DataMarket subscribed by the current user as a table.

 Csv.Document 

Returns the contents of a CSV document as a table using the specified encoding.

 Excel.Workbook 

Returns a table representing sheets in the given excel workbook.

 Excel.CurrentWorkbook 

Returns the tables in the current Excel workbook

 Exchange.Contents 

Returns a table of contents from a Microsoft Exchange account.

 Facebook.Graph 

Returns a table containing content from the Facebook graph .

 File.Contents 

Returns the binary contents of the file located at a path.

 Folder.Contents 

Returns a table containing the properties and contents of the files and folders found at path.

 Folder.Files 

Returns a table containing a row for each file found at a folder path, and subfolders. Each row contains properties of the folder or file and a link to its content.

 Hdfs.Contents 

Returns a table containing a row for each folder and file found at the folder url, {0}, from a Hadoop file system. Each row contains properties of the folder or file and a link to its content.

 Hdfs.Files 

Returns a table containing a row for each file found at the folder url, {0}, and subfolders from a Hadoop file system. Each row contains properties of the file and a link to its content.

 Json.Document 

Returns the contents of a JSON document. The contents may be directly passed to the function as text, or it may be the binary value returned by a function like File.Contents.

 OData.Feed 

Returns a table of OData feeds offered by an OData serviceUri.

 Soda.Feed 

Returns the resulting table of a CSV file that can be accessed using the SODA 2.0 API. The URL must point to a valid SODA-compliant source that ends in a .csv extension.

 Sql.Databases 

Returns a table with references to databases located on a SQL Server instance. Returns a navigation table.

 Sql.Database 

Returns a table containing SQL tables located on a SQL Server instance database.

 Oracle.Database 

Returns a table with data relating to the tables in the specified Oracle Database.

 DB2.Database 

Returns a table with data relating to the tables in the specified DB2 Database.

 MySQL.Database 

Returns a table with data relating to the tables in the specified MySQL Database.

 Sybase.Database 

Returns a table with data relating to the tables in the specified Sybase Database.

 SharePoint.Contents 

Returns a table containing a row for each folder and document found at the SharePoint site url. Each row contains properties of the folder or file and a link to its content.

 SharePoint.Files 

Returns a table containing a row for each document found at the SharePoint site url, and subfolders. Each row contains properties of the folder or file and a link to its content.

 SharePoint.Tables 

Returns a table containing the result of a SharePoint List as an OData feed.

 Teradata.Database 

Returns a table with data relating to the tables in the specified Teradata Database.

PostgreSQL.Database

Returns a table with data relating to the tables in the specified PostgreSQL Database.

 Web.Contents 

Returns the contents downloaded from a web url as a binary value.

 Web.Page 

Returns the contents of an HTML webpage as a table.

 Xml.Document 

Returns the contents of an XML document as a hierarchical table (list of records).

 Xml.Tables 

Returns the contents of an XML document as a nested collection of flattened tables.

 HdInsight.Containers 

Returns a navigational table containing all containers found in the HDInsight account. Each row has the container name and table containing its files.

HdInsight.Contents

Returns a navigational table containing all containers found in the HDInsight account. Each row has the container name and table containing its files.

 HdInsight.Files 

Returns a table containing a row for each folder and file found at the container URL, and subfolders from an HDInsight account. Each row contains properties of the file/folder and a link to its content.

 AzureStorage.Blobs 

Returns a navigational table containing all containers found in the Azure Storage account. Each row has the container name and a link to the container blobs.

 AzureStorage.Tables 

Returns a navigational table containing a row for each table found at the account URL from an Azure storage vault. Each row contains a link to the azure table.

SapBusinessObjects.Universes

Connects to the SAP BusinessObjects BI Universe at the specified URL and returns the set of available universes.

Salesforce.Data

Connects to the Salesforce Objects API and returns the set of available objects (i.e. Accounts).

Salesforce.Reports

Connects to the Salesforce Reports API and returns the set of available reports.

AnalysisServices.Databases

Returns the Analysis Services databases on a particular host.

Odbc.Query

Connects to a generic provider with the given connection string and returns the result of evaluating the query.

GoogleAnalytics.Accounts

Returns the Google Analytics accounts for the current credential.

                 

Function

Description

 Uri.Combine 

Returns a Uri based on the combination of the base and relative parts.

 Uri.Parts 

Returns a record value with the fields set to the parts of a Uri text value.

                                                                                                 

Function

Description

 BinaryFormat.7BitEncodedSignedInteger 

A binary format that reads a 64-bit signed integer that was encoded using a 7-bit variable-length encoding.

 BinaryFormat.7BitEncodedUnsignedInteger 

A binary format that reads a 64-bit unsigned integer that was encoded using a 7-bit variable-length encoding.

 BinaryFormat.Binary 

Returns a binary format that reads a binary value.

 BinaryFormat.Byte 

A binary format that reads an 8-bit unsigned integer.

 BinaryFormat.ByteOrder 

Returns a binary format with the specified byte order.

 BinaryFormat.Choice 

Returns a binary format that chooses the next binary format based on a value that has already been read.

 BinaryFormat.Decimal 

A binary format that reads a .NET 16-byte decimal value.

 BinaryFormat.Double 

A binary format that reads an 8-byte IEEE double-precision floating point value.

 BinaryFormat.Group 

Returns a binary format that reads a group of items. Each item value is preceded by a unique key value. The result is a list of item values.

 BinaryFormat.Length 

Returns a binary format that limits the amount of data that can be read. Both BinaryFormat.List and BinaryFormat.Binary can be used to read until end of the data. BinaryFormat.Length can be used to limit the number of bytes that are read.

 BinaryFormat.List 

Returns a binary format that reads a sequence of items and returns a list.

 BinaryFormat.Null 

A binary format that reads zero bytes and returns null.

 BinaryFormat.Record 

Returns a binary format that reads a record. Each field in the record can have a different binary format.

 BinaryFormat.SignedInteger16 

A binary format that reads a 16-bit signed integer.

 BinaryFormat.SignedInteger32 

A binary format that reads a 32-bit signed integer.

 BinaryFormat.SignedInteger64 

A binary format that reads a 64-bit signed integer.

 BinaryFormat.Single 

A binary format that reads a 4-byte IEEE single-precision floating point value.

 BinaryFormat.Text 

Returns a binary format that reads a text value. The optional encoding value specifies the encoding of the text.

 BinaryFormat.Transform 

Returns a binary format that will transform the values read by another binary format.

 BinaryFormat.UnsignedInteger16 

A binary format that reads a 16-bit unsigned integer.

 BinaryFormat.UnsignedInteger32 

A binary format that reads a 32-bit unsigned integer.

 BinaryFormat.UnsignedInteger64 

A binary format that reads a 64-bit unsigned integer.

                                         

Function

Description

 Binary.Buffer 

Buffers the binary value in memory. The result of this call is a stable binary value, which means it will have a deterministic length and order of bytes.

 Binary.Combine 

Combines a list of binaries into a single binary.

 Binary.From 

Returns a binary value from the given value.

 Binary.FromList 

Converts a list of numbers into a binary value

 Binary.FromText 

Decodes data from a text form into binary.

 Binary.Length 

Returns the length of binary values.

 Binary.ToList 

Converts a binary value into a list of numbers

 Binary.ToText 

Encodes binary data into a text form.

                         

Function

Description

 Lines.FromBinary 

Converts a binary value to a list of text values split at lines breaks.

 Lines.FromText 

Converts a text value to a list of text values split at lines breaks.

 Lines.ToBinary 

Converts a list of text into a binary value using the specified encoding and lineSeparator.The specified lineSeparator is appended to each line. If not specified then the carriage return and line feed characters are used.

 Lines.ToText 

Converts a list of text into a single text. The specified lineSeparator is appended to each line. If not specified then the carriage return and line feed characters are used.

                     

Function

Description

 Expression.Evaluate 

Evaluates a Text expression and returns the evaluated value.

 Expression.Constant 

Returns a constant text literal from a value.

 Expression.Identifier 

Returns a text value that can be used as an identifier from a text value.

             

Function

Description

 Function.Invoke 

Invokes the given function using the specified and returns the result.

             

Function

Description

 Error.Record 

Returns a record containing fields “Reason”, “Message”, and “Detail” set to the provided values. The record can be used to raise or throw an error.

                         

Function

Description

 Comparer.Equals 

Returns a logical value based on the equality check over the two given values.

 Comparer.FromCulture 

Returns a comparer function given the culture and a logical value for case sensitivity for the comparison. The default value for ignoreCase is false. The value for culture are well known text representations of locales used in the .NET framework.

 Comparer.Ordinal 

Returns a comparer function which uses Ordinal rules to compare values.

 Culture.Current 

Returns the current culture of the system.

                                     

Function

Description

 Splitter.SplitTextByAnyDelimiter 

Returns a function that splits text by at any of the delimiters.

 Splitter.SplitTextByDelimiter 

Returns a function that will split text according to a delimiter.

 Splitter.SplitTextByEachDelimiter 

Returns a function that splits text by each delimiter in turn.

 Splitter.SplitTextByLengths 

Returns a function that splits text according to the specified lengths.

 Splitter.SplitTextByPositions 

Returns a function that splits text according to the specified positions.

 Splitter.SplitTextByRanges 

Returns a function that splits text according to the specified ranges.

 Splitter.SplitTextByWhitespace 

Returns a function that splits text according to whitespace.

                             

Function

Description

 Combiner.CombineTextByDelimiter 

Returns a function that combines a list of text into a single text using the specified delimiter.

 Combiner.CombineTextByEachDelimiter 

Returns a function that combines a list of text into a single text using each specified delimiter in sequence.

 Combiner.CombineTextByLengths 

Returns a function that merges a list of text into a single text.

 Combiner.CombineTextByPositions 

Returns a function that merges a list of text into a single text.

 Combiner.CombineTextByRanges 

Returns a function that merges a list of text into a single text.

                 

Function

Description

 Replacer.ReplaceText 

This function be provided to List.ReplaceValue or Table.ReplaceValue to do replace of text values in list and table values respectively.

 Replacer.ReplaceValue 

This function be provided to List.ReplaceValue or Table.ReplaceValue to do replace values in list and table values respectively.

                                                                                     

Function

Description

 Type.AddTableKey 

Add a key to a table type.

 Type.ClosedRecord 

The given type must be a record type returns a closed version of the given record type (or the same type, if it is already closed)

 Type.ForFunction 

Creates a function type from the given .

 Type.ForList 

Returns a list type for an item type.

 Type.ForNullable 

Returns a nullable type for a type.

 Type.ForRecord 

Returns a Record type from a fields record.

 Type.ForTable 

Constructs a table type from a row type as a record type.

 Type.FunctionParameters 

Returns a record with field values set to the name of the parameters of a function type, and their values set to their corresponding types.

 Type.FunctionRequiredParameters 

Returns a number indicating the minimum number of parameters required to invoke the a type of function.

 Type.FunctionReturn 

Returns a type returned by a function type.

 Type.IsNullable 

Returns true if a type is a nullable type; otherwise, false.

 Type.IsOpenRecord 

Returns whether a record type is open.

 Type.ListItem 

Returns an item type from a list type.

 Type.NonNullable 

Returns the non nullable type from a type.

 Type.OpenRecord 

Returns an opened version of a record type, or the same type, if it is already open.

 Type.RecordFields 

Returns a record describing the fields of a record type with each field of the returned record type having a corresponding name and a value that is a record of the form [ Type = type, Opional = logical ].

 Type.ReplaceTableKeys 

Replaces the keys in a table type.

 Type.TableKeys 

Returns keys from a table type.

 Type.TableRow 

Returns a row type from a table type.

Show:
© 2015 Microsoft