Select max_id in each date

Expand the merged table to include only the rows with the maximum Report ID:

After the merge, you will see a new column with the merged table data.
Click on the expand icon (two arrows) next to the merged column and select only the MaxReportID to expand.
You can now filter the original table based on the MaxReportID to keep only the rows where the Report ID matches the MaxReportID.

Example in Power Query M Language:

Here is an example of how the steps would look in Power Query M code:

// Load the original table
let
    Source = Sql.Database("YourServerName", "YourDatabaseName"),
    OriginalTable = Source{[Schema="dbo",Item="YourTableName"]}[Data],

    // Group by Date to get the maximum Report ID for each date
    GroupedTable = Table.Group(OriginalTable, {"Date"}, {{"MaxReportID", each List.Max([Report ID]), type number}}),

    // Merge the original table with the grouped table to keep only the rows with the max Report ID
    MergedTable = Table.NestedJoin(OriginalTable, {"Date", "Report ID"}, GroupedTable, {"Date", "MaxReportID"}, "GroupedTable", JoinKind.Inner),

    // Expand the merged table
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "GroupedTable", {"MaxReportID"}, {"MaxReportID"}),

    // Filter to keep only rows where Report ID equals MaxReportID
    FilteredTable = Table.SelectRows(ExpandedTable, each [Report ID] = [MaxReportID])
in
    FilteredTable

Explanation of the Code

Source: Connects to your SQL Server and loads the original table.
GroupedTable: Groups the data by Date and calculates the maximum Report ID for each date.
MergedTable: Merges the original table with the grouped table based on Date and Report ID.
ExpandedTable: Expands the merged column to include the MaxReportID.
FilteredTable: Filters the rows to keep only those where Report ID equals MaxReportID.

By following these steps and using the provided Power Query M code, you should be able to filter your data as required and set up incremental refresh in Power BI. mysedgwick app