Power Query to calculate duration

[samahiji]

Hello
I have the following table and need to calculate the duration among users for each status changed (Open>Closed):

USER	TIME	STATUS
Tony	04/05/2023 08:45​	Opened
Mary	04/05/2023 08:55​	Opened
Tony	04/05/2023 09:24​	Closed
Mary	04/05/2023 14:06​	Closed
Alan	04/05/2023 16:07​	Opened
Alan	04/05/2023 17:10​	Closed

 

[johnny51981]

See if this suits your need. Note "Table1" will need to be updated to match that of your table name.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"USER", type text}, {"TIME", type datetime}, {"STATUS", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"USER", Order.Ascending}, {"TIME", Order.Ascending}}),
    #"Filtered Rows - STATUS Opened" = Table.SelectRows(#"Sorted Rows", each ([STATUS] = "Opened")),
    #"Added Opened Index" = Table.AddIndexColumn(#"Filtered Rows - STATUS Opened", "Opened Index", 1, 1, type text),
    #"Renamed Column - Opened Time" = Table.RenameColumns(#"Added Opened Index",{{"TIME", "Opened Time"}}),
    #"Filtered Rows - STATUS Closed" = Table.SelectRows(#"Sorted Rows", each ([STATUS] = "Closed")),
    #"Added Closed Index" = Table.AddIndexColumn(#"Filtered Rows - STATUS Closed", "Closed Index", 1, 1, type text),
    #"Renamed Column - Closed Time" = Table.RenameColumns(#"Added Closed Index",{{"TIME", "Closed Time"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Column - Opened Time", {"Opened Index"}, #"Renamed Column - Closed Time", {"Closed Index"}, "Renamed Column - Closed Time", JoinKind.LeftOuter),
    #"Expanded Renamed Column - Closed Time" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Column - Closed Time", {"Closed Time"}, {"Closed Time"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Renamed Column - Closed Time",{"STATUS", "Opened Index"}),
    #"Added Column - Duration" = Table.AddColumn(#"Removed Columns", "Duration", each [Closed Time] - [Opened Time], type duration)
in
    #"Added Column - Duration"

 

[samahiji]

johnny51981​

Thanks a lot. It is working fine :D

 

[samahiji]

Sorry,
when tested in real data, the query is giving wrong result!!
the issue with adding index maybe.