Power Query: Check if Text Starts With a Capital Letter

As mentioned in previous articles, Power Query doesn’t natively support regular expressions, so we often need to create custom functions for specific string validations.

In this article, we’ll demonstrate how to build a simple text validation function that checks whether a string starts with an uppercase letter. While Power Query doesn’t offer a built-in feature for this, it’s easy to create using M code. The function returns TRUE if the first character is a capital letter (A–Z) and FALSE otherwise.

Instructions

Step 1: Create a New Function

1. From the Home ribbon in Power Query, select New Source > Other Sources > Blank Query. This creates a new blank query.
2. From the Home ribbon, click Advanced Editor.
3. Replace the default query with the following function:

let
  StartsWithCapital = (inputText as nullable any) as logical =>
    let
      isValid = inputText <> null
        and Value.Is(inputText, type text)
        and Text.Length(inputText) > 0,
      firstChar = if isValid then Text.Start(inputText, 1) else "",
      ascii = if isValid then Character.ToNumber(firstChar) else - 1,
      isCapital = ascii >= 65 and ascii <= 90
    in
      isCapital
in
  StartsWithCapital

4. Click the Done button.
5. In the Query Settings pane, under Properties, rename the query to StartsWithCapital.
6. Click Close & Load to exit Power Query.

Step 2: Testing the Function

To test the function:

1. From the Queries pane in Power Query, select StartsWithCapital.
2. A prompt will appear asking for a parameter. For inputText, enter Abc. The function call will look like this:

let
    Source = StartsWithCapital("Abc")
in
    Source

3. Click the Invoke button.
4. The result of the function will appear as a new query named Invoked Function in the Queries pane, returning the value TRUE as expected.

If I instead entered the value abc, the corresponding function call returns the value FALSE as expected.

let
    Source = StartsWithCapital("abc")
in
    Source

Results

Using sample data from an Excel table named TBL_TEST_DATA, you can apply the function across multiple rows using the following Power Query code:

let
  Source = Excel.CurrentWorkbook(){[Name = "TBL_TEST_DATA"]}[Content],
  #"Added Custom" = Table.AddColumn(
    Source,
    "Starts With Capital?",
    each StartsWithCapital([TEST DATA])
  )
in
  #"Added Custom"

The function returns TRUE for values beginning with a capital letter and FALSE for all other entries.

Summary

Checking whether a string starts with a capital letter in Power Query is simple with a custom M function. This method is efficient, reusable, and easily adaptable to your data models. It’s a helpful tool for improving data validation in your Power BI or Excel workflows.