The M Code Behind the Power Query M function Value.Alternates

Introduction to the M code behind Value.Alternates

The M code is the backbone of Power Query. It is a functional programming language used to define data transformations. Each function in Power Query has its M code behind it. The Value.Alternates function, for instance, has the following M code:

(Value as any, Alternates as list) =>


AlternatingFunction =

(index as number) =>

if List.Contains(Alternates, Value) then

Alternates{index mod List.Count(Alternates)}



Result = AlternatingFunction



This code takes two parameters: Value and Alternates. Value is the original value in the column, while Alternates is a list of alternative values. The function uses a nested function called AlternatingFunction to replace the original value with an alternate value.

Practical applications of Value.Alternates

Value.Alternates can be used in various scenarios. Here are some examples:

Example 1: Replacing missing values with an alternate value

In some cases, a dataset may have missing values. For instance, a sales dataset may have missing values in the “Region” column. Value.Alternates can be used to replace the missing values with an alternate value. Consider the following code:

#"Replaced Value" = Table.ReplaceValue(#"Previous Step",null,"Unknown",Replacer.ReplaceValue,{"Region"}),

#"Alternating Function" = Value.Alternates([Region],{"East","West","North","South"}),

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Unknown",#"Alternating Function",Replacer.ReplaceValue,{"Region"})

This code replaces the missing values with “Unknown” using the Table.ReplaceValue function. It then uses Value.Alternates to replace “Unknown” with an alternate value in the “Region” column.

Example 2: Alternating column values

Value.Alternates can be used to alternate column values. For instance, you may want to alternate the font color in a table column. Consider the following code:


#"Added Index" = Table.AddIndexColumn(#"Previous Step","Index",0,1),

#"Alternating Index" = Table.AddColumn(#"Added Index","Alternating Index", each Number.Mod([Index],2)),

#"Alternating Function" = Value.Alternates([#"Alternating Index"],{1,2}),

#"Removed Columns" = Table.RemoveColumns(#"Alternating Function",{"Index","Alternating Index"})


#"Removed Columns"

This code adds an index column to the table and then creates an alternating index column using the Number.Mod function. It then uses Value.Alternates to alternate the index column between 1 and 2. Finally, it removes the unnecessary columns.

Value.Alternates is a powerful M function that allows you to replace a value in a column with an alternate value. This function can be used in various scenarios, including replacing missing values and alternating column values. By understanding the M code behind Value.Alternates, you can harness the full potential of Power Query and streamline your data transformation process.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

10-12 Jul 23 (London or Online)
25-27 Sep 23 (London or Online)
20-22 Nov 23 (London or Online)

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)