Forum Discussion
PowerQuery: Lookup if row exists in another query based on row from current query
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.
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. 🙂
Perhaps you mean something like this
with
let Source = Excel.CurrentWorkbook(){[Name="Two"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Contains( One[X], [A])) in #"Added Custom"
