Power Query使用Index +/-1致时间内存占用过高，求优化方案

问题背景 #

现有Excel资源需求表：

• 前两列为Program（项目）、Skill（技能）
• 后续列为月份列，每行代表对应项目-技能在该月所需的人员数量
  目标转换为包含Program、Skill、Label、Start、Stop的表格：
• Start/Stop：资源使用的起止日期（连续月份合并为一个区间）
• Label：区分同一Program-Skill组合下的不同资源（如Program_1_1、Program_1_2）

原转换方案因内存占用过高（70KB文件占数GB内存）、耗时过长无法使用，核心问题出在Gantt查询的逐行索引引用和数据膨胀操作。

原查询性能瓶颈分析 #

1. 数据爆炸式增长：通过List.Accumulate添加13列再逆透视，单条原始数据会生成13条记录，直接放大数据量
2. 低效逐行引用：使用#"Added Index"[EOM_plus]{[Index]-1}这类行级引用，属于O(n²)复杂度操作，数据量增加时内存和时间成本急剧上升

优化后的解决方案 #

1. 简化的File_pointer查询 #

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Desktop\Forecast_Resources.xlsx"), null, true),
    Foglio1_Sheet = Source{[Item="Foglio1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Foglio1_Sheet, [PromoteAllScalars=true]),
    #"Removed Unneeded Columns" = Table.RemoveColumns(#"Promoted Headers", {"Usage"})
in
    #"Removed Unneeded Columns"

2. 简化的Programs查询 #

let
    Source = File_pointer,
    #"Unpivoted Month Columns" = Table.UnpivotOtherColumns(Source, {"Program", "Skill"}, "Month", "Headcount"),
    #"Cleaned Data" = Table.SelectRows(
        Table.TransformColumnTypes(#"Unpivoted Month Columns", {
            {"Program", type text}, 
            {"Skill", type text}, 
            {"Month", type date}, 
            {"Headcount", Int64.Type}
        }),
        each [Headcount] > 0
    ),
    #"Added Month Boundaries" = Table.AddColumns(#"Cleaned Data", {
        "MonthStart", each Date.StartOfMonth([Month]),
        "MonthEnd", each Date.EndOfMonth([Month])
    })
in
    #"Added Month Boundaries"

3. 高效的Gantt转换查询 #

let
    Source = Programs,
    #"Generate Resource Entries" = Table.ExpandListColumn(
        Table.AddColumn(Source, "ResourceIndex", each {1..[Headcount]}),
        "ResourceIndex"
    ),
    #"Grouped by Resource" = Table.Group(#"Generate Resource Entries", {"Program", "Skill", "ResourceIndex"}, {
        {"MonthList", each Table.Sort(_, {"Month", Order.Ascending})[MonthStart], type list},
        {"EndMonthList", each Table.Sort(_, {"Month", Order.Ascending})[MonthEnd], type list}
    }),
    #"Merge Continuous Months" = Table.AddColumn(#"Grouped by Resource", "DateRanges", each 
        let
            MonthStarts = [MonthList],
            MonthEnds = [EndMonthList],
            GroupIndices = List.Generate(
                () => [Current=0, Group=0],
                each [Current] < List.Count(MonthStarts),
                each [
                    Current = [Current]+1,
                    Group = if Date.AddDays(MonthEnds{[Current]},1) = MonthStarts{[Current]+1} then [Group] else [Group]+1
                ],
                each [Group]
            ),
            GroupedRanges = Table.Group(
                Table.FromRecords(List.Zip({MonthStarts, MonthEnds, GroupIndices})),
                {"Group2"},
                {"Start", each List.Min(_[Column1]), type date},
                {"Stop", each List.Max(_[Column2]), type date}
            )
        in
            GroupedRanges
    ),
    #"Expanded Date Ranges" = Table.ExpandTableColumn(#"Merge Continuous Months", "DateRanges", {"Start", "Stop"}),
    #"Added Label" = Table.AddColumn(#"Expanded Date Ranges", "Label", each Text.Combine({[Program], "_", Number.ToText([ResourceIndex])})),
    #"Final Columns" = Table.ReorderColumns(#"Added Label", {"Program", "Skill", "Label", "Start", "Stop"})
in
    #"Final Columns"

优化说明 #

1. 避免数据爆炸：仅根据实际人员数量生成对应资源条目，而非固定生成13条，数据量可控
2. 分组替代逐行引用：通过分组+List.Generate识别连续月份区间，复杂度降至O(n log n)，大幅降低内存占用和处理时间
3. 简化中间步骤：去掉冗余列和不必要的转换，减少计算开销

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