How to Extract Only the Rows Matching a List from a Table in Power Query

Описание: #PowerQuery #DataExtraction #ExcelTips #PowerQueryTutorial #DataCleaning

"Copy the link below to get the sample file."
https://docs.google.com/spreadsheets/...

1. M Code

let
Source = Customers,
#"Filtered Rows" = Table.SelectRows(Source, each List.Contains(CustomerID, [CustomerID]))
in
#"Filtered Rows"

2. Step-by-Step Explanation

Step 1: Source = Customers
This step loads the Customers table, which contains all customer data you want to filter.

Step 2:
#"Filtered Rows" = Table.SelectRows(Source, each List.Contains(CustomerID, [CustomerID]))

Table.SelectRows is a function that keeps only the rows that meet a specific condition.

The each keyword means "for each row in the table, do this".

List.Contains checks whether the value [CustomerID] (from this row) exists in the CustomerID list.

For example:

List.Contains({"ANATR", "BERGS", "BONAP"}, "BERGS") // returns true

So the row with CustomerID = "BERGS" will be included.

In summary:
This step filters the Customers table to include only those rows where the CustomerID matches one in your predefined list.
All other rows are removed.

Step 3: in #"Filtered Rows"
This simply outputs the filtered table from Step 2 as the result of the query.

3. Prerequisites
You need to prepare a CustomerID list first.

For example, you can create this list from the CustomerID column in your CustomerIDTable as follows:

CustomerID = CustomerIDTable[CustomerID]

This will create a list like:
{ "ANATR", "BERGS", "BONAP", "CENTC", "DRACD", "FISSA", "EASTC" }

Note:
Both the Customers table and the CustomerIDTable should be loaded as "Connection Only" in Power Query.
This means you don’t need to load them to an Excel sheet, but they must be available in Power Query so you can reference them in your filtering query.

4. Summary (One-liner)
This M code filters the Customers table to keep only the customers whose ID is in a predefined list, using Table.SelectRows and List.Contains.