Power Query: Check if Text Value is Alphabetic Only

When working with data in Power BI or Excel, it’s common to need checks for data quality — such as verifying that a value contains only alphabetic characters (A–Z or a–z). Power Query doesn’t have a built-in function for this, but you can easily implement your own solution.

In this article, we’ll explore two practical ways to perform this check using Power Query. The first method uses a reusable custom function. The second method uses an inline if-then-else expression with Text.Select to achieve the same result.

Let’s walk through each approach step-by-step.

Instructions

Method 1: Custom Power Query Function

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
  IsAlphaOnly = (inputText as nullable any) as logical =>
    let
      result =
        if inputText = null then
          false
        else if not Value.Is(inputText, type text) then
          false
        else
          let
            chars = Text.ToList(Text.Lower(inputText)),
            charList = List.Transform(
              chars,
              each Character.FromNumber(Character.ToNumber(_)) >= "a"
                and Character.FromNumber(Character.ToNumber(_)) <= "z"
            ),
            allAlpha = List.AllTrue(charList)
          in
            allAlpha
    in
      result
in
  IsAlphaOnly

4. Click the Done button.
5. In the Query Settings pane, under Properties, rename the query to IsAlphaOnly.
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 IsAlphaOnly.
2. A prompt will appear asking for a parameter. For inputText, enter abc. The function call will look like this:

let
    Source = IsAlphaOnly("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 abc1xyz, the corresponding function call returns the value FALSE as expected.

let
    Source = IsAlphaOnly("abc1xyz")
in
    Source

Method 2: Use Text.Select with If-Then-Else

This approach uses Text.Select to filter out non-letter characters, then compares the result to the original text.

if [TEST DATA] = null or not Value.Is([TEST DATA], type text) then
  false
else if Text.Select(Text.Lower([TEST DATA]), {"a" .. "z"}) = Text.Lower([TEST DATA]) then
  true
else
  false

Results

Using sample data in an Excel table named TBL_TEST_DATA, we compare the results from both methods using the following Power Query code.

let
  Source = Excel.CurrentWorkbook(){[Name = "TBL_TEST_DATA"]}[Content],
  #"Added Custom - IsAlphaOnly Function" = Table.AddColumn(
    Source,
    "Is Alpha Only? (Function)",
    each IsAlphaOnly([TEST DATA])
  ),
  #"Added Custom - Text.Select" = Table.AddColumn(
    #"Added Custom - IsAlphaOnly Function",
    "Is Alpha Only? (Text.Select)",
    each
      if [TEST DATA] = null or not Value.Is([TEST DATA], type text) then
        false
      else if Text.Select(Text.Lower([TEST DATA]), {"a" .. "z"}) = Text.Lower([TEST DATA]) then
        true
      else
        false
  )
in
  #"Added Custom - Text.Select"

Both methods return TRUE for alphabetic-only values and FALSE for entries that contain numbers, symbols, or are null.

Summary

Verifying alphabetic-only text in Power Query is simple with either a custom function or using Text.Select. Both methods are effective and easy to apply depending on your needs. Choose the one that fits best with your workflow and data model.