Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MichiyoTora
Frequent Visitor

Add a custom column closest previous date with other criterias

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

idproduct_iddeployment_datetime
1toto01/01/2024 00:00:00
2tata01/01/2024 00:00:00
3tata02/02/2024 00:00:00
4titi01/01/2024 00:00:00
5titi02/02/2024 00:00:00
6titi03/03/2024 00:00:00

Incidents

product_idincident_datetimeresolution_time
toto01/01/2024 02:00:0046
tata01/01/2024 02:00:0062
titi02/02/2024 02:00:00108
titi12/12/2023 02:00:002


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_idincident_datetimeresolution_timelinked_deployment_id
toto01/01/2024 02:00:00461
tata01/01/2024 02:00:00622
titi02/02/2024 02:00:001085
titi12/12/2023 02:00:002null

 

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

Thank's a lot !

2 ACCEPTED SOLUTIONS
ronrsnfld
Super User
Super User

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"

 

 

ronrsnfld_0-1722539594852.png

 

 

View solution in original post

dufoq3
Super User
Super User

Hi @MichiyoTora, different approach:

 

Result

dufoq3_0-1723022349251.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

5 REPLIES 5
MichiyoTora
Frequent Visitor

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
Community Support
Community Support

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
Super User
Super User

Hi @MichiyoTora, different approach:

 

Result

dufoq3_0-1723022349251.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

ronrsnfld
Super User
Super User

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"

 

 

ronrsnfld_0-1722539594852.png

 

 

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 😃

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors