SOLVED

Power Query: If (nested or second table) contains statement

%3CLINGO-SUB%20id%3D%22lingo-sub-2827953%22%20slang%3D%22en-US%22%3EPower%20Query%3A%20If%20(nested%20or%20second%20table)%20contains%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2827953%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20latest%20version%20of%20excel%20and%20I%20am%20trying%20to%20create%20multiple%20lookups%20using%20power%20query%20(it%20has%20to%20be%20in%20power%20query%20for%20speed%20reasons).%20what%20I%20am%20envisioning%20is%20Having%20my%20main%20table%20look%20to%20a%20lookup%20table%20and%20then%20if%20column%20in%20main%20table(keywords)%20contains%20a%20value%20in%20lookup%20table%20column(contain)%20and%20does%20not%20contain%20a%20value%20in%20lookup%20table%20column(not%20contain)%20then%20return%20result%20from%20lookup%20table%20column(result)%20else%20return%20main%20table(Prediction).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20nesting%20the%20lookup%20table%20into%20the%20main%20table%20would%20work%20but%20I%20cannot%20figure%20out%20how%20to%20make%20a%20lookup%20statement%20without%20expanding%20the%20columns%2C%20and%20expanding%20the%20columns%20is%20not%20an%20option%20as%20it%20will%20create%20way%20too%20many%20duplicates.%20Thank%20you%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMain%20Table%3C%2FSTRONG%3E%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CU%3EKeywords%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CU%3EPrediction%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CU%3EColumn%20to%20create%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECucumber%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EVeggie%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EVeggie%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ELemon%2C%20Oranges%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECitrus%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECitrus%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELemon%2C%20Sugar%3C%2FTD%3E%3CTD%3ECitrus%3C%2FTD%3E%3CTD%3ELemonade%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ELookup%20Table%3C%2FSTRONG%3E%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2256.25%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CU%3EContain%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CU%3ENotContain%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CU%3EResult%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ELemon%20AND%20sugar%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EOrange%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ELemonade%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2827953%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828009%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20If%20(nested%20or%20second%20table)%20contains%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1177685%22%20target%3D%22_blank%22%3E%40Joe_JL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20data%20structuring.%20In%20main%20table%20you%20have%20%22%3CSPAN%3ELemon%2C%20Sugar%22%2C%20in%20lookup%20table%20%22Lemon%20AND%20sugar%22.%20Is%20that%20intentionally%2C%20or%20in%20lookup%20table%20it%20could%20%22Lemon%20AND%20sugar%22%2C%20or%20%22Lemon%2C%20sugar%22%20or%20%22Lemon%2C%20Sugar%22%2C%20whatever.%20What%20is%20exact%20format%20if%20you%20have%20it%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828031%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20If%20(nested%20or%20second%20table)%20contains%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20looking%20at%20this!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20was%20saying%20by%20that%20structure%20was%2C%20I%20would%20like%20to%20be%20able%20to%20have%20the%20statement%20say%3A%20it%20say%20if%20it%20contains%20Lemon%20and%20Sugar%20but%20does%20not%20contain%20Orange%20then%20result%20is%20lemonade.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20in%20retrospect%20I%20think%20it%20would%20be%20best%20formatted%20this%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CU%3EContain%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CU%3EAlsoContain%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CU%3ENotContain%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CU%3EResult%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ELemon%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ESugar%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EOrange%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ELemonade%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828058%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20If%20(nested%20or%20second%20table)%20contains%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1177685%22%20target%3D%22_blank%22%3E%40Joe_JL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you.%20I%20hope%20I%20understand%20the%20idea%2C%20but%20depends%20on%20how%20your%20data%20is%20represented%20Power%20Query%20could%20be%20totally%20different.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828201%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20If%20(nested%20or%20second%20table)%20contains%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828201%22%20slang%3D%22en-US%22%3EI%20appreciate%20your%20help%2C%20would%20it%20be%20helpful%20If%20I%20created%20a%20demo%20table%20structure%20in%20Power%20Query%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone,

 

I am using the latest version of excel and I am trying to create multiple lookups using power query (it has to be in power query for speed reasons). what I am envisioning is Having my main table look to a lookup table and then if column in main table(keywords) contains a value in lookup table column(contain) and does not contain a value in lookup table column(not contain) then return result from lookup table column(result) else return main table(Prediction).

 

I thought nesting the lookup table into the main table would work but I cannot figure out how to make a lookup statement without expanding the columns, and expanding the columns is not an option as it will create way too many duplicates. Thank you for your help!

 

Main Table

KeywordsPredictionColumn to create
CucumberVeggieVeggie
Lemon, OrangesCitrusCitrus
Lemon, SugarCitrusLemonade

 

 

Lookup Table

ContainNotContainResult
Lemon AND sugarOrangeLemonade
   
   
   
9 Replies

@Joe_JL 

Could you please clarify data structuring. In main table you have "Lemon, Sugar", in lookup table "Lemon AND sugar". Is that intentionally, or in lookup table it could "Lemon AND sugar", or "Lemon, sugar" or "Lemon, Sugar", whatever. What is exact format if you have it?

@Sergei Baklan Thank you for looking at this!

 

What I was saying by that structure was, I would like to be able to have the statement say: it say if it contains Lemon and Sugar but does not contain Orange then result is lemonade. 

 

However, in retrospect I think it would be best formatted this way.

 

ContainAlsoContainNotContainResult
LemonSugarOrangeLemonade
    

@Joe_JL 

Thank you. I hope I understand the idea, but depends on how your data is represented Power Query could be totally different.

I appreciate your help, would it be helpful If I created a demo table structure in Power Query?

@Joe_JL 

Yes, that will be great. Sample file is always welcome.

@Sergei Baklan 

 

I have attached a sample file below (with the end results filled in, but obviously remove those as needed). Thank you again!

best response confirmed by Joe_JL (Occasional Contributor)
Solution

@Joe_JL 

That could be like this, our queries are

image.png

Lookup keep as it is.

In Main query add column with list of keywords by Text.Split() of Keywords field.

Reference Lookup and create Not Contains keeping only Result and Not Contain columns, unpivot the latest, Group by Result without aggregation keeping only Values column. Thus we have list of Not Contain words for each Result (if exists)

image.png

Similar for Contains

image.png

Merge both above to have both lists in one List Combined table

image.png

Reference Main query as Final, add above Lookup Combined table to new column here

image.png

and expand it. 

Add new custom column to check KeywordsList against Contains/Not Contains

image.png

Group by initial columns without aggregation keeping in resulting table of each group only rows where previous value is true.

Expand Result from it

image.png

and finally combine with Prediction

image.png

Details are in attached file.

Thank you! Let me try this and I will let you know how it works. Thank you again for your help, really appreciate it!
@Sergei Baklan This worked great, I made a few minor tweaks and it ended up being exactly what I needed. Thank you!