Append two tables using left antijoin

Hi,

This M code works

let
    Source = Table.Combine({All, reopened}),
    #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Count", each Table.Max(_,"Date Worked On")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Description", "Date Worked On"}, {"Description", "Date Worked On"})
in
    #"Expanded Count"

Hope this helps.

workmonitored anhow you have both the solution, should be easy to test without too much efforts, not sure what else to tell. Thanks!

workmonitored where the records are matching you want to keep the newest one, in this case # 2?

workmonitored if that it the case, append both the table and group it get the latest date, here is M code, start a blank query, click advanced editor and paste the code below, change the name of the table in the first line:

let
    Source = Table.Combine({All, Open}),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Key", "Description"}, {{"Latest Date", each List.Max([Date Worked On]), type nullable date}})
in
    #"Grouped Rows"

hi workmonitored ,

try like:

append them, sort by date column, select key column and remove duplicate rows

or

left outer join the reopenned table to all ticket table based on key column; expand the date column from reopenned , remove the original date column

workmonitored at this point performance is more of SQL server, not Power BI. you have to make sure your queries in the SQL server are optimized, Power BI is just reading data and will depend on the SQL server. 

workmonitored well there is always a decision to make, you can test both approaches (since you have both solutions) and go from there. Not sure why there is schedule dependency on SQL, you will create a view which will prepare the data, and then Power BI will connect to that view, in this case, there is no schedule on the SQL side, or maybe I'm not fully sure about your setup.