Conditional format for Alpha-numerical Columns

[mdrammeh]

I have a column with alphanumerical values identifying the difference between INTERNAL versus EXTERNAL contact.

A	B
MUST1234	EXTERNAL
HAVENO	INTERNAL
NOHAV12	EXTERNAL
MITE1233	EXTERNAL
GLAMRO	INTERNAL
FAMINW	INTERNAL

I want to create a conditional lookup in Power Query to say IF column “A” contains a number, return External, Other “INTERNAL”.

[mdrammeh]

What is I want to make an exception to the rule to capture one or two of the descriptions that does not have a value as part of the description and call it "External". 

Example "

A	B
MUST1234	EXTERNAL
HAVENO	INTERNAL
NOHAV12	EXTERNAL
MITE1233	EXTERNAL
GLAMRO	External
FAMINW	INTERNAL

[Greg_Deckler · Accepted solution]

if List.ContainsAny(Text.ToList([A]), {"1","2","3","4","5","6","7","8","9","0"}) or [A] = "GLAMRO" then "EXTERNAL" else "INTERNAL" 

[Greg_Deckler]

if List.ContainsAny(Text.ToList([A]), {1,2,3,4,5,6,7,8,9,0}) then "EXTERNAL" else "INTERNAL"

[mdrammeh]

I must have missed something in the formula that it's returning an error. Also, the result only returned for "Internal" even though I had values that include a text in the description. 

[Vvelarde]

@mdrammeh

Hi, just add a " " en each number

if List.ContainsAny(Text.ToList([TEXTFIELD]), {"1","2","3","4","5","6","7","8","9","0"}) then "EXTERNAL" else "INTERNAL"

[mdrammeh]

It worked perfect except the error message. The column with the values also had blank rows that was not being picked up by the formula. Is there a workaround? 

[Vvelarde]

@mdrammeh

The formula works also with Blank Rows

I'll try first to made a Trim and Clean to the Column to delete other characters.

[mdrammeh]

Sounds good. one more question. What if I want to create an exception to the rule by picking up a text description from the list.