Number.Permutations

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Number.Permutations

In this article, we will explore the M code behind the Number.Permutations function, and show how it can be used to calculate the number of permutations for a given set of items.

What are Permutations?

Permutations are a way of arranging a set of items in a particular order. For example, consider the set {1, 2, 3}. There are six possible permutations of this set:

– {1, 2, 3}

– {1, 3, 2}

– {2, 1, 3}

– {2, 3, 1}

– {3, 1, 2}

– {3, 2, 1}

Note that each permutation is a unique arrangement of the items in the set. Also, note that the number of possible permutations for a set of n items is given by n factorial (n!).

The Number.Permutations Function

The Number.Permutations function is a built-in function in Power Query that allows users to calculate the number of permutations for a given set of items. The syntax of the function is as follows:

Number.Permutations(n, k)

where n is the total number of items in the set, and k is the number of items to be arranged in each permutation.

For example, to calculate the number of permutations of a set of 5 items, where each permutation is a combination of 3 items, we can use the following formula:

Number.Permutations(5, 3)

This will return the value 60, which is the number of possible permutations for a set of 5 items, where each permutation is a combination of 3 items.

The M Code Behind the Number.Permutations Function

The Number.Permutations function is implemented in M code, which is the language used by Power Query to transform data. The M code behind the Number.Permutations function is as follows:

let

Source = List.Numbers(1, n),

Permutations = List.Generate(

() => [Current = {}, Count = 0],

each [Count] < Number.Combinations(List.Count(Source), k),

each [Current = List.Combine({{[Current]}, {List.RemoveItems(Source, [Current])}}), Count = [Count] + 1],

each [Current]{0}

),

Result = List.Count(Permutations)

in

Result

Let's break down the code and understand what each part does.

- The first line of the code defines a variable called "Source", which is a list of numbers from 1 to n. This represents the set of items that we want to arrange in permutations.

- The second line of the code defines a variable called "Permutations". This variable uses the List.Generate function to generate a list of all possible permutations of the items in the "Source" list.

- The List.Generate function takes four arguments:

- The first argument is a function that returns the initial state of the generator. In this case, we define the initial state as an empty list and a count of 0.

- The second argument is a function that determines whether the generator should continue. In this case, we continue generating permutations as long as the count is less than the number of possible combinations of the items in the "Source" list.

- The third argument is a function that generates the next state of the generator. In this case, we generate the next permutation by combining the current permutation with the remaining items in the "Source" list.

- The fourth argument is a function that returns the current state of the generator. In this case, we return only the current permutation, not the count.

- The third line of the code defines a variable called "Result", which is the final result of the function. This variable simply counts the number of permutations generated by the List.Generate function.

The Number.Permutations function is a powerful tool that allows users to calculate the number of permutations of a set of items in Power Query. The M code behind the function is complex, but by understanding how it works, users can take full advantage of its capabilities. By using the Number.Permutations function, users can quickly and easily calculate the number of permutations they need for their data analysis tasks.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)