Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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”.
Solved! Go to Solution.
if List.ContainsAny(Text.ToList([A]), {"1","2","3","4","5","6","7","8","9","0"}) or [A] = "GLAMRO" then "EXTERNAL" else "INTERNAL"
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"
if List.ContainsAny(Text.ToList([A]), {1,2,3,4,5,6,7,8,9,0}) then "EXTERNAL" else "INTERNAL"
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.
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"
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?
The formula works also with Blank Rows
I'll try first to made a Trim and Clean to the Column to delete other characters.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |