Power BI中禁用VBA，用M语言/Power Query计算列匹配最近值

嘿，针对你需要在Power BI里实现「为整列数据匹配固定值集合中最近值」的需求，我整理了一套完全不需要VBA、可复用且支持后续扩展固定值的方案，用Power Query的M语言就能搞定：

第一步：创建可维护的固定值集合查询 #

首先我们把可变的尺寸集合做成一个独立的查询，这样后续添加新尺寸时不用动主数据的代码：

• 在Power BI的「数据」面板里，点击「新建查询」→「空白查询」，把这个查询命名为SizeReference（名字可以自定义，记得后续代码里对应上就行）
• 点击「高级编辑器」，把默认代码替换成以下M脚本：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlWJ1oJSM4oyQfCM8vPSU1OLVbSUXJCUlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sys_size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sys_size", Int64.Type}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems({[Sys_size]}, {null})))
in
    #"Removed Blank Rows"

• 这段代码会生成你提供的示例尺寸集合（7、9、12、15、17），并且自动过滤掉null值。后续要加新尺寸的话，直接在这个查询的表格里新增行输入数值就行，非常省心。

第二步：在主数据查询中添加最近匹配计算列 #

假设你的主数据查询叫MainData，其中有一列是需要匹配的数值列（比如叫TargetSize，记得替换成你实际的列名）：

1. 打开MainData的查询编辑器，点击「添加列」→「自定义列」
2. 在弹出的公式框里输入以下M代码：

let
    CurrentValue = [TargetSize], // 替换成你主表中需要匹配的列名
    SizeList = SizeReference[Sys_size],
    // 处理null值：这里返回null，你可以改成返回集合的默认值（比如List.Min(SizeList)）
    MatchValue = if CurrentValue = null then null else
        List.Min(SizeList, (x) => Number.Abs(x - CurrentValue))
in
    MatchValue

• 逻辑说明：
  • 先获取当前行的待匹配值，再调用我们的固定尺寸集合
  • 如果当前值是null，返回null（你可以根据需求改成其他默认值，比如集合里的最小值）
  • 非null值的话，计算每个固定尺寸与当前值的绝对差，取差值最小的那个尺寸作为匹配结果

第三步：处理平局情况（可选） #

如果遇到当前值刚好在两个固定尺寸中间（比如当前值是10.5，固定集合有9和12，差值都是1.5），上面的代码会返回较小的那个值。如果需要返回较大的那个，或者自定义规则，可以用下面的进阶版代码：

let
    CurrentValue = [TargetSize],
    SizeList = SizeReference[Sys_size],
    MatchValue = if CurrentValue = null then null else
        let
            // 生成包含尺寸和对应差值的记录列表
            DiffRecords = List.Transform(SizeList, (x) => [Size = x, Diff = Number.Abs(x - CurrentValue)]),
            // 找到最小的差值
            MinDiff = List.Min(List.Transform(DiffRecords, (r) => r[Diff])),
            // 筛选出所有差值等于最小差值的候选尺寸
            Candidates = List.Select(DiffRecords, (r) => r[Diff] = MinDiff),
            // 取候选中的最大值（要最小值就换成List.Min）
            FinalMatch = List.Max(List.Transform(Candidates, (r) => r[Size]))
        in
            FinalMatch
in
    MatchValue

第四步：测试与扩展 #

• 测试：随便在主表的待匹配列输入几个值试试，比如输入10会匹配到9，输入13会匹配到12，输入16会匹配到15（用基础版）或者17（用进阶版）
• 扩展尺寸集合：直接打开SizeReference查询，新增行输入新的尺寸（比如20），主数据的计算列会自动同步更新，完全不用修改主查询的代码

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