Retrieve distinct rows after a join

Question

I am performing a join between two events as follows. How can I get distinct rows in my final output?

let fromdate = "2017-04-26 23:00";

let fileEvents = (Events
| where Timestamp > todatetime(fromdate) 
| project fileId, fileName, Application);

fileEvents | join (Events
    | where Timestamp > todatetime(fromdate) and Data.Size > 1024
    | project fileId) on fileId
| project fileId,Application, fileName;

Query output

1 , Web , Agreement
1 , Web , Agreement
2 , Api , Contract
2 , Api , Contract
1 , Web , Agreement
2 , Api , Contract

I want the output to be

1 , Web , Agreement
2 , Api , Contract

Answer 1

Use the summarize operator to summarize by all result columns:

let fromdate = "2017-04-26 23:00";

let fileEvents = (Events
| where Timestamp > todatetime(fromdate) 
| project fileId, fileName, Application);

fileEvents
| join (Events
| where Timestamp > todatetime(fromdate) and Data.Size > 1024
| project fileId) on fileId
| summarize by fileId, Application, fileName

An equivalent of the above will be:

let fromdate = "2017-04-26 23:00";
Events
| where Timestamp > todatetime(fromdate) 
| project fileId, fileName, Application
| join (
    Events
    | where Timestamp > todatetime(fromdate) and Data.Size > 1024
    | project fileId) on fileId
| summarize by fileId, Application, fileName