Power Query自定义函数需求：限制累计行/列总值填充

需求说明 #

已实现Power Query自定义函数可创建N列并填充每行值，需新增两个停止填充条件：

• 当当前行的累计填充总值超过设定阈值时，该行剩余列停止填充
• 当当前列的累计填充总值达到固定阈值时，该列剩余行停止填充

修改后的完整函数代码 #

let
    AddColumnsWithThresholds = (existingTable as table, maxSlots as number, CRcolname as text, rowThreshold as number, columnThreshold as number) =>
        let
            // 获取原始表的行记录
            originalRows = Table.ToRecords(existingTable),
            // 初始化列累计值的列表，所有列初始累计为0
            columnAccumulators = List.Repeat({0}, maxSlots),
            
            // 逐行处理，同时跟踪列累计值
            processedRows = List.Accumulate(originalRows, {[], columnAccumulators}, (state, currentRow) =>
                let
                    currentRows = state{0},
                    currentColumnAccums = state{1},
                    crValue = Record.Field(currentRow, CRcolname),
                    
                    // 计算当前行的填充值，同时检查行累计和列累计阈值
                    fillRowColumns = List.Accumulate({0..maxSlots-1}, {0, []}, (rowState, colIndex) =>
                        let
                            rowTotal = rowState{0},
                            colValues = rowState{1},
                            colName = Text.From(colIndex + 1),
                            currentColAccum = currentColumnAccums{colIndex},
                            
                            // 判断是否满足停止填充条件
                            shouldFill = (rowTotal < rowThreshold) and (currentColAccum < columnThreshold),
                            newValue = if shouldFill then crValue else 0,
                            newRowTotal = rowTotal + newValue,
                            newColValues = colValues & {newValue}
                        in
                            {newRowTotal, newColValues}
                    ){1},
                    
                    // 更新列累计值
                    newColumnAccums = List.Zip({currentColumnAccums, fillRowColumns}) |> List.Transform(List.Sum),
                    
                    // 合并原始行和新列值
                    updatedRow = Record.Combine({currentRow, Record.FromList(fillRowColumns, List.Transform({1..maxSlots}, Text.From))}),
                    newRows = currentRows & {updatedRow}
                in
                    {newRows, newColumnAccums}
            ){0},
            
            // 将处理后的记录转换为表，并设置列类型
            finalTable = Table.FromRecords(processedRows)
                        |> Table.TransformColumnTypes(List.Transform({1..maxSlots}, each {Text.From(_), type number}))
        in
            finalTable
in
    AddColumnsWithThresholds

关键逻辑说明 #

1. 参数扩展：新增rowThreshold（行累计阈值）和columnThreshold（列累计阈值）两个参数，用于定义停止填充的触发条件
2. 双累计跟踪：
   • 外层List.Accumulate遍历原始行，同时维护每列的累计总值
   • 内层List.Accumulate遍历新列，计算当前行的累计总值，同步检查列累计值是否达标
3. 阈值判断：仅当行累计未超限且当前列累计未达阈值时，才填充对应CR值，否则填充0（可根据需求改为null）
4. 类型规范：最后统一将新列转换为数值类型，确保数据格式一致性

使用示例 #

假设输入表名为Source，调用函数时：

AddColumnsWithThresholds(Source, 5, "CR", 10, 20)

表示创建5个新列，基于CR列的值填充，当行累计超过10或列累计达到20时停止填充。

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