Forum Discussion
Power Query: If (nested or second table) contains statement
That could be like this, our queries are
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)
Similar for Contains
Merge both above to have both lists in one List Combined table
Reference Main query as Final, add above Lookup Combined table to new column here
and expand it.
Add new custom column to check KeywordsList against Contains/Not Contains
Group by initial columns without aggregation keeping in resulting table of each group only rows where previous value is true.
Expand Result from it
and finally combine with Prediction
Details are in attached file.
I have attached a sample file below (with the end results filled in, but obviously remove those as needed). Thank you again!
That could be like this, our queries are
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)
Similar for Contains
Merge both above to have both lists in one List Combined table
Reference Main query as Final, add above Lookup Combined table to new column here
and expand it.
Add new custom column to check KeywordsList against Contains/Not Contains
Group by initial columns without aggregation keeping in resulting table of each group only rows where previous value is true.
Expand Result from it
and finally combine with Prediction
Details are in attached file.
Joe_JL , you are welcome, glad it helped
- SergeiBaklan This worked great, I made a few minor tweaks and it ended up being exactly what I needed. Thank you!
- Thank you! Let me try this and I will let you know how it works. Thank you again for your help, really appreciate it!