Sep 03 2021 01:23 AM - edited Sep 03 2021 01:27 AM
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"
Sep 03 2021 04:45 AM
Sep 03 2021 12:20 PM
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. :)
Sep 03 2021 02:03 PM
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"
Sep 03 2021 03:07 PM
Sep 04 2021 02:21 AM
Sorry, I only illustrated an idea, it was much faster to generate that sample than restore your tables structure from the script.