强制缓冲引发Power Query慢查询问题求助

问题背景 #

处理60万行数据库数据时，需按Tag、QCF、WS contract cpy分组，在自定义排序后保留每组的首行。原方案通过Table.Sort+Table.Buffer+Table.Distinct实现，但缓冲全表导致查询卡顿无法执行。

可行优化方案 #

方案1：用Table.Group替代缓冲+去重逻辑（推荐） #

直接按分组键对数据分组，在每组内完成排序并提取首行，无需全表缓冲，大幅降低内存占用：

替换原步骤7-10的代码为：

// Step 7-10 替换为分组取首行
GroupedRows = Table.Group(AddSortOrder, {"Tag", "QCF", "WS contract cpy"}, {
    {"FirstRow", each Table.First(Table.Sort(_, {{"SortOrder", Order.Ascending}}))}
}),
// 展开分组后的首行数据
ExpandedFirstRow = Table.ExpandRecordColumn(GroupedRows, "FirstRow", 
    List.Remove(Table.ColumnNames(AddSortOrder), {"Tag", "QCF", "WS contract cpy"})),
// 移除SortOrder列
FinalTable = Table.RemoveColumns(ExpandedFirstRow, {"SortOrder"})

方案2：缩小缓冲数据范围（兼容原逻辑） #

如果坚持使用原逻辑，仅缓冲分组和排序必需的列，后续再合并其他列：

// Step 7: 排序
SortedRows = Table.Sort(AddSortOrder, {{"Tag", Order.Ascending}, {"QCF", Order.Ascending}, {"WS contract cpy", Order.Ascending}, {"SortOrder", Order.Ascending}}),
// Step 8: 仅缓冲分组键和排序列
BufferedKeyColumns = Table.Buffer(Table.SelectColumns(SortedRows, {"Tag", "QCF", "WS contract cpy", "SortOrder"})),
// Step 9: 标记每组首行
FlagFirstRows = Table.AddColumn(BufferedKeyColumns, "IsFirst", each [SortOrder] = List.Min(Table.SelectRows(BufferedKeyColumns, (r) => r[Tag]=[Tag] and r[QCF]=[QCF] and r[WS contract cpy]=[WS contract cpy])[SortOrder])),
// Step 10: 合并原表并筛选首行
MergedTable = Table.NestedJoin(SortedRows, {"Tag", "QCF", "WS contract cpy", "SortOrder"}, FlagFirstRows, {"Tag", "QCF", "WS contract cpy", "SortOrder"}, "FlagTable", JoinKind.Inner),
FilteredFirstRows = Table.SelectRows(Table.ExpandTableColumn(MergedTable, "FlagTable", {"IsFirst"}), each [IsFirst] = true),
FinalTable = Table.RemoveColumns(FilteredFirstRows, {"SortOrder", "IsFirst"})

方案3：调整Power Query性能设置 #

• 关闭后台数据预览：文件>选项和设置>选项>数据加载>取消勾选"后台数据预览"
• 增加Power Query内存分配：在选项中调整内存限制（如果系统内存充足）

修改后的完整M代码 #

let
// Source data loading
Source = PowerPlatform.Dataflows(null),
Workspaces = Source{[Id="Workspaces"]}[Data],
xxx,
xxx,
vw_pbi_01_fact_inspection_state_ = #"xxx"{[entity="vw_pbi_01_fact_inspection_state",version=""]}[Data],

// Step 1: Remove unnecessary columns
RemovedColumns = Table.RemoveColumns(vw_pbi_01_fact_inspection_state_, {
    "Modification date", "LastModifiedDate", "Inspection id", "Inspection state Order", "RFI number", 
    "RFI revision number", "Location", "TypeCode", "Functional WBS Description", "Parent FWBS Code", 
    "Parent FWBS Description", "WS reference", "WS Position", "Weight", "WS contract Nr", "WS Work Location", 
    "Nb QCF attached", "Support doc Is required", "Support doc Is provided", "Nb Sup doc attached", 
    "Sup doc last upload date", "Is last inspection", "Is partial inspection", "Subcontractor inspector username", 
    "Subcontractor inspector", "Subcontractor signature date", "Subcontractor signature signatory", 
    "Subcontractor signature outcome", "Contractor inspector username", "Contractor inspector", 
    "Contractor signature date", "Contractor signature signatory", "Contractor signature outcome", 
    "Client inspector username", "Client inspector", "Client signature date", "Client signature signatory", 
    "Client signature outcome", "Nb comments", "Nb watchers", "Old TagName", "RevokedInspection"
}),

// Step 2: Rename columns
RenamedColumns = Table.RenameColumns(RemovedColumns, {
    {"Functional WBS", "Sub-System"}, 
    {"Inspection state", "QCF Status"}
}),

// Step 3: Reorder columns
ReorderedColumns = Table.ReorderColumns(RenamedColumns, {
    "Sub-System", "Phase", "Discipline", "Tag", "Tag Description", "QCF", "QCF Description", 
    "QCF Status", "WS contract cpy", "QCF last upload date", "Physical Progress", "QCF Is provided", 
    "Inspection date", "WS attendance", "Submission date", "Sub work class Description", "Geographical WBS", 
    "WS description", "QCF Is required", "Is NA", "WS status", "Sub work class", "Tag Discipline"
}),

// Step 4: Filter rows based on criteria
FilteredRows1 = Table.SelectRows(ReorderedColumns, each ([Discipline] <> "8200")),
FilteredRows2 = Table.SelectRows(FilteredRows1, each ([Is NA] = false)),
FilteredRows3 = Table.SelectRows(FilteredRows2, each ([QCF] <> " ")),
FilteredRows4 = Table.SelectRows(FilteredRows3, each ([QCF Description] <> "")),

// Step 5: Filter distinct rows
DistinctRows = Table.Distinct(FilteredRows4, {"Tag", "QCF", "WS contract cpy", "QCF Status"}),

// Step 6: Add a custom sort order for "QCF Status"
AddSortOrder = Table.AddColumn(DistinctRows, "SortOrder", each 
    if [QCF Status] = "Inspection Step" then 1
    else if [QCF Status] = "Closed Inspection" then 2
    else if [QCF Status] = "Open RFI" then 3
    else if [QCF Status] = "Documentation Completed" then 4
    else 5
),

// ------------------- 优化后的步骤7-10 -------------------
// Step 7-10: 分组取每组首行（替代原排序+缓冲+去重）
GroupedRows = Table.Group(AddSortOrder, {"Tag", "QCF", "WS contract cpy"}, {
    {"FirstRow", each Table.First(Table.Sort(_, {{"SortOrder", Order.Ascending}}))}
}),
ExpandedFirstRow = Table.ExpandRecordColumn(GroupedRows, "FirstRow", 
    List.Remove(Table.ColumnNames(AddSortOrder), {"Tag", "QCF", "WS contract cpy"})),
FinalTable = Table.RemoveColumns(ExpandedFirstRow, {"SortOrder"}),
// -------------------------------------------------------

// Step 11: Replace values using a list
ValueReplaceList = {
    {"1400", "Site Prep"}, {"1500", "Instrum & Telecom"}, {"1600", "Elec"},
    {"1700", "Civil"}, {"1800", "Structure"}, {"2000", "Bldg"},
    {"2200", "Insulation"}, {"2300", "Painting"}, {"6800", "Mech"},
    {"8200", "Temporary"}, {"1300", "Piping"}, {"9800", "PCOM"},
    {"9900", "COM"}, {"CON", "CONST"}, {"Documentation Completed", "Signed"},
    {"Inspection Step", "Pending"}, {"Open RFI", "Pending"},
    {"Closed Inspection", "Pending"}, {"Pending RFI", "Pending"}
},

// Function to replace values
ReplaceValues = List.Accumulate(ValueReplaceList, FinalTable, (state, current) =>
    Table.ReplaceValue(state, current{0}, current{1}, Replacer.ReplaceText, {"Tag Discipline", "Discipline", "Phase", "QCF Status"})
),

// Step 12: Correct time zone offset
JetLagCorrection = Table.TransformColumns(ReplaceValues, {"QCF last upload date", each _ - #duration(0, 9, 0, 0)}),

// Step 13: Change column types
ChangedType = Table.TransformColumnTypes(JetLagCorrection, {
    {"QCF last upload date", type date}, {"Inspection date", type date}, {"Submission date", type date}
})

in ChangedType

方案说明 #

• Table.Group方案：将数据按分组键拆分后，仅对每组内部排序并取首行，避免了全表缓冲，内存占用仅为原方案的几分之一，适合大数据量场景。
• 若必须保留原逻辑，缩小缓冲范围可减少内存压力，但性能提升不如分组方案显著。

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