Lookup Between Two Queries

[shaebert]

Hello, 

I am trying to do a lookup between two queries. I am looking to see if the Domain & Campaign ID values in a row from query 1 can be found in Query 2. If true "Yes" else "No." 

Thanks for the help!

Query 1

Fname	LName	Company	Domain	Campaign ID	Lead	Quote Created?
John	Doe	Smith Inc	smith.com	ID-3532	Yes	Yes
Jane	Doe	Walters LLC	walter.com	ID-5329	Yes	No

Query 2

Quoate Name	Company	Domain	Campaign ID
New Upgrade	Smith Manufacturing	smith.com	ID-3532
Renewal	Walters Energy	walter.com	ID-4235

[PhilipTreacy · Accepted solution]

Hi @shaebert 

Download this sample PBIX with the code below

This Power Query code should work for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXLJTwWSwbmZJRkKnnnJQHYxiK2XnJ8LZHu66BqbGhsBWZGpxUqxOkBtiXmpcG3hiTklqUXFCj4+zkBeOZiH0AnUaAnTGQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fname = _t, LName = _t, Company = _t, Domain = _t, #"Campaign ID" = _t, Lead = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Quote Created", each if [Domain] = Query2[Domain]{[Index]} and [Campaign ID] = Query2[Campaign ID]{[Index]} then "Yes" else "No"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Regards

Phil

[Anonymous · Accepted solution]

Hi @shaebert ,

Please try the following formula to add a calculated column:

Quote Created? =
IF (
    [Campaign ID]
        = LOOKUPVALUE ( 'Query 2'[Campaign ID], 'Query 2'[Domain], 'Query 1'[Domain] ),
    "Yes",
    "No"
)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy · Accepted solution]

Hi @shaebert 

Download this sample PBIX with the code below

This Power Query code should work for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXLJTwWSwbmZJRkKnnnJQHYxiK2XnJ8LZHu66BqbGhsBWZGpxUqxOkBtiXmpcG3hiTklqUXFCj4+zkBeOZiH0AnUaAnTGQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fname = _t, LName = _t, Company = _t, Domain = _t, #"Campaign ID" = _t, Lead = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Quote Created", each if [Domain] = Query2[Domain]{[Index]} and [Campaign ID] = Query2[Campaign ID]{[Index]} then "Yes" else "No"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Regards

Phil