Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |