Enumerations in Power Query M

In programming, enumerations are a commonly used concept. You can picture enumerations as collections of named constants, kind of like predefined choices that guide how functions behave. Imagine you’re setting up rules for how a list should be sorted; an enumeration like Order.Ascending spells out clearly that you want your items sorted from A to Z. It reduces the complexity your code. But this is not the only way in which you can use them. You can also refer to enumerations through their corresponding index number.

If you prefer watching videos, you can follow along here:

Let’s dive into an example to illustrate this concept. Consider a list of letters:

{ "a", "v", "r", "p", "o" }

Our goal is to arrange them alphabetically. Here’s how you could do it using a function called List.Sort, which sorts the list in ascending order:

// Output: { "a", "o", "p", "r", "v" }
List.Sort( { "a", "v", "r", "p", "o" }, Order.Ascending ) 

In this case, the Order.Ascending enumeration tells our function to line up the values alphabetically. Interestingly, enumerations can also be represented by index numbers. For instance, Order.Ascending might also be known as 0, making the following code do exactly the same thing:

// Output: { "a", "o", "p", "r", "v" }
List.Sort( { "a", "v", "r", "p", "o" }, 0 ) 

This flexibility allows for different ways to instruct a function, using either a descriptive name or a numeric index. Now, let’s consider the broader application of enumerations. Some are made for specific functions, yet they might be universally recognized across other functions that accept enumerations.

Take Precision.Type, which determines how precise calculations should be, either as Precision.Double or Precision.Decimal:

Value.Add( 0.000004, 0.0003, Precision.Double ) // Output: 0,00030399999999999996
Value.Add( 0.000004, 0.0003, Precision.Decimal) // Output: 0,000304

Precision matters greatly when dealing with detailed values, affecting the result. Curiously, if we input a precision-related enumeration into the List.Sort function, it still works:

// Output: { "a", "o", "p", "r", "v" }
List.Sort( { "a", "v", "r", "p", "o" }, Precision.Double ) 

Here the function gives identical results to using Order.Ascending or 0. And surprisingly we get no error. What’s happening here? The Precision.Double enumeration corresponds to an underlying index number of 0, just like Order.Ascending does.

Important Enumerations

Let’s highlight some essential enumerations in the M language:

• Day.Type: Defines the week’s start day, influencing functions related to dates, like Date.DayOfWeek, Date.EndOfWeek, Date.StartOfWeek, Date.WeekOfMonth and Date.WeekOfYear.
• GroupKind.Type: used in the Table.Group function to specify how to group values. It can either respect the order of the values when grouping, or perform grouping based on the values in the entire dataset.
• JoinKind.Type: specifies the type of join to perform, for instance an inner join, left-anti-join or full-outer-join. This enumeration is supported by the functions Table.FuzzyJoin, Table.FuzzyNestedJoin, Table.Join and Table.NestedJoin.
• MissingField.Type: dictates how a function should handle missing values. It allows you return an error, ignore missing values or use null values instead. You can find it in record functions like Record.RemoveFields, Record.RenameFields, but also in table functions like Table.FromRecords, Table.ReorderColumns and Table.TransformColumns.
• Occurrence.Type: used to specify which value to return for functions that lookup values. It can return the first or last match, but also allows you to return all matches. Some functions that support this enumeration are List.PositionOf, Table.PositionOf and Text.PositionOf.
• Order.Type: determines how a function should sort its input. Functions supporting this enumeration are List.Sort, Table.AddRankColumn and Table.Sort.
• RoundingMode.Type: instructs functions how to round the outcome of its calculations. Some functions that use this enumeration are Currency.From, Int64.From and Number.Round.

This article highlighted the variety of enumerations at your disposal, simplifying how developers set parameters for functions. It explained that you could use either the specific index number or the name of an enumeration. Additionally, it clarified that Power Query relies on the index numbers to interpret how a function should operate, even if the enumeration name isn’t directly related to that function.