Power BI中基于键左部构建两表唯一关系及筛选问题

问题背景 #

作为Power BI/Power Query/DAX新手，需解决仅通过WBS元素左部值在两个表间构建关联的问题，已用DAX实现成本计算，但需在Power Query中完成数据集缩减与关联列创建：

Actual Cost = SUMX (FILTER ('SAP Orders', LEFT('SAP Orders'[WBS_ELEMENT], LEN([WBS in Uppercase])) = [WBS in Uppercase]),'SAP Orders'[ACTUAL_COSTS])

问题1：Power Query中按WBS左部值动态筛选SAP Orders表 #

直接通过Table.SelectRows结合Text.StartsWith筛选，以Distinct_ERP_PROJECT_ID中的Used_WBS为匹配基准，适配动态增长的大表：

let
    // 获取所有需匹配的WBS前缀列表
    WBS_Prefixes = List.Distinct(Distinct_ERP_PROJECT_ID[Used_WBS]),
    // 筛选SAP Orders表中WBS_ELEMENT以任意前缀开头的记录
    Filtered_SAP_Orders = Table.SelectRows('SAP Orders', each List.AnyTrue(List.Transform(WBS_Prefixes, (prefix) => Text.StartsWith([WBS_ELEMENT], prefix, Comparer.OrdinalIgnoreCase))))
in
    Filtered_SAP_Orders

• 优势：仅保留符合前缀条件的记录，直接缩减数据集，适配表的动态更新
• 注意：Comparer.OrdinalIgnoreCase可忽略大小写差异，可根据实际需求移除该参数启用大小写严格匹配

问题2：创建用于表关联的唯一匹配列 #

不要直接使用Text.Equals（该函数仅支持完全相等匹配，不符合前缀匹配需求），改用Table.AddColumn结合List.Select定位匹配的Used_WBS值，确保关联列唯一：

let
    WBS_Prefixes = List.Distinct(Distinct_ERP_PROJECT_ID[Used_WBS]),
    // 先筛选符合条件的订单记录
    Filtered_SAP_Orders = Table.SelectRows('SAP Orders', each List.AnyTrue(List.Transform(WBS_Prefixes, (prefix) => Text.StartsWith([WBS_ELEMENT], prefix, Comparer.OrdinalIgnoreCase)))),
    // 添加唯一匹配列，取第一个匹配的Used_WBS（需确保前缀无重叠，否则需额外处理）
    Add_Match_Column = Table.AddColumn(Filtered_SAP_Orders, "Matched_Used_WBS", each 
        let
            Matched = List.Select(WBS_Prefixes, (prefix) => Text.StartsWith([WBS_ELEMENT], prefix, Comparer.OrdinalIgnoreCase))
        in
            if List.Count(Matched) > 0 then Matched{0} else null
    )
in
    Add_Match_Column

• 若存在多个前缀匹配同一WBS_ELEMENT的情况，需先对前缀列表按长度降序排序，确保匹配最长的有效前缀：

WBS_Prefixes_Sorted = List.Sort(WBS_Prefixes, (a,b) => Text.Length(b) - Text.Length(a))

完整WBS路径订单的Used_WBS值为null的排查方案 #

1. 清理数据格式：检查是否存在大小写、空格或特殊字符差异，先统一格式再匹配：

// 清理Distinct_ERP_PROJECT_ID的Used_WBS列
Cleaned_Distinct = Table.TransformColumns(Distinct_ERP_PROJECT_ID, {"Used_WBS", each Text.Upper(Text.Trim(_))}),
// 清理SAP Orders的WBS_ELEMENT列
Cleaned_SAP_Orders = Table.TransformColumns('SAP Orders', {"WBS_ELEMENT", each Text.Upper(Text.Trim(_))})

2. 移除多余字符：无需给WBS_ELEMENT添加&"x"，额外字符会破坏完全相等时的匹配逻辑，Text.StartsWith本身支持字符串完全相等的场景
3. 统一数据类型：确认Used_WBS和WBS_ELEMENT均为文本类型，若存在数值类型需先转换：

Table.TransformColumns(目标表, {"目标列名", Text.From})

内容的提问来源于stack exchange，提问作者Svein Arne Hylland