Power Query动态公式列性能优化求助：如何提速慢查询？

我用Excel Power Query一年多，第一次碰到运行超20分钟的查询。查询能正常跑，但肯定能通过优化写法大幅提速。

数据结构 #

• 公司（参会者）数据库：约400行，仅含「Company Title」列。
• 活动数据库：约500行，「Export CSV - Company」列用逗号分隔参会公司，另有「Event Title」「Date」「Year」列。
• 独立查询「Company Event Count - 1 Years List」：存储所有活动年份列表。

目标 #

把数据转成可视化需要的结构：以「Company Title」为行，各年份为列，单元格值是对应公司当年的参会活动数。

现有代码 #

// 这是我能想到的唯一方法，让#"Keep only names column"里的[Company Title]和动态生成列的"currentColumnTitleYearStr"在同一作用域使用
    count_table_year_company = (myTbl, yearStr, companyStr) => 
        Table.RowCount(
            Table.SelectRows(
                myTbl,
                each Text.Contains([#"Export CSV - Company"],  companyStr)
            )
        ),
                     

    Source = #"Company 1 - Loaded CSV From Folder",                             // 获取所有公司列表
    #"Keep only names column" = Table.SelectColumns(Source,{"Company Title"}),  // 只保留[Company Title]字段
    // 动态生成各年份列，示例列：[Company Title], [2015], [2016], [2017]等
    #"Add Columns for each year" =
        List.Accumulate(
            #"Company Event Count - 1 Years List",                              // 获取所有活动年份列表
            #"Keep only names column", 
            (state, currentColumnTitleYearStr) => Table.AddColumn(
                state, 
                currentColumnTitleYearStr,  // 年份作为列标题，同时用于筛选
                let // 我本来希望在这里按年份筛选表格，这样每列只筛选一次，而不是每个单元格筛选一次
                    eventsThisYearTbl = Table.SelectRows(
                        #"Event 1 - Loaded CSV From Folder",
                        each ([Year] = Number.FromText(currentColumnTitleYearStr)) 
                    )
                in(
                    // 最后计算每个单元格的活动数量，比如'John Smith'2015年参加了多少活动
                    each count_table_year_company(eventsThisYearTbl, currentColumnTitleYearStr, [Company Title]) //CompanyTitleVar
                )
            )
        ),
    FinalStep = #"Add Columns for each year"
in
    FinalStep

性能疑虑 #

• 用List.Accumulate动态生成年份列，怀疑这个函数不是最优选择，state字段可能导致计算量过大。
• 担心存在多余的each嵌套循环，each实际是嵌套循环，移除后可能显著提升性能。

优化方案 #

核心思路 #

你的核心问题是重复遍历表格次数过多：当前代码对每个公司+每个年份都要遍历一次筛选后的活动表，400家公司×N个年份，等于做了几百上千次Table.SelectRows和Text.Contains检查，这是性能瓶颈的根源。优化方向是先拆解关联关系，一次性完成统计，再转成目标结构。

具体优化步骤&完整代码 #

let
    // 1. 处理活动表：拆分逗号分隔的参会公司为单独行，清理数据
    EventSource = #"Event 1 - Loaded CSV From Folder",
    #"Split Company Column" = Table.ExpandListColumn(
        Table.AddColumn(EventSource, "Company Title", each Text.Split([#"Export CSV - Company"], ", ")),
        "Company Title"
    ),
    #"Trim Company Names" = Table.TransformColumns(#"Split Company Column", {"Company Title", Text.Trim}),
    #"Keep Relevant Columns" = Table.SelectColumns(#"Trim Company Names", {"Year", "Company Title"}),

    // 2. 按年份+公司分组，一次性统计参会次数
    #"Group by Year and Company" = Table.Group(
        #"Keep Relevant Columns",
        {"Year", "Company Title"},
        {{"Attendance Count", Table.RowCount, Int64.Type}}
    ),

    // 3. 生成公司+年份的全量组合（避免遗漏未参会的公司/年份）
    CompanySource = #"Company 1 - Loaded CSV From Folder",
    #"All Companies" = Table.SelectColumns(CompanySource, {"Company Title"}),
    AllYears = #"Company Event Count - 1 Years List",
    #"Company Year Cross Join" = Table.AddColumn(#"All Companies", "Year", each AllYears),
    #"Expand Years" = Table.ExpandListColumn(#"Company Year Cross Join", "Year"),
    #"Convert Year to Text" = Table.TransformColumns(#"Expand Years", {"Year", Text.From}),

    // 4. 关联统计数据，转置年份为列
    #"Join with Attendance Data" = Table.LeftJoin(
        #"Convert Year to Text",
        {"Company Title", "Year"},
        #"Group by Year and Company",
        {"Company Title", "Year"},
        JoinKind.LeftOuter
    ),
    #"Replace Null with 0" = Table.ReplaceValue(#"Join with Attendance Data", null, 0, Replacer.ReplaceValue, {"Attendance Count"}),
    #"Pivot Year Columns" = Table.Pivot(
        #"Replace Null with 0",
        List.Distinct(#"Replace Null with 0"[Year]),
        "Year",
        "Attendance Count"
    ),

    FinalStep = #"Pivot Year Columns"
in
    FinalStep

优化点说明 #

1. 减少遍历次数：只对活动表做一次拆分和分组，后续操作都基于聚合后的小表，避免原代码中400×N次的重复筛选与文本匹配。
2. 替换嵌套循环：原代码中each count_table_year_company是对每个单元格执行一次筛选，优化后通过拆分+分组一次性完成所有统计。
3. 用原生Pivot替代动态加列：Power Query的Table.Pivot是原生优化的列转置函数，比手动用List.Accumulate循环加列效率高得多。

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