Power Query M语言如何用Table.SelectRows筛选两表外键匹配的员工行

方案1：基于Table.SelectRows实现（逻辑直观，适合小数据量） #

你需要先加载business表，提取有效id列表后再过滤employee表：

let
    Source = MySQL.Database(<DATABASE>, "business_database", [ReturnSingleDatabase=true, CreateNavigationProperties=false]),
    business_database_employee_all = Source{[Schema="business_database",Item="employee"]}[Data],
    // 加载business表
    business_database_business_all = Source{[Schema="business_database",Item="business"]}[Data],
    // 提取business表所有有效id转为列表
    valid_business_ids = business_database_business_all[id],
    // 过滤满足条件的employee行
    employee_included = Table.SelectRows(business_database_employee_all, each List.Contains(valid_business_ids, [business_id]))
in
    employee_included

方案2：基于表关联实现（更推荐，适合全量场景） #

该方案支持查询折叠，会直接在MySQL端执行关联过滤，无需拉全量数据到本地，性能远高于方案1：

let
    Source = MySQL.Database(<DATABASE>, "business_database", [ReturnSingleDatabase=true, CreateNavigationProperties=false]),
    business_database_employee_all = Source{[Schema="business_database",Item="employee"]}[Data],
    business_database_business_all = Source{[Schema="business_database",Item="business"]}[Data],
    // 内连接关联两张表，仅保留id匹配的行
    joined_table = Table.Join(
        business_database_employee_all, "business_id",
        // 若business表id是唯一主键，可省略Table.Distinct
        Table.Distinct(Table.SelectColumns(business_database_business_all, "id")), "id",
        JoinKind.Inner
    ),
    // 移除多余的id列，保留员工表原有字段
    employee_included = Table.RemoveColumns(joined_table, "id")
in
    employee_included

内容的提问来源于stack exchange，提问作者Sean