旧版Excel用Power Query拆分大列加载异常及查询无法加载问题

问题背景 #

需将含999999行数据的A列拆分为每200行一列，新版Excel可通过WRAPCOLS(A2:A999999,200)实现，但终端用户使用旧版Excel；可通过INDEX(A2:A999999,ROW(A1)+(COLUMN(A1)-1)*200)公式实现，但希望采用Power Query方案。用户编写的Power Query代码执行后一直处于加载状态，且查询无法加载到Excel。

用户原代码：

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.FromRows(List.Split(Source[ID],200))
in
Custom1

问题原因排查 #

• 内存过载：List.Split会一次性拆分100万行的完整列表，生成约5000个子列表（999999÷200≈5000），旧版Excel的Power Query内存管理能力有限，一次性处理如此大规模的列表集合易导致内存溢出，引发长时间卡顿或加载失败。
• 表格构建效率低下：Table.FromRows需一次性将所有子列表转换为表格列，旧版Power Query对大规模列的表格构建支持不足，加重了内存负担和处理耗时。
• 无轻量化预处理：直接读取整个表格内容，未做数据类型指定或分批处理，额外消耗了系统资源。

优化解决方案 #

1. 分批构建列，降低内存占用 #

改用逐列增量构建的方式，避免一次性拆分整个大列表，代码示例：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TotalRows = Table.RowCount(Source),
    ColumnCount = Number.RoundUp(TotalRows / 200),
    ColumnNames = List.Transform({1..ColumnCount}, each "Column_" & Text.From(_)),
    BuildColumns = List.Accumulate({0..ColumnCount-1}, #table({}, {}), (state, current) =>
        let
            StartIndex = current * 200,
            CurrentColumn = List.Range(Source[ID], StartIndex, 200),
            ColumnTable = Table.FromColumns({CurrentColumn}, {ColumnNames{current}}),
            MergedTable = Table.Join(state, {}, ColumnTable, {}, JoinKind.FullOuter)
        in
            MergedTable
    )
in
    BuildColumns

2. 优化数据源读取 #

• 明确数据类型：在Source步骤后添加数据类型转换，避免Power Query自动检测类型的额外开销：

  ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}})
  

• 若原表格是普通区域转换而来，可先转成范围再重新创建表格，手动指定数据类型，减少类型检测的资源消耗。

3. 适配旧版Excel加载限制 #

旧版Excel（如2016及更早）对单工作表的单元格总数有上限，若仍无法加载：

• 将拆分后的数据分批次加载到不同工作表，比如每1000列存一个工作表；
• 关闭Excel外的其他占用内存的进程，释放系统资源。

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