PowerQuery: Lookup if row exists in another query based on row from current query

New Contributor

So I want to add a logical column that says whether a row exists in another query based on values from the current row:

In the example below, MC in line 2 is a query with a field named DefKey and Domo in line 3 is another query that has a field named definitionKey.

If I merge the query MC with Domo, I do get matching rows, but here it seems the function is not matching rows. I know from the data in both queries that there are matching rows (checked for extra spaces in the value and case as well).

Originally, I did not have "Source" in front of "[DefKey]" in line 3, but I got an error that says PowerQuery could not find "DefKey", so I put "Source" in front of it and I did not get the error. However, I am still not matching any rows.

What gives?

 

 

 

 

 

let
    Source = MC,
    #"Add InDomo" = Table.AddColumn(Source, "InDomo", each Table.MatchesAnyRows(Domo, each [definitionKey] = Source [DefKey]), Logical.Type ),
    #"Filtered Rows" = Table.SelectRows( #"Add InDomo", each ([EmailAddress] = "aberger@drmblaw.com"))
in
    #"Filtered Rows"

 

 

 

 

 

5 Replies
Just perform a Merge, choosing as many columns as you like to make a match. Expand the result and all the nulls are where it did not match.

Hi @Jeremy Norbury 

Thanks for the response.

I did try the Merge/Left Join, that looks promising, as I can aggregate the resulting table column to counts. I did have a bug where it kept re-reading the source files for each row of the "left" query, so I was trying this one which would have been perfect and simpler f it worked. 

If this can't work, it just means I'll have to go back to the Merge option and see what was up with that as well.

 

Just really curious why this one is not finding a match. It "seems" syntactically and logically correct.

@Barokzi 

Perhaps you mean something like this

image.png

with

let
    Source = Excel.CurrentWorkbook(){[Name="Two"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Contains( One[X], [A]))
in
    #"Added Custom"

 

That. Except in my case One is another query. Did not think of it as a list so I will try and report back.
Thank you Sergei!

@Barokzi 

Sorry, I only illustrated an idea, it was much faster to generate that sample than restore your tables structure from the script.