Power Query - Lookup

[subirch]

Hi,

How can I have "Is Manager?" column output from the column "Employee Id" and "Manager Id"in power query?

Employee ID	Manager ID	Is Manager?
100	200	No
200	200	Yes
300	200	No
400	700	Yes
500	700	No
600	700	No
700	700	Yes
800	400	No
900	400	No
1000	400	No

[edhans · Accepted solution]

See this code @subirch 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgKTfvlKsTrRUA6EjEwtBosZYyozAXPMUZSZIolBlZlhCplj0WkB5pkgK7PEFAK6FlUsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Manager ID" = _t, #"Is Manager?" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Manager ID", Int64.Type}}),
    #"Added Is Manager" = 
        Table.AddColumn(
            #"Changed Type", "Is Manager", 
            each if List.Contains(#"Changed Type"[Manager ID], [Employee ID]) then "Yes" else "No")
in
    #"Added Is Manager"

It is a straight forward if/then/else construct with one function. The Key is this function: List.Contains(#"Changed Type"[Manager ID], [Employee ID])

It turns the Manager ID into a list, then scans that list to see if the Employee ID is contained in it, returning True or False.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[subirch]

Hi Jakinta,

Below error is appearing, where "Changed Type" is prior step.

Expression.Error: We cannot convert the value "Changed Type" to type Table.
Details:
Value=Changed Type
Type=[Type]

Thank you

[Jakinta]

#"Changed Type" 

[Jakinta]

= Table.AddColumn(  PriorStepName , "Is Manager?", each if List.ContainsAny ( List.Distinct( PriorStepName [#"Manager ID"]),  {[Employee ID]} ) then "Yes" else "No")