Problem with Table.Distinct

Hi cseu ,

This is a classic and subtle Power Query problem, and the cause is almost certainly Query Folding. Your logic is sound, but the Power Query engine's optimization is getting in the way. It tries to translate all your steps into a single, efficient native query (SQL, in this case) to send to your data source.

The issue arises because the combination of Table.Sort followed by Table.Distinct doesn't translate well into SQL. When you click "Close & Apply," Power Query generates a SQL query that likely uses SELECT DISTINCT [Email], .... However, a SQL DISTINCT operation doesn't guarantee which row's data will be kept for the other columns when duplicate emails exist. It simply picks one, often ignoring the ORDER BY clause you specified earlier in your steps. This is why it works correctly in the Power Query editor preview, which often operates on a smaller, in-memory subset of data, but fails during the full refresh where query folding takes over.

To solve this, you must introduce a step that Power Query cannot fold, forcing it to perform the operation in its own engine after the data is sorted and loaded. The most robust method is to use Table.Group. This pattern involves grouping by the unique key, which creates nested tables for each email, and then simply taking the first row from each nested table. Since you already sorted by date descending, that first row will always be the most recent record.

Here is the more reliable M-code to achieve your goal:

let
    Source = sqlquery,
    // Clean the email text first
    #"Cleaned Email" = Table.TransformColumns(Source, {{"Email", each Text.Clean(Text.Trim(_)), type text}}),

    // Sort the entire table so the newest record for each email is at the top
    #"Sorted Rows" = Table.Sort(#"Cleaned Email", {{"Date", Order.Descending}}),

    // Group by Email and keep all rows for each email in a nested table
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Email"}, {{"AllData", each _, type table}}),

    // Add a new column that extracts only the first row (the most recent) from each nested table
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "MostRecentRecord", each Table.FirstN([AllData], 1)),

    // Remove the column with the nested tables, we don't need it anymore
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"MostRecentRecord"}),

    // Expand the column containing the single-row tables to get your final columns
    #"Expanded MostRecentRecord" = Table.ExpandTableColumn(#"Removed Other Columns", "MostRecentRecord",
        // Replace with your actual column names from the original table
        {"Email", "Date", "Column3", "Column4"},
        // Rename them if you want, otherwise use the same names
        {"Email", "Date", "Column3", "Column4"}
    ),
    
    // Finally, remove the date column if you don't need it in the dimension
    #"Removed Date Column" = Table.RemoveColumns(#"Expanded MostRecentRecord",{"Date"})

in
    #"Removed Date Column"

This approach works because the Table.Group operation, when configured to return all rows (each _) in a nested table, is too complex to be folded into a standard SQL statement. This forces Power Query to execute the sort and then perform the grouping in its own engine. From there, Table.FirstN safely selects the correct record from each group, and expanding the result gives you the clean, unique dimension table you need.

Best regards,