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
