Power Query技术问询：能否用M语言让合并查询仅返回True或False？

Absolutely! You can totally replicate that Excel VLOOKUP + ISNA behavior in Power Query's M language—here are a couple of practical approaches depending on your workflow and data size:

Method 1: Merge Queries + Custom Column (Great for Visual Workflows) #

If you prefer building your query through the Power Query UI first (and then grabbing the M code), this is the way to go:

• Start with your main table (let's call it TableA) where you want to add the "match check" column.
• Go to Merge Queries > Select your reference table (TableB), pick the matching column(s) (e.g., ID in both tables), and choose the Left Outer join type (this mirrors how VLOOKUP works by keeping all rows from TableA).
• Add a custom column with a simple null check to flag matches:

  = [TableB] <> null
  

  Or equivalently:

  = not List.IsNull([TableB])
  

The full M code for this approach looks like this:

let
    Source = TableA,
    // Left-join TableA with TableB on the ID column
    MergedTables = Table.NestedJoin(Source, {"ID"}, TableB, {"ID"}, "TableB", JoinKind.LeftOuter),
    // Add column to check if a match was found
    AddedMatchFlag = Table.AddColumn(MergedTables, "IsMatch", each [TableB] <> null)
in
    AddedMatchFlag

Method 2: Direct Lookup with Table.Contains (Clean & Concise) #

If you don't need to keep the merged data and just want the true/false flag, skip the merge entirely and use Table.Contains directly. This is perfect for quick checks:
Add a custom column to TableA with this formula:

= Table.Contains(TableB, [ID = [ID]])

This checks if the current row's ID exists anywhere in TableB's ID column. For multi-column matches (e.g., matching both ID and ProductName), just expand the record:

= Table.Contains(TableB, [ID = [ID], ProductName = [ProductName]])

Full M code example:

let
    Source = TableA,
    AddedMatchFlag = Table.AddColumn(Source, "IsMatch", each Table.Contains(TableB, [ID = [ID]]))
in
    AddedMatchFlag

Method 3: List-Based Lookup (Better for Large Datasets) #

If TableB is huge, converting its lookup column to a list first will speed up the check significantly (list lookups are more efficient than table lookups):

let
    Source = TableA,
    // Extract the lookup column from TableB into a static list
    LookupValues = TableB[ID],
    // Check if each row's ID is in the list
    AddedMatchFlag = Table.AddColumn(Source, "IsMatch", each List.Contains(LookupValues, [ID]))
in
    AddedMatchFlag

All three methods will give you exactly what you're after: True when a match exists, False when it doesn't—just like NOT(ISNA(VLOOKUP(...))) in Excel!

内容的提问来源于stack exchange，提问作者Frederic Le Guen