Search code examples
mergepowerquerym

Power Query - Merge queries returns true or false

I would like to know if it's possible with the M language to return only True or False if the merge between 2 queries match data or not ?

More or less, something similar to the ISNA function in Excel for the function VLOOKUP

Thanks

Solution

  • Say I want to check what values in this table ("firstTable"):

    First table

    exist in this table below ("secondTable"):

    Second table

    Approach 1

    If you only want to check for presence (and not actually perform a merge), then this might be one approach:

    let
    firstTable = Table.FromColumns({List.Numbers(1,10,1)}, type table [column A=Int64.Type]),
    secondTable = Table.FromColumns({List.Numbers(1,10,2)}, type table [column B=Int64.Type]),
    check = Table.AddColumn(firstTable, "Is column A in column B?", each Table.Contains(secondTable, [column B = [column A]]), type logical)
in
    check

    Approach 2

    A similar but more verbose approach might be:

    let
    firstTable = Table.FromColumns({List.Numbers(1,10,1)}, type table [column A=Int64.Type]),
    secondTable = Table.FromColumns({List.Numbers(1,10,2)}, type table [column B=Int64.Type]),
    check = Table.AddColumn(firstTable, "Is column A in column B?", each let colA = [column A], lookup = Table.MatchesAnyRows(secondTable, each [column B] = colA) in lookup, type logical)
in
    check

    Approach 3

    This approach will actually perform the merge, then check if each merged/joined table contains any rows. I would imagine it to be slower than the others (in theory as it will attempt to merge/match all rows and won't exit as soon as one match is found), but I might be wrong and it depends on Table.Join's internal implementation.

    let
    firstTable = Table.FromColumns({List.Numbers(1,10,1)}, type table [column A=Int64.Type]),
    secondTable = Table.FromColumns({List.Numbers(1,10,2)}, type table [column B=Int64.Type]),
    merged = Table.NestedJoin(firstTable, {"column A"}, secondTable, {"column B"}, "Is column A in the second table?", JoinKind.LeftOuter),
    isEmpty = Table.TransformColumns(merged, {{"Is column A in the second table?", Table.IsEmpty, type logical}})
in
    isEmpty

    I've given three approaches as you've not shown your data/example. It's difficult to suggest what will work well. But hopefully you can try them and one of them will be useful/performant for the size of your data.

    Note that the first two lines (of each M snippet) are just to create some dummy tables firstTable and secondTable.