Number.Combinations

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the Number.Combinations function and provide examples of how it can be used to solve real-world data problems.

Understanding Number.Combinations

The Number.Combinations function is used to calculate the number of possible combinations of a given set of items. It takes two arguments:

– The first argument is a list of items that will be used to generate combinations.

– The second argument is an integer that specifies the number of items to include in each combination.

The function returns a table that contains all possible combinations of the specified number of items from the input list.

For example, suppose we have a list of four items: A, B, C, and D. We want to generate all possible combinations of three items from this list. We would use the following M code:

Number.Combinations({“A”, “B”, “C”, “D”}, 3)

This would return a table with the following rows:

A B C

A B D

A C D

B C D

Note that the order of the items within each combination does not matter.

Using Number.Combinations in Practice

The Number.Combinations function can be used to solve a variety of real-world data problems. Here are some examples:

Example 1: Generating Product Combinations

Suppose we have a table that contains a list of products and we want to generate all possible combinations of products that a customer could purchase. We can use the Number.Combinations function to generate these combinations.

Assuming we have the following table:

| Product |

|---------|

| A |

| B |

| C |

We can use the following M code to generate all possible combinations of two products:

Number.Combinations(Table.Column(Table1, “Product”), 2)

This would return a table with the following rows:

A B

A C

B C

Example 2: Generating Unique Combinations

Suppose we have a table that contains a list of sales transactions. Each transaction includes a customer ID and a product ID. We want to generate a list of all possible combinations of customers and products, but we want to ensure that each combination is unique.

Assuming we have the following table:

| Customer | Product |

|----------|---------|

| 1 | A |

| 2 | B |

| 1 | C |

| 3 | A |

We can use the following M code to generate all unique combinations of customers and products:

let

Source = Table.FromRecords({

[Customer = 1, Product = “A”],

[Customer = 2, Product = “B”],

[Customer = 1, Product = “C”],

[Customer = 3, Product = “A”]

}),

AllCustomers = Table.Distinct(Source, {“Customer”}),

AllProducts = Table.Distinct(Source, {“Product”}),

UniqueCombinations = Table.Combine(

List.Transform(

Table.ToRecords(AllCustomers),

each Table.SelectRows(AllProducts, each [Product] <> [Product])

)

)

in

UniqueCombinations

This would return a table with the following rows:

Customer Product

1 B

1 A

2 A

2 C

3 B

3 C

Note that each combination is unique and there are no duplicate rows.

The Number.Combinations function is a powerful tool that can be used to generate all possible combinations of a given set of items. In this article, we explored the M code behind this function and provided examples of how it can be used to solve real-world data problems.

If you are a data analyst or developer who works with Power Query M, the Number.Combinations function is definitely worth adding to your toolkit.

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)