Mastering M: Which Power Query Functions to Learn and When

The Power Query M language features nearly 1000 unique functions, which can be overwhelming for beginners. In this article, we highlight the most important functions to master in M. We start with the basics and slowly build up to more advanced functions. There are a total of 119 functions I recommend.

When starting out with Power Query, you generally learn how the user interface works. Buttons will help you with operations like filtering, sorting, deleting columns and transforming your data. Each of these operations generates M code. And you don’t need to know M to do these basics. But there may come a time, where knowing the most important functions will help. Let’s have a look at the most basic functions that are useful to know when starting out with M.

Essential Basics (30 functions)

When starting with the M language the basics are important. Some functions are designed to create date and time values, such as #date and #time. Others, like Text.From, Date.From, and Number.From, are used to convert values into text, date, or number values respectively. These functions are important to ensure your data is shaped in a way compatible with the operation you want to do.

As you delve into the M language, understanding how to manipulate tables is important. Functions that add new columns, manage headers, and filter rows help here. Equally important are those functions that allow you to sort your data. These operations help you organize your data.

Next, you should know the fundamental text manipulation functions. Learning how to extract specific portions of text with Text.Start, Text.Middle, and Text.End is very useful. Additionally, functions like Text.StartsWith and Text.EndsWith help you verify the beginning or end of strings. Changing text case is made easy with Text.Lower, Text.Upper, and Text.Proper.

The basic functions are listed in the table below and provide a solid foundation for those new to the M language:

Function	Description
#date	Creates a date value from whole numbers representing the year, month, and day.
#time	Creates a time value from numbers representing the hour, minute, and (fractional) second.
Date.AddDays	Adds the specified days to the date.
Date.Day	Returns the day component.
Date.DayOfWeekName	Returns the day of the week name.
Date.From	Creates a date from the given value.
Date.Month	Returns the month component.
Date.MonthName	Returns the name of the month component.
Date.Year	Returns the year component.
List.Sum	Returns the sum of the items in the list.
Number.From	Creates a number from the given value.
Table.AddColumn	Adds a column with the specified name
Table.PromoteHeaders	Promotes the first row of values as the new column headers.
Table.RowCount	Returns the number of rows in the table.
Table.SelectRows	Selects the rows that meet the condition function.
Table.Sort	Sorts the table using one or more column names and comparison criteria.
Text.Combine	Concatenates a list of text values into one text value.
Text.Contains	Returns whether the text contains the substring.
Text.End	Returns the last characters of the text.
Text.EndsWith	Indicates whether the text ends in the specified value.
Text.From	Creates a text value from the given value.
Text.Length	Returns the number of characters.
Text.Lower	Converts all characters to lowercase.
Text.Middle	Returns the substring up to a specific length.
Text.Proper	Capitalizes the first letter of each word.
Text.Replace	Replaces all occurrences of the given substring in the text.
Text.Select	Selects all occurrences of the given character or list of characters from the input text value.
Text.Start	Returns the start of the text.
Text.StartsWith	Indicates whether the text starts with a specified value.
Text.Upper	Converts all characters to uppercase.

Core Operations (45 functions)

Once you’ve grasped the basic functions, it’s time to move on to the core functions, which are slightly more complex but useful for any Power Query developer.

The first few functions create values (#datetime, #duration, #table). Next, we explore date and time functions. Some functions adjust dates to the start of the month or extract a datetime value from another value. Additionally, there are functions that retrieve the current datetime from your machine and can help in making your queries dynamic.

When you get more experience, working with lists becomes important. There are functions that test if a list contains a value (List.Contains), return the number of items in the list (List.Count) or create a sequence of date values (List.Dates). The List.Distinct function can remove duplicates from your list, while List.Select allows you to select values that meet a certain condition. You can also use functions to sort a list based on your own criteria (List.Sort).

Next, we explore basic number functions. Number.Round returns a rounded value, while Number.Abs provides the absolute value. To determine if a number is odd or even, use Number.IsOdd or Number.IsEven.

At this stage it’s also important to learn about the different replacer functions for replacing values (Replacer.ReplaceText and Replacer.ReplaceValue). These are commonly used in the Table.ReplaceValue function. We then delve into a range of table functions that help you sort, manipulate and reshape tables.

Lastly, you will find some text functions that help you work with strings. Extracting text based on delimiters or using Text.Trim and Text.Clean to eliminate unnecessary spaces or characters are techniques essential for cleaning and preparing text data.

You can find the relevant functions in the table below:

Function	Description
#datetime	Creates a datetime value from numbers representing the year, month, day, hour, minute, and (fractional) second.
#duration	Creates a duration value from numbers representing days, hours, minutes, and (fractional) seconds.
#table	Creates a table value from columns and rows.
Date.StartOfMonth	Returns the start of the month.
DateTime.From	Creates a datetime from the given value.
DateTime.LocalNow	Returns the current date and time in the local timezone.
List.Contains	Indicates whether the list contains the value.
List.Count	Returns the number of items in the list.
List.Dates	Generates a list of date values given an initial value, count, and incremental duration value.
List.Distinct	Returns a list of values with duplicates removed.
List.Select	Returns a list of values that match the condition.
List.Sort	Sorts a list of data according to the criteria specified.
Number.Abs	Returns the absolute value of the number.
Number.IsEven	Indicates if the value is even.
Number.IsOdd	Indicates if the value is odd.
Number.Round	Returns the rounded number
Replacer.ReplaceText	Replaces text within the provided input.
Replacer.ReplaceValue	Replaces values within the provided input.
Table.AddIndexColumn	Appends a column with explicit position values.
Table.ColumnNames	Returns the column names as a list.
Table.Combine	Returns a table that is the result of merging a list of tables.
Table.DemoteHeaders	Demotes the column headers to the first row of values.
Table.Distinct	Removes duplicate rows from the table.
Table.FillDown	Propagates the value of a previous cell to the null-valued cells below in the column.
Table.FillUp	Propagates the value of a cell to the null-valued cells above in the column.
Table.Group	Groups rows in the table that have the same key.
Table.NestedJoin	Performs a join between tables on supplied columns and produces the join result in a new column.
Table.Pivot	Given a pair of columns representing attribute-value pairs, rotates the data in the attribute column into a column headings.
Table.RemoveColumns	Removes the specified columns.
Table.RenameColumns	Applies rename(s) of the form {old, new}.
Table.ReplaceValue	Replaces one value with another in the specified columns.
Table.TransformColumnTypes	Applies type transformation(s) of the form { column, type } using a specific culture.
Table.Transpose	Makes columns into rows and rows into columns.
Table.Unpivot	Translates a set of columns in a table into attribute-value pairs.
Table.UnpivotOtherColumns	Translates all columns other than a specified set into attribute-value pairs.
Text.AfterDelimiter	Returns the portion of text after the specified delimiter
Text.BeforeDelimiter	Returns the portion of text before the specified delimiter
Text.BetweenDelimiters	Returns the portion of text between the specified startDelimiter and endDelimiter
Text.Clean	Returns the text value with all control characters removed.
Text.PadEnd	Returns text of a specified length by padding the end of the given text.
Text.PadStart	Returns text of a specified length by padding the start of the given text.
Text.PositionOf	Returns the first position of the value (-1 if not found).
Text.Range	Returns the substring found at offset.
Text.Remove	Removes all occurrences of the given character or list of characters from the input text value.
Text.Split	Splits text into a list of text values based upon a specified delimiter.

After looking at the core operations, we now get to important functions that are generally used in more intermediate scenarios.

Intermediate Level (32 functions)

The intermediate level functions are often used in more complex scenarios and typically involve more challenging arguments. For instance, List.Transform, Table.TransformColumns, and Table.TransformColumnNames allow you to apply specific transformations to lists and tables by using a function.

On the other hand, functions like List.Skip, Table.SelectColumns, Table.FirstN, Table.LastN, Table.RemoveFirstN, and Table.RemoveLastN are designed to retrieve or exclude data based on certain conditions. While being able to use conditions is useful, it does require a bit more knowledge from the developer to set this up.

There is also a family of functions that transform values from one structure to another. For example, you can create a table from different values using Table.FromColumns, Table.FromList, Table.FromRecords, and Table.FromRows. Similarly, Record.FromList allows you to construct a record from a list. If you need to reshape an existing table into a different format, you can use Table.ToColumns, Table.ToRecords, and Table.ToRows. Once you become more comfortable with M, reshaping values is often required to be able to provide functions with the relevant argument input.

At this intermediate level you may also be working with custom functions. At this point, to be able to reference fields or columns, you will need functions like Record.Field and Table.Column. At the same time, you may also want to optimize the performance of your queries. Helpful functions for doing this are List.buffer and Table.Buffer.

Lastly, you also find functions aimed at lists that help you reshape their values. This can be useful when providing lists as input for other functions. Here’s an overview of important intermediate level functions:

Function	Description
Date.ToText	Returns a textual representation of the date value.
Duration.Days	Returns the days portion of a duration.
List.Buffer	Buffers a list.
List.Difference	Returns the difference of the two given lists.
List.Skip	Returns a list that skips the specified number of elements at the beginning of the list.
List.Transform	Returns a new list of values computed from this list.
List.Zip	Returns a list of lists by combining items at the same position in multiple lists.
Number.Mod	Integer divides two numbers and returns the remainder.
Number.ToText	Formats the given number as text.
Record.Field	Returns the value of the specified field in a record.
Record.FromList	Returns a record given a list of field values and a set of fields.
Record.ToList	Returns a list of values containing the field values of the input record.
Table.AddRankColumn	Appends a column with the ranking of one or more other columns.
Table.Buffer	Buffers a table in memory, isolating it from external changes during evaluation.
Table.Column	Returns a specified column of data from the table as a list.
Table.CombineColumns	Combines the specified columns into a new column using the specified combiner function.
Table.FirstN	Returns the first count rows specified.
Table.FromColumns	Creates a table from a list of columns and specified values.
Table.FromList	Converts a list into a table by applying the specified splitting function to each item in the list.
Table.FromRecords	Converts a list of records into a table.
Table.FromRows	Creates a table from a list of row values and optional columns
Table.LastN	Returns the last specified number of rows.
Table.RemoveFirstN	Returns a table with the first count rows skipped.
Table.RemoveLastN	Returns a table with the last N rows removed.
Table.Repeat	Repeats the rows of the tables a specified number of times.
Table.SelectColumns	Returns a table with only the specified columns.
Table.Skip	Returns a table with the first count rows skipped.
Table.ToColumns	Creates a list of nested lists of column values from a table.
Table.ToRecords	Converts a table to a list of records.
Table.ToRows	Creates a list of nested lists of row values from a table.
Table.TransformColumnNames	Transforms column names by using the given function.
Table.TransformColumns	Transforms the values of one or more columns.

After learning about the intermediate level functions, there may also be more advanced scenarios you want to work with. These often involve a different set of functions, which we will cover now.

Advanced Level (12 functions)

The advanced level functions are effective, but require some experience in M. Some functions have an iterative nature, designed to perform operations repeatedly. For instance, List.Accumulate allows you to perform a function iteratively over a list for a fixed number of times, building up a result. List.Generate is used to continue operations until a specified condition is no longer met. These functions are among the most complex in M due to their recursive nature and complex syntax.

Also included in the advanced section are functions for fuzzy grouping and joining. These are less frequently used but are useful for cleaning and combining messy data. Such functions can discern and group similar entries, or join data based on non-exact matches.

You will also find specialized functions that enable the sending of optimized queries, such as Value.NativeQuery, which can directly execute native database queries through M. Conversely, Table.StopFolding is used to prevent query folding, ensuring that certain parts of your data transformation process are executed exactly as written, without optimization by the Power Query engine.

You’ll also find data type related functions that can help manage your data types. For example, Value.Type retrieves the data type from an earlier step, while Value.ReplaceType allows you to change the data type ascribed to a value.

Finally, functions like Table.Profile and Table.Schema offering profiles of data distribution or detailed schema information. These functions are helpful for understanding the characteristics and structure of your data. You can find an overview of these functions in the below table:

Function	Description
List.Accumulate	Accumulates a summary value from the items in the list.
List.Generate	Generates a list of values.
Table.FuzzyGroup	Groups rows in the table based on fuzzy matching of keys.
Table.FuzzyNestedJoin	Performs a fuzzy join between tables on supplied columns and produces the join result in a new column.
Table.Profile	Returns a profile of the columns of a table.
Table.Schema	Returns a table containing a description of the columns (i.e
Table.StopFolding	Prevents any downstream operations from being run against the original source of the data.
Type.Is	Determines if a value of the first type is always compatible with the second type.
Type.TableSchema	Returns a table containing a description of the columns (i.e
Value.NativeQuery	Evaluates a query against a target.
Value.ReplaceType	Replaces the value’s type.
Value.Type	Returns the type of the given value.

Conclusion

This article provided an overview of 119 important functions to learn in the M language. The order in which to learn these is open for discussion. While I believe the order above provides a good indication of a logical order in which many of you will learn M, your journey may also be different. Hopefully these functions will get you started. I encourage you to start exploring these functions to improve your M skills.

Enjoy Power Query!