topic Re: Event Difference in Power Query

Event Difference

Matic20 — Wed, 21 Aug 2019 13:10:28 GMT

Hello

I need to get the difference between every reopen and closed then return the same of the difference

1hr 2 mints + 29 mints + 2 mints = 1hr and 43 mints

AFFECTED OBJECT	LOG CREATION DATE	EVENT NAME
10283	7/18/2019 12:20	reopenticket
10283	7/18/2019 13:22	closeticket
10283	7/18/2019 13:30	reopenticket
10283	7/18/2019 13:59	closeticket
10283	7/18/2019 15:26	reopenticket
10283	7/18/2019 15:28	closeticket

Re: Event Difference

Anonymous — Wed, 21 Aug 2019 15:16:06 GMT

Hi,

Please take a look at this code, I used the approach listed here to get the previous date, and then I added a custom column subtracting the times on each closedticket.

Finally, I grouped this on the affected object column to get the results you mentioned.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AFFECTED OBJECT", Int64.Type}, {"LOG CREATION DATE", type text}, {"EVENT NAME", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"LOG CREATION DATE", Order.Ascending}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Sorted Rows", {{"LOG CREATION DATE", type datetime}}, "en-US"),
    #"Sorted Rows1" = Table.Sort(#"Changed Type with Locale",{{"LOG CREATION DATE", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"LOG CREATION DATE"}, {"LOG CREATION DATE.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index", "Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"LOG CREATION DATE.1", "PREVIOUS LOG CREATION DATE"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each [PREVIOUS LOG CREATION DATE]-[LOG CREATION DATE]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Duration", each if [EVENT NAME] = "closeticket" then [LOG CREATION DATE]-[PREVIOUS LOG CREATION DATE] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration", type duration}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"AFFECTED OBJECT"}, {{"Total Duration", each List.Sum([Duration]), type duration}})
in
    #"Grouped Rows"

the final result is this.

Please mark it as solution if that works for you.

Tks!

Vagner

Re: Event Difference

Matic20 — Wed, 21 Aug 2019 18:28:18 GMT

Anonymous I tried the proceedure provided but most of the results provided were wrong results

Re: Event Difference

Anonymous — Wed, 21 Aug 2019 19:14:45 GMT

Can you add more details? The results on the data you provided were exactly the same you said you would expect.

Tks

Re: Event Difference

v-juanli-msft — Thu, 29 Aug 2019 07:00:46 GMT

Hi Matic20

You could create columns using DAX easier than in Power query.

index =
RANKX (
    FILTER (
        'Table (2)',
        'Table (2)'[AFFECTED OBJECT] = EARLIER ( 'Table (2)'[AFFECTED OBJECT] )
    ),
    [LOG CREATION DATE],
    ,
    ASC,
    DENSE
)


reopen time =
CALCULATE (
    SUM ( 'Table (2)'[LOG CREATION DATE] ),
    FILTER (
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[AFFECTED OBJECT] ),
        'Table (2)'[index]
            = EARLIER ( 'Table (2)'[index] ) - 1
    )
)


time diff = IF([EVENT NAME]="closeticket",DATEDIFF([reopen time],[LOG CREATION DATE],SECOND))

total time per = CALCULATE(SUM('Table (2)'[time diff]),ALLEXCEPT('Table (2)','Table (2)'[AFFECTED OBJECT]))

final output =
VAR H =
    INT ( [total time per] / 3600 )
VAR M =
    INT ( ( [total time per] - INT ( [total time per] / 3600 ) * 3600 ) / 60 )
VAR S =
    MOD ( [total time per] - INT ( [total time per] / 3600 ) * 3600, 60 )
VAR hh =
    IF ( LEN ( H ) = 1, "0" & H, H )
VAR mm =
    IF ( LEN ( M ) = 1, "0" & M, M )
VAR ss =
    IF ( LEN ( S ) = 1, "0" & S, S )
RETURN
    hh & ":" & mm & ":" & ss

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.