Power Query中类COUNTIFS逻辑的性能瓶颈优化方案咨询

Your current approach using Table.SelectRows inside a calculated column is causing major performance issues because it scans the entire table once per row—that's an O(n²) time complexity, which gets painfully slow as your dataset grows (especially with thousands of monthly records).

Here's a far more efficient approach that groups data by product first, so we only process each product's retail months once instead of per row:

Step-by-Step Optimized Solution #

1. Group Data by Product Code #

First, we group your table to collect all retail months for each product into a sorted list. Sorting helps make subsequent range checks much faster:

// Group by Product Code, collect sorted retail months and keep all row data
GroupedByProduct = Table.Group(
    ChangeType,
    {"Product Code"},
    {
        {"All Rows", each _, type table [Product Code=text, Retail month=date]},
        {"Sorted Retail Months", each List.Sort([Retail month]), type list}
    }
)

2. Add Presence Check Columns to Each Product's Rows #

Instead of scanning the entire table for every row, we now use the pre-collected sorted list of months for each product. We'll use List.Between (optimized for sorted lists) to quickly check if any month falls within the target range:

// Process each product's rows to add the check columns
ProcessedProductRows = Table.TransformColumns(
    GroupedByProduct,
    {"All Rows", (productRows) =>
        Table.AddColumns(productRows, {
            // Check for last month presence
            "Hit nos últimos 1 mês?", each 
                List.Count(List.Between([Sorted Retail Months], Date.AddMonths([Retail month], -1), Date.AddMonths([Retail month], -1))) > 0 
                then 1 else 0,
            // Check for last 3 months presence (excluding current month)
            "Hit nos últimos 3 meses?", each 
                List.Count(List.Between([Sorted Retail Months], Date.AddMonths([Retail month], -3), Date.AddMonths([Retail month], -1))) > 0 
                then 1 else 0,
            // Check for last 12 months presence (excluding current month)
            "Hit nos últimos 12 meses?", each 
                List.Count(List.Between([Sorted Retail Months], Date.AddMonths([Retail month], -12), Date.AddMonths([Retail month], -1))) > 0 
                then 1 else 0
        })
    }
)

3. Expand the Processed Rows #

Finally, expand the processed rows to get back your full table with the new columns:

// Expand the table to restore all original rows with new columns
FinalTable = Table.ExpandTableColumn(
    ProcessedProductRows,
    "All Rows",
    {"Retail month", "Hit nos últimos 1 mês?", "Hit nos últimos 3 meses?", "Hit nos últimos 12 meses?"},
    {"Retail month", "Hit nos últimos 1 mês?", "Hit nos últimos 3 meses?", "Hit nos últimos 12 meses?"}
)

Why This Is Faster #

• Reduced Scans: Each product's retail month list is generated once, not once per row.
• Sorted List Efficiency: List.Between leverages sorted lists to perform range checks without iterating every element (similar to binary search), which is way faster than full list scans.
• Lower Time Complexity: This approach drops from O(n²) to O(n log n) (from sorting each product's month list), which is night-and-day for large datasets.

You can also tweak the logic slightly—for example, using List.AnyTrue instead of List.Count if you just need a boolean check, but List.Between is particularly efficient here due to the sorted list.

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