powerquery

Use PowerQuery to Determine if a name from a list of names is missing from the cell

I want to use PowerQuery to check if a cell contains the name of a supervisor by checking another query that contains the list of supervisors' names, and to put "Missing" in the new column if the supervisor name is missing.

My dataset is pretty large, but I am including a small sample to illustrate how I would like the end result to appear.

Missing Supervisor Signature

Solution 
  • let
    Source = Excel.CurrentWorkbook(){[Name="MainTable"]}[Content],
    SupervisorsList = Excel.CurrentWorkbook(){[Name="SupervisorsList"]}[Content][Supervisors],
    CheckMissing = Table.AddColumn(Source, "Supervisor Missing", each 
        if List.Count(List.Intersect({Text.Split([Signed By], ", "), SupervisorsList})) = 0 then "Missing" else "")
in
    CheckMissing