Append two tables using left antijoin

[workmonitored]

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 those two tables in Power Query M ? 

All the tickets

Key	Description	Date Worked On
1	Fix my problem	01/01/2021
2	I want a laptop	02/02/2021

Tickets that got reopened

Key	Description	Date Worked On
2	I want a laptop	03/02/2021
3	This is a new	03/03/2021

All the tickets data should look like this

Key	Description	Date Worked On
1	Fix my problem	01/01/2021
2	I want a laptop	03/02/2021
3	This is a new	03/03/2021

[parry2k]

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

[workmonitored]

Thank you. Is there a recommended programming language for integrating SQL Server with a rest api endpoint ?

[v-denglli-msft]

Hi @workmonitored ,

You can integrate SQL Server with a Rest API endpoint through C#.

Please check the links below.

Calling REST API Service From SQL Server Using C# SQL CLR

sql-server-samples/samples/features/json/todo-app/dotnet-rest-api/README.md at master · microsoft/sq...

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@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.

[workmonitored]

Thanks. The data comes from a REST api resource.  It has two call signatures. One that are active and one that's not. That's why I have to refresh the active data once a day or so. Hence the schedule....

[parry2k]

@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. 

[parry2k · Accepted solution]

@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.

[workmonitored]

Thank you. Will move it to SQL. My only gripe is the refresh schedule of Power BI and SQL refresh will have to be in sync. I hate creating dependencies with two different systems.

[Ashish_Mathur]

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]

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. 

[FreemanZ]

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

[parry2k]

@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"

[workmonitored]

Thank you. Should I be worried about doing this in PowerBI and not a sql server. Not sure how performant PowerBI will be. I am guessing that's why there is an index column in there. I am talking about hundred thousand rows and updating 2K of them.

[parry2k]

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

[workmonitored]

yes