Verify if column contains letter

[sparvez]

Hello altruists,

How to verify in Power Query if certain column contains letters  ( from A to Z ) ?

Thank you in advance.

[nickvanmaele]

Hi,

If you want to check for any letter, you can simply modify my above solution by setting:

listLettersToCheckFor = { "A" .. "Z"}

The solution will work immediately. 

Since it has only 3 steps (or even just 2 if you combine them), it is already quite simple.

You could also make a custom function out of the above which returns TRUE or FALSE. 

[sparvez]

Thank you all , but is there any simple way to do this ?  I just want to verify whether contains any of 26 letters of English Alphabet.

[v-jingzhang · Accepted solution]

Hi @sparvez 

You can add a custom column with below code.

Text.Length(Text.Select([Column1],{"A".."Z"}))>0

If you want it to ignore case, you can use

Text.Length(Text.Select([Column1],{"A".."Z","a".."z"}))>0

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[rusgesig]

This doesn't work

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=
serial=AEA413

[ronrsnfld]

Your question is very vague. The more information you provide, the better result you will obtain. For example, a "simple" method would be to visually scan the column until you see a letter; if you do then you have verified one is present. 

Providing a representative data sample as text which can be copy/pasted, and at least a screenshot of your expected outcome, might result in a more focused suggestion.

[nickvanmaele]

Hi @sparvez ,

You can try splitting your values, according to this solution by @RickdeGroot 

Description of solution

Using the above technique, I created the example further below:

• pqInputData is a table with one colum which contains the data you want to check.
  • The column header is "Data"
  • The data in this example contain letters, strings, and numbers. 
• listLetterstoCheckFor is the list of letters or strings that you want to check for

The solution will first split your data by the values in list 'listLettersToCheckFor'. 

It will then create a second column with a count of the lists obtained in the first step. If the count is greater than 1, then the letter occurred in your original data. 

Finally, the solution filters on all the rows where your original data contain the sought letter. 

By adapting 'listLettersToCheckFor' to what you need, you have your own solution. 

Data: 

Source table 'pqInputData' represents the table below, where "Data" is the column header:

Data

A

a

Abby

ABBY

abby

note

bear

Alaska

1

234

1a23

listLettersToCheckFor = { "a" }  //case-sensitive

Solution:

Query pqCheckForLetters has the following code: 

let
Source = pqInputData,
#"Split text by delimiter" = Table.AddColumn(Source, "Splitter", each Splitter.SplitTextByAnyDelimiter(listLettersToCheckFor) ([Data])),
#"Count nbr of split items" = Table.AddColumn(#"Split text by delimiter", "CountSplitter", each List.Count([Splitter])),
#"Items with sought letter" = Table.SelectRows(#"Count nbr of split items", each [CountSplitter] > 1)
in
#"Items with sought letter"

If you like this answer, please accept it as solution. 

[BA_Pete]

Hi @sparvez ,

*BUMP* Bumping this post as it seems to be invisible 🙂

You could try something like this:

not List.IsEmpty(
    List.Intersect(
        {
            {"A".."Z"},
            Text.ToList([YourStringColumn])
        }
    )
)

Pete