Table.FilterWithDataTable

In this article, we will take a deep dive into the M code behind the Power Query M function Table.FilterWithDataTable. We will explore its capabilities, syntax, and usage scenarios.

What is Table.FilterWithDataTable Function?

Table.FilterWithDataTable function is a Power Query M function that filters a table based on the contents of another table. The function takes two tables as inputs: the source table and the filter table. The filter table contains the filter criteria, which are used to filter the source table.

The function returns a table that contains only the rows from the source table that match the filter criteria specified in the filter table.

Syntax of Table.FilterWithDataTable Function

The syntax of the Table.FilterWithDataTable function is as follows:

– `table` is the source table that needs to be filtered.

– `filterTable` is the table that contains the filter criteria.

– `joinColumns` is a list of column names that are used to join the two tables. The column names must be present in both the source table and the filter table.

– `columnsToInclude` is an optional parameter that specifies the list of columns to include in the output table. If this parameter is not specified, all columns are included in the output table.

How to use Table.FilterWithDataTable Function?

Let’s take an example to understand how to use the Table.FilterWithDataTable function.

Suppose we have two tables: `Sales` and `Filter`. The `Sales` table contains the sales data of a company, and the `Filter` table contains the list of products that need to be included in the analysis. We want to filter the `Sales` table to include only the products that are present in the `Filter` table.

The `Sales` table looks like this:

| Product | Region | Sales |

|———|——–|——-|

| A | North | 100 |

| B | South | 200 |

| C | East | 300 |

| D | West | 400 |

| E | North | 500 |

| F | South | 600 |

The `Filter` table looks like this:

| Product |

|———|

| A |

| C |

| E |

To filter the `Sales` table to include only the products present in the `Filter` table, we can use the Table.FilterWithDataTable function as follows:

The output of the above code will be a table that looks like this:

| Product | Region | Sales |

|———|——–|——-|

| A | North | 100 |

| C | East | 300 |

| E | North | 500 |

Table.FilterWithDataTable function is a powerful function in Power Query that allows users to perform bulk filtering operations on tables. The function is useful in scenarios where we need to filter a large amount of data using complex filter criteria.

In this article, we explored the syntax and usage scenarios of the Table.FilterWithDataTable function. We learned how to use the function to filter a table using another table and saw an example of how to apply the function in practice.

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

Upcoming Courses

Contact Us