Forum Discussion

workmonitored's avatar
workmonitored
New Member
1 year ago
Solved

Append two tables using left antijoin

I have two tables with same structures (tickets), one contains the all data (unchanging) and the other (new data). Based on the day, the new data can contain rows from the all data. How can I merge t...
Need Help
  • parry2k's avatar
    parry2k
    1 year ago

    workmonitored as a rule of thumb and best practice, do transformation/data preparation as upstream as possible, and as close to the source.

     

    if you can do this in an SQL server then better to do it there so that it can be used for other reports/projects as well instead of doing it in Power BI.

     

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
1 year ago

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's avatar
workmonitored
New Member
1 year ago

Thank you. This is very smart. Side question, should I worry about refresh times, the all table contains rows upwards of 100K and the new table has about 1-2K rows which gets refreshed daily.