Add a custom column closest previous date with other criterias

[MichiyoTora]

Hi everyone,
I have a very anoying subject with a difficult request.
I have 2 tables :
- the list of the deployments by product with deployment_id, product_id, deployment_date
- the list of incidents by product with product_id, incident_date, time_of_resolution
The final objective is to calculate the average time of resolution of tickets by product, but also generated by some selected deployments.

Deployments

id	product_id	deployment_datetime
1	toto	01/01/2024 00:00:00
2	tata	01/01/2024 00:00:00
3	tata	02/02/2024 00:00:00
4	titi	01/01/2024 00:00:00
5	titi	02/02/2024 00:00:00
6	titi	03/03/2024 00:00:00

Incidents

product_id	incident_datetime	resolution_time
toto	01/01/2024 02:00:00	46
tata	01/01/2024 02:00:00	62
titi	02/02/2024 02:00:00	108
titi	12/12/2023 02:00:00	2

The difficulty is how link this two tables ?
I would like to add in the incidents table a column "linked_deployment_id" with this definition :
An incident have to be linked to the last incident (closest previous deployment date), for the same product.

final_incidents :

product_id	incident_datetime	resolution_time	linked_deployment_id
toto	01/01/2024 02:00:00	46	1
tata	01/01/2024 02:00:00	62	2
titi	02/02/2024 02:00:00	108	5
titi	12/12/2023 02:00:00	2	null

Have you a solution to do it in power query or DAX ?

Thank's a lot !

[ronrsnfld · Accepted solution]

You can merge the tables on the product ID column.

Then extract the deployment_id according to your rules.

In the code below Incidents and Deployments refer to your two source tables.

let
    Source = Table.NestedJoin(Incidents, {"product_id"}, Deployments, {"product_id"}, "Deployments", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "linked_deployment_id", each let 
          //Incidents only after Deployment Date
            a = Table.SelectRows([Deployments], (r)=>[incident_datetime]>=r[deployment_datetime]),

          //return list of differences to determine position of the minimum difference
            b = List.Accumulate(
                a[deployment_datetime],
                {},
                (s,c)=> s & {Duration.TotalSeconds([incident_datetime] - c)}),

          //return the appropriate ID
            id = try a[id]{List.PositionOf(b,List.Min(b))} otherwise null
        in 
            id, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Deployments"})
in
    #"Removed Columns"

[MichiyoTora]

I'm sorry, I was vacationning without easy access to the web. I tried the two previous solutions and it works with a small dataset but I have millions deployments and incidents, and it is too big to work with that, it charges infinitely. I think that working in power query isn't the best solution in my case because it's too greddy. I'll try to insert the "foreign key" in incidents table in my data source directly (materialized views).
If someone have a best idea to optimize my problem, I remain open to other suggestions.
Thank's for your time and your help !

[v-stephen-msft]

Hi @MichiyoTora ,

I noticed that two super users have provided possible solutions to your issue. Could you please let us know if any of these solutions have resolved your problem? If so, kindly accept the helpful response as the solution.

Thank you!

Best Regards,

Stephen Tao

[dufoq3 · Accepted solution]

Hi @MichiyoTora, different approach:

Result

let
    Deployments = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSrJL8kHUgaG+kBkZGBkomBgYAVGSrE60UpGICWJJYl4lBgjKTHSByJMJSYgJZklmXhMMUVSgsMUMyQlxvpAhKYkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, product_id = _t, deployment_datetime = _t]),
    Incidents = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKskvyVfSUTIw1AciIwMjEwUDIysDAyACipqYKcXqANUkliTiVGNmBFGTWZIJUmOkD0ToagwNLJAVGRrpG4IVGSMrApoTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [product_id = _t, incident_datetime = _t, resolution_time = _t]),
    ChangedTypeDeployments = Table.TransformColumnTypes(Deployments,{{"id", Int64.Type}, {"deployment_datetime", type datetime}}),
    ChangedTypeIncidents = Table.TransformColumnTypes(Incidents,{{"incident_datetime", type datetime}, {"resolution_time", type number}}),
    MergedQueries = Table.NestedJoin(ChangedTypeIncidents, {"product_id"}, ChangedTypeDeployments, {"product_id"}, "Deployments", JoinKind.LeftOuter),
    Ad_LinkedDeploymentId = Table.AddColumn(MergedQueries, "linked_deployment_id", each Table.Last(Table.SelectRows([Deployments], (x)=> x[deployment_datetime] <= [incident_datetime]))[id]?, Int64.Type),
    RemovedColumns = Table.RemoveColumns(Ad_LinkedDeploymentId,{"Deployments"})
in
    RemovedColumns

[ronrsnfld · Accepted solution]

You can merge the tables on the product ID column.

Then extract the deployment_id according to your rules.

In the code below Incidents and Deployments refer to your two source tables.

let
    Source = Table.NestedJoin(Incidents, {"product_id"}, Deployments, {"product_id"}, "Deployments", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "linked_deployment_id", each let 
          //Incidents only after Deployment Date
            a = Table.SelectRows([Deployments], (r)=>[incident_datetime]>=r[deployment_datetime]),

          //return list of differences to determine position of the minimum difference
            b = List.Accumulate(
                a[deployment_datetime],
                {},
                (s,c)=> s & {Duration.TotalSeconds([incident_datetime] - c)}),

          //return the appropriate ID
            id = try a[id]{List.PositionOf(b,List.Min(b))} otherwise null
        in 
            id, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Deployments"})
in
    #"Removed Columns"

[MichiyoTora]

I'll try this tomorrow, thank's a lot for the idea, I hope I'll success to apply to my use case because I simplified the problem a lot to post it here. I'll come back tu you if it works ... or not 😃