powerquery - Slow query due to mandatory buffering - Stack Overflow

admin管理员组

文章数量:1122832

Purpose of my query is to get distinct rows, from a 600 000 rows database.

Everything is fine except that, at one time,I have to keep/extract the first rows of groups after sorting. (step 7 to 9)
And after web search, the only way to make it work is too add a buffering step (8) after sorting (9). Otherwise it doesn't keep the first instance of the groups.
When I do the buffering, it just hang on the query, i always have to escape.

What could i do?

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
),
// Step 7: Sort the table based on "Tag", "QCF", "WS contract cpy", and the custom sort order
SortedRows = Table.Sort(AddSortOrder, {{"Tag", Order.Ascending}, {"QCF", Order.Ascending}, {"WS contract cpy", Order.Ascending}, {"SortOrder", Order.Ascending}}),

// Step 8: Buffer after adding sort order
BufferedStep = Table.Buffer(SortedRows),

// Step 9: Remove duplicates based on "Tag", "QCF", and "WS contract cpy", keeping the first occurrence
DistinctRows2 = Table.Distinct(BufferedStep, {"Tag", "QCF", "WS contract cpy"}),

// Step 10: Remove the custom sort order column
FinalTable = Table.RemoveColumns(DistinctRows2, {"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

本文标签： powerquerySlow query due to mandatory bufferingStack Overflow