How to use Power Query Text.StartsWith() range of values

The Text.StartsWith() function in Power Query (M Language) is used to check if a text string starts with a specific substring. However, when you want to check if a text starts with any one of a range of values, you’ll need to slightly modify your approach because Text.StartsWith() natively handles only a single text value as the parameter.

Creating Test Data

Let’s say we have a table with a column named “ProductName” that contains various product names. We want to filter this table to show only the products whose names start with certain prefixes.

Here is an example of test data:

ProductName
Apple iPhone 12
Samsung Galaxy S21
Apple MacBook Air
Google Pixel 5
Samsung TV 42 Inch
Apple Watch Series 6
Google Home Mini
Microsoft Surface Pro
Dell XPS 13

Using Text.StartsWith() with a Range of Values

Let’s say you want to filter the products that start with either “Apple” or “Samsung”. Here’s how you can do this in Power Query:

1. Load the Data: Load your data into Power Query. You can do this by using Excel or Power BI to connect to your data source and then selecting the table you need.
2. Apply a Filter: To filter based on multiple potential starting strings (“Apple” or “Samsung”), you can use the List.AnyTrue() function along with Text.StartsWith().

Here is the M code to achieve this:

let
    // Sample data
    Source = Table.FromRows(
        {
            {"Apple iPhone 12"}, 
            {"Samsung Galaxy S21"}, 
            {"Apple MacBook Air"}, 
            {"Google Pixel 5"}, 
            {"Samsung TV 42 Inch"}, 
            {"Apple Watch Series 6"}, 
            {"Google Home Mini"}, 
            {"Microsoft Surface Pro"}, 
            {"Dell XPS 13"}
        },
        {"ProductName"}
    ),

    // Prefixes to check
    Prefixes = {"Apple", "Samsung"},

    // Function to check if any prefix matches
    StartsWithAnyPrefix = (productName as text) as logical =>
        List.AnyTrue(List.Transform(Prefixes, each Text.StartsWith(productName, _))),

    // Filtering the table
    FilteredData = Table.SelectRows(Source, each StartsWithAnyPrefix([ProductName]))
in
    FilteredData

Explanation

1. Source: This part of the code creates a table from the sample data.
2. Prefixes: This is a list containing the prefixes we are interested in (“Apple” and “Samsung”).
3. FilteredData: This step involves filtering rows in the table. List.Transform(Prefixes, each Text.StartsWith([ProductName], _)) transforms the list of prefixes into a list of true/false values, indicating whether the ProductName starts with each prefix. List.AnyTrue() then checks this list to see if any value is true (meaning that the product name starts with one of the specified prefixes).

Download Power Query Text.StartsWith() range of values

Let’s create a more complex scenario where we want to filter product names that start with different prefixes, but we also want to include some conditional logic based on another attribute. Suppose we have an additional column “Category” and we want to apply different start filters based on the category of the product.

Extended Test Data

Let’s enhance our previous table by adding a “Category” column:

ProductName	Category
Apple iPhone 12	Electronics
Samsung Galaxy S21	Electronics
Apple MacBook Air	Computers
Google Pixel 5	Electronics
Samsung TV 42 Inch	Appliances
Apple Watch Series 6	Electronics
Google Home Mini	Appliances
Microsoft Surface Pro	Computers
Dell XPS 13	Computers

Objective

We want to filter the data with the following conditions:

• For products in the “Electronics” category, the name should start with “Apple” or “Samsung”.
• For products in the “Computers” category, the name should start with “Apple” or “Microsoft”.

Using Text.StartsWith() with Conditional Logic

Here’s how you can apply these conditions in Power Query:

let
    // Sample data
    Source = Table.FromRows(
        {
            {"Apple iPhone 12", "Electronics"}, 
            {"Samsung Galaxy S21", "Electronics"}, 
            {"Apple MacBook Air", "Computers"}, 
            {"Google Pixel 5", "Electronics"}, 
            {"Samsung TV 42 Inch", "Appliances"}, 
            {"Apple Watch Series 6", "Electronics"}, 
            {"Google Home Mini", "Appliances"}, 
            {"Microsoft Surface Pro", "Computers"}, 
            {"Dell XPS 13", "Computers"}
        },
        {"ProductName", "Category"}
    ),

    // Helper function to check prefix
    StartsWithAny = (productName as text, prefixes as list) as logical =>
        List.AnyTrue(List.Transform(prefixes, each Text.StartsWith(productName, _))),

    // Conditional filtering
    FilteredData = Table.SelectRows(Source, each (
        ([Category] = "Electronics" and StartsWithAny([ProductName], {"Apple", "Samsung"})) or
        ([Category] = "Computers" and StartsWithAny([ProductName], {"Apple", "Microsoft"}))
    ))
in
    FilteredData

Explanation

1. Source: We define our data with the additional “Category” column.
2. FilteredData: Here’s the core logic:
   • We select rows from the source table based on conditions for each category:
     • For “Electronics”, we check if the product name starts with “Apple” or “Samsung”.
     • For “Computers”, we check if the product name starts with “Apple” or “Microsoft”.
   • We use List.Transform() to apply the Text.StartsWith() function to each prefix we’re interested in and then use List.AnyTrue() to check if any of those conditions are met.

How List.AnyTrue() function works?

List.AnyTrue() function in Power Query’s M language is a very useful tool for evaluating a list of boolean (true/false) values to determine if any of the values in the list are true. This function helps in scenarios where you need to confirm at least one condition is met within a set of conditions.

List.AnyTrue(list)

• list: This is a list of boolean values (true/false).

How it Works

List.AnyTrue() takes a list of boolean values as its input. It returns true if any of the values in the list is true. If all the values in the list are false, it returns false.

Example of List.AnyTrue()

Let’s consider a simple list of boolean values to demonstrate how List.AnyTrue() functions:

let
    // Create a list of boolean values
    BoolList = {true, false, false},

    // Apply List.AnyTrue()
    Result = List.AnyTrue(BoolList)
in
    Result

In this example, List.AnyTrue(BoolList) will return true because there is at least one true in the list.