Answered by:
Multilevel "If Text.Contains"
Question

Hi guys,
complicated question today.
I want to run 2level "If Text.Contains".
Here is my scenario:
 Values in column A:
 a0
 b1
 c2
 Formulas in column B:
 If Text.Contains([A],"a") then "A" else
If Text.Contains([A],"b") then "B" else  If Text.Contains([A],"c") then "C" else
 If Text.Contains([A],"0") then "0" else
If Text.Contains([A],"1") then "1" else  If Text.Contains([A],"2") then "2" else "Generic"
 Formulas in Column C:
 ????
In Column C, I want to use similar set of formulas as in column B but I need to always "skip" the first found match. So for A0 row, I would see A0 in column A, A in column B, and 0 in columns C.
Any tips?
Thx!
 Edited by Daniel Zrust Wednesday, February 17, 2016 3:49 PM
Answers

 Proposed as answer by Ehren  MSFTMicrosoft employee, Owner Friday, February 19, 2016 12:16 AM
 Marked as answer by Michael AmadiModerator Thursday, April 7, 2016 5:21 AM
All replies

What is your overall goal? You have one column in your worksheet and want to see a table that contains what?
Your formulas in column B wouldn't work as they stand here: elses missing. Have the impression that there must be a much easier way.
Imke Feldmann TheBIccountant.com

sorry, yes "else"s are missing there.
The deal is that I have about 100 + 100 "if contains" functions generated. Every set of these functions ends with else "Generic".
I run 1st hundred through column A. Results of this are stored in Column B. Then I run 2nd hundred again through column A. Results are stored in Columns C.
Some rows from the first run are resulting as "Generic", both in column B and C. Some as something+something. Some as "Generic"+something.
For combinations which return "Generic" in Col B and "something" in Col C, I want to run next round of checks.
This time, I would run 2nd set of those 100 "if contains" through Col A and then I would like to run 2nd set of those 100 if contains through Col A again while skipping the previously found value. if I dont skip the previously found value I would see something like something from 2nd 100 + something from 2nd 100. The two found values would be the same.
The goal is to minimize "Generic"+"Generic" combinations while I know that rules from 2nd 100 can help me achieve that. In these cases, I also need to take care of results from 2nd 100 since I dont want to see something from 2nd 100 + something from 2nd 100 but rather something from 2nd 100 + something v2 from second 2nd
Does it make sense?
The trouble is that the order of if contains matters a lot for me.
DZ
 Edited by Daniel Zrust Wednesday, February 17, 2016 3:49 PM

Maybe I would understand it a little if the first checks worked like filters: if Text.Contains([A], "a")=true then "in" else "out" then filtering on "in" aso
But I seem to miss the big picture here. Sorry, cannot help without visualising the overall goal here.
Imke Feldmann TheBIccountant.com

Ok, let's try it with "real data". Table with one row.
column A: "big beautiful boat"
Vehicles: column B:
If text.contains([A],"car") then "Car" else
If text.contains([A],"bus") then "bus" else "Generic"
=>"Generic"
Attributes: column C:
If Text.Contains([A],"big") then "Big" else
If Text.Contains([A],"small") then "Small"
If Text.Contains([A],"beautiful") then "Beautiful" else "Generic"
=>"Big
Results of these operations would be "Generic" + "Big" but I want to see "Big" + "Beautiful" if we figure out "multilevel if contains" :)
Does it make sense?
DZ
 Edited by Daniel Zrust Wednesday, February 17, 2016 4:05 PM

Better :)
So the max number of returned found values will always be 2?
if it would be "big beautiful Car" you expect "Car" + "big"?
if it would be "big beautiful Carport" you expect "Car" + "big"?
What if "Carport" comes later in the search terms? It would be ignored then because there are already 2 matches?
Shall the matches be case sensitive?
Imke Feldmann TheBIccountant.com

Better :)
So the max number of returned found values will always be 2? YES
if it would be "big beautiful Car" you expect "Car" + "big"? YES
if it would be "big beautiful Carport" you expect "Car" + "big"? YES
What if "Carport" comes later in the search terms? It would be ignored then because there are already 2 matches? 
Not sure what you mean  I am only interested in the "big" + "beautiful" set of rules to run as "multilevel".
Shall the matches be case sensitive? Not really, I am running everything lower case anyway.
Imke Feldmann TheBIccountant.com

Have a look if this example does what you expect. Please have a special check at the "green haired monkey".
Can provide explanations on how this works if that's what you need.
Imke Feldmann TheBIccountant.com


? I thought you had about 100 Levels ?
Would this be your complete solutions if the other values would turn out (with "generic") as you described in your file or would you consider this as the start to some loops/repeats?
Imke Feldmann TheBIccountant.com


Totally sure about the small ugly lorry? both matches come from the Level2!
Imke Feldmann TheBIccountant.com

If there were 3 matches in Level 2: Which ones to take?
Imke Feldmann TheBIccountant.com

If there were 2 matches in Level 1 already (and another one in Level 2): Which ones to take?
Imke Feldmann TheBIccountant.com


 Proposed as answer by Ehren  MSFTMicrosoft employee, Owner Friday, February 19, 2016 12:16 AM
 Marked as answer by Michael AmadiModerator Thursday, April 7, 2016 5:21 AM
