Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sparvez
Helper I
Helper I

Verify if column contains letter

Hello altruists,

 

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

Thank you in advance.

1 ACCEPTED SOLUTION

Hi @sparvez 

 

You can add a custom column with below code.

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

vjingzhang_1-1675047538700.png

 

If you want it to ignore case, you can use

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

vjingzhang_2-1675047732276.png

 

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

View solution in original post

7 REPLIES 7
nickvanmaele
Advocate II
Advocate II

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
Helper I
Helper I

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.

 

Hi @sparvez 

 

You can add a custom column with below code.

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

vjingzhang_1-1675047538700.png

 

If you want it to ignore case, you can use

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

vjingzhang_2-1675047732276.png

 

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

This doesn't work


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

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
Advocate II
Advocate II

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
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors