Calculated table to calculate the time taken for application stages

[Anonymous]

Hi, I have the below table data. Application ID is there. Each application will proceed with different stages 1, 2, 3, etc., The date and status columns will show us when the stage got started and when got closed.

Sometimes the stages may get failed and again got reversed. In the below example Application No 1 after travelling to Stage 3 it again goes to Stage 2 and after approvals it will again go to 3, 4, 5 etc.,

I need to generate the below table result where the time taken column shows the difference between the start and complete. Here we also need to handle the condition of duplicate stages. Application No and Date field will always be unique in the above shown table. So we need consider the nearest date time for calculating correctly.

Below results gives the time taken for each stages. Since stage 2 comes two times in above table, this table too have stage 2 two times with correct time taken.

[AlB · Accepted solution]

Hi @Anonymous 

Place the following M code in a blank query to see the steps for a possible solution in power query

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDQ10DS10jAyNDBQMDKwMDoFBwSWJRiVKsDlZFhlBFzvm5BTmpJalwdUZo6gyxGYauyAiHYcYY6owxDUNXZEKky0yIcZkpumGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"App No" = _t, Stage = _t, Date = _t, Status = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type datetime}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"App No", type text}, {"Stage", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"App No", "Stage"}, {{"Time Taken", each  Duration.TotalHours(List.Max([Date]) - List.Min([Date]))}},GroupKind.Local),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Time Taken", type number}})
in
    #"Changed Type1"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.