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
mdrammeh
Helper III
Helper III

Conditional format for Alpha-numerical Columns

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”.

1 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" 

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
mdrammeh
Helper III
Helper III

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

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

 

Screenshot.-Today.PNG

@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"



Lima - Peru

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? 

@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.




Lima - Peru

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. 

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.