Compare all the colmma separated text values in one row/column to a list of required values

[jwynne]

I have been fighiting Power query for excel to try and find a way to compare and validate either of the scenerios below;

First, compare one cell which is full of comma seperated text values (Qualifications) to another cell of comma seperated text values (Neccesary Qualifications) then dispaying an "yes" or "no" in a new column (Meets Qualifications) if the first cell contains all the items in the other cell.

Second, compare one cell full of comma seperated text values (Qualifications) to an list (query with just a table of values) resulitng in either an "yes" or "no" in a new column confirming that the first cell contains all the items in the referenced list.

Qualifications	Neccesary Qualifications	Meets Qualifications
MAGOMA,TWIC,MAGPLE,NUSCOU	MAGOMA,MAGPLE,NUSCOU	YES
TWIC,MAGPLE,NUSCOU	MAGOMA,MAGPLE,NUSCOU	NO
MAGPLE,NUSCOU,TWIC,PHIROC	NUSCOU,MAGPLE	YES

[ronrsnfld · Accepted solution]

Just add a custom column with the formula:

=if List.ContainsAll(Text.Split([Qualifications],","),Text.Split([Neccesary Qualifications],",")) then "Yes" else "No"

[jwynne]

I swear I tried that about a dozen times. That worked, THANK YOU!!