Reply
Regular Visitor
Posts: 22
Registered: ‎01-16-2019
Accepted Solution

Check if each row in a column contains A through Z

I all I've used Text.Remove([Pol Ref], {"0".."9"}), to quickly remove all numric values from a text string column with no problems.

 

I wanted to create a similar, type of custom column, this time to check if each row in a column contains A through to Z

 

So I create another custom column, with the following M code

 

if
  Text.Contains([Postcode], {"A".."Z"})
then "Keep"
 
else "Remove"

 

However the it returns Error on each row for my Postcode column, advising

 

"Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=List
    Type=Type"

 

I'm rather new to programming, and new to programming in M.  Can anyone advise a solution for such a newbie?


Accepted Solutions
Regular Visitor
Posts: 22
Registered: ‎01-16-2019

Re: Check if each row in a column contains A through Z

@Zubair_Muhammad

 

Thank you,  we hit a error at the "then" section of the M coding

 

If seems like M does not allow to create conditional formula onto lists?!?

View solution in original post

Super User
Posts: 2,820
Registered: ‎09-27-2017

Re: Check if each row in a column contains A through Z

@101Mathew

 

It works when I use some sample data

Take a look at attached Excel file's query editor

 

werewr.png

View solution in original post


All Replies
Highlighted
Super User
Posts: 2,820
Registered: ‎09-27-2017

Re: Check if each row in a column contains A through Z

@101Mathew

 

Try this revision

 

=if
List.ContainsAny(Text.ToList([Column1]),{"a".."z"}&{"A".."Z"})
then
"Keep"
else
"Remove"
Regular Visitor
Posts: 22
Registered: ‎01-16-2019

Re: Check if each row in a column contains A through Z

@Zubair_Muhammad

 

Thank you,  we hit a error at the "then" section of the M coding

 

If seems like M does not allow to create conditional formula onto lists?!?

Super User
Posts: 2,820
Registered: ‎09-27-2017

Re: Check if each row in a column contains A through Z

@101Mathew

 

It works when I use some sample data

Take a look at attached Excel file's query editor

 

werewr.png

Regular Visitor
Posts: 22
Registered: ‎01-16-2019

Re: Check if each row in a column contains A through Z

@Zubair_Muhammad

 

Thank you, and sorry

 

I made a mistake with the brackets, it's annoying that Power Query does not give better feedback on issues with my programming.

 

It now works very well.

 

Cheers

Mathew