Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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:

FunctionDescription
#dateCreates a date value from whole numbers representing the year, month, and day.
#timeCreates a time value from numbers representing the hour, minute, and (fractional) second.
Date.AddDaysAdds the specified days to the date.
Date.DayReturns the day component.
Date.DayOfWeekNameReturns the day of the week name.
Date.FromCreates a date from the given value.
Date.MonthReturns the month component.
Date.MonthNameReturns the name of the month component.
Date.YearReturns the year component.
List.SumReturns the sum of the items in the list.
Number.FromCreates a number from the given value.
Table.AddColumnAdds a column with the specified name
Table.PromoteHeadersPromotes the first row of values as the new column headers.
Table.RowCountReturns the number of rows in the table.
Table.SelectRowsSelects the rows that meet the condition function.
Table.SortSorts the table using one or more column names and comparison criteria.
Text.CombineConcatenates a list of text values into one text value.
Text.ContainsReturns whether the text contains the substring.
Text.EndReturns the last characters of the text.
Text.EndsWithIndicates whether the text ends in the specified value.
Text.FromCreates a text value from the given value.
Text.LengthReturns the number of characters.
Text.LowerConverts all characters to lowercase.
Text.MiddleReturns the substring up to a specific length.
Text.ProperCapitalizes the first letter of each word.
Text.ReplaceReplaces all occurrences of the given substring in the text.
Text.SelectSelects all occurrences of the given character or list of characters from the input text value.
Text.StartReturns the start of the text.
Text.StartsWithIndicates whether the text starts with a specified value.
Text.UpperConverts 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:

FunctionDescription
#datetimeCreates a datetime value from numbers representing the year, month, day, hour, minute, and (fractional) second.
#durationCreates a duration value from numbers representing days, hours, minutes, and (fractional) seconds.
#tableCreates a table value from columns and rows.
Date.StartOfMonthReturns the start of the month.
DateTime.FromCreates a datetime from the given value.
DateTime.LocalNowReturns the current date and time in the local timezone.
List.ContainsIndicates whether the list contains the value.
List.CountReturns the number of items in the list.
List.DatesGenerates a list of date values given an initial value, count, and incremental duration value.
List.DistinctReturns a list of values with duplicates removed.
List.SelectReturns a list of values that match the condition.
List.SortSorts a list of data according to the criteria specified.
Number.AbsReturns the absolute value of the number.
Number.IsEvenIndicates if the value is even.
Number.IsOddIndicates if the value is odd.
Number.RoundReturns the rounded number
Replacer.ReplaceTextReplaces text within the provided input.
Replacer.ReplaceValueReplaces values within the provided input.
Table.AddIndexColumnAppends a column with explicit position values.
Table.ColumnNamesReturns the column names as a list.
Table.CombineReturns a table that is the result of merging a list of tables.
Table.DemoteHeadersDemotes the column headers to the first row of values.
Table.DistinctRemoves duplicate rows from the table.
Table.FillDownPropagates the value of a previous cell to the null-valued cells below in the column.
Table.FillUpPropagates the value of a cell to the null-valued cells above in the column.
Table.GroupGroups rows in the table that have the same key.
Table.NestedJoinPerforms a join between tables on supplied columns and produces the join result in a new column.
Table.PivotGiven a pair of columns representing attribute-value pairs, rotates the data in the attribute column into a column headings.
Table.RemoveColumnsRemoves the specified columns.
Table.RenameColumnsApplies rename(s) of the form {old, new}.
Table.ReplaceValueReplaces one value with another in the specified columns.
Table.TransformColumnTypesApplies type transformation(s) of the form { column, type } using a specific culture.
Table.TransposeMakes columns into rows and rows into columns.
Table.UnpivotTranslates a set of columns in a table into attribute-value pairs.
Table.UnpivotOtherColumnsTranslates all columns other than a specified set into attribute-value pairs.
Text.AfterDelimiterReturns the portion of text after the specified delimiter
Text.BeforeDelimiterReturns the portion of text before the specified delimiter
Text.BetweenDelimitersReturns the portion of text between the specified startDelimiter and endDelimiter
Text.CleanReturns the text value with all control characters removed.
Text.PadEndReturns text of a specified length by padding the end of the given text.
Text.PadStartReturns text of a specified length by padding the start of the given text.
Text.PositionOfReturns the first position of the value (-1 if not found).
Text.RangeReturns the substring found at offset.
Text.RemoveRemoves all occurrences of the given character or list of characters from the input text value.
Text.SplitSplits 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:

FunctionDescription
Date.ToTextReturns a textual representation of the date value.
Duration.DaysReturns the days portion of a duration.
List.BufferBuffers a list.
List.DifferenceReturns the difference of the two given lists.
List.SkipReturns a list that skips the specified number of elements at the beginning of the list.
List.TransformReturns a new list of values computed from this list.
List.ZipReturns a list of lists by combining items at the same position in multiple lists.
Number.ModInteger divides two numbers and returns the remainder.
Number.ToTextFormats the given number as text.
Record.FieldReturns the value of the specified field in a record.
Record.FromListReturns a record given a list of field values and a set of fields.
Record.ToListReturns a list of values containing the field values of the input record.
Table.AddRankColumnAppends a column with the ranking of one or more other columns.
Table.BufferBuffers a table in memory, isolating it from external changes during evaluation.
Table.ColumnReturns a specified column of data from the table as a list.
Table.CombineColumnsCombines the specified columns into a new column using the specified combiner function.
Table.FirstNReturns the first count rows specified.
Table.FromColumnsCreates a table from a list of columns and specified values.
Table.FromListConverts a list into a table by applying the specified splitting function to each item in the list.
Table.FromRecordsConverts a list of records into a table.
Table.FromRowsCreates a table from a list of row values and optional columns
Table.LastNReturns the last specified number of rows.
Table.RemoveFirstNReturns a table with the first count rows skipped.
Table.RemoveLastNReturns a table with the last N rows removed.
Table.RepeatRepeats the rows of the tables a specified number of times.
Table.SelectColumnsReturns a table with only the specified columns.
Table.SkipReturns a table with the first count rows skipped.
Table.ToColumnsCreates a list of nested lists of column values from a table.
Table.ToRecordsConverts a table to a list of records.
Table.ToRowsCreates a list of nested lists of row values from a table.
Table.TransformColumnNamesTransforms column names by using the given function.
Table.TransformColumnsTransforms 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:

FunctionDescription
List.AccumulateAccumulates a summary value from the items in the list.
List.GenerateGenerates a list of values.
Table.FuzzyGroupGroups rows in the table based on fuzzy matching of keys.
Table.FuzzyNestedJoinPerforms a fuzzy join between tables on supplied columns and produces the join result in a new column.
Table.ProfileReturns a profile of the columns of a table.
Table.SchemaReturns a table containing a description of the columns (i.e
Table.StopFoldingPrevents any downstream operations from being run against the original source of the data.
Type.IsDetermines if a value of the first type is always compatible with the second type.
Type.TableSchemaReturns a table containing a description of the columns (i.e
Value.NativeQueryEvaluates a query against a target.
Value.ReplaceTypeReplaces the value’s type.
Value.TypeReturns 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!

Share on:

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.