The M Code Behind the Power Query M function Table.AddFuzzyClusterColumn

One of the M functions used in Power Query is the Table.AddFuzzyClusterColumn function. This function is used to create fuzzy clusters based on the similarity between values in a column. This article will explain the M code behind the Table.AddFuzzyClusterColumn function and how it can be used to cluster data.

What are Fuzzy Clusters?

Fuzzy clustering is a method of grouping data points based on their similarity. Unlike traditional clustering methods, which assign each data point to a single cluster, fuzzy clustering assigns each data point a degree of membership to each cluster. This allows data points to belong to multiple clusters at the same time.

Fuzzy clustering is useful when dealing with data that has overlapping characteristics or when a data point cannot be clearly assigned to a single cluster. For example, in customer segmentation, a customer may have characteristics that belong to multiple segments.

How Does the Table.AddFuzzyClusterColumn Function Work?

The Table.AddFuzzyClusterColumn function takes four parameters:

– Table: The table to which the fuzzy cluster column will be added.

– Column: The column on which the fuzzy clustering will be performed.

– Options: A record containing the options for clustering. This includes the number of clusters, the similarity metric, and the maximum number of iterations.

– NewColumnName: The name of the new column that will be added to the table.

The function works by first calculating the similarity between each value in the column using the specified similarity metric. It then performs an iterative process to assign each value to a cluster based on its similarity to the other values in the column. The process continues until the maximum number of iterations is reached or until the clusters converge.

The result of the Table.AddFuzzyClusterColumn function is a new column in the table that contains the fuzzy cluster assignments for each value in the column.

Example Usage of the Table.AddFuzzyClusterColumn Function

Suppose we have a table containing customer data with columns for age, income, and spending. We want to perform fuzzy clustering on the spending column to identify groups of customers with similar spending patterns.

We can use the Table.AddFuzzyClusterColumn function to add a new column to the table containing the fuzzy cluster assignments for each customer. The function call might look like this:




[NumberOfClusters=3, SimilarityMetric="EuclideanDistance"],



In this example, we are specifying that we want to create three clusters based on the Euclidean distance similarity metric. The resulting table will contain a new column called “FuzzyCluster” that contains the fuzzy cluster assignments for each customer.

The Table.AddFuzzyClusterColumn function is a powerful tool for performing fuzzy clustering in Power Query. By leveraging the M language, we can create custom functions and scripts to transform and cleanse data for analysis. Fuzzy clustering is useful for identifying groups of data points with overlapping characteristics, which can be valuable in customer segmentation, market research, and other applications.

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)