Calculate leadtime from a log file

[h4_dkj]

Hi guys

I have a log fle that look something like this

Project	Project Status	Project Status Entry Timestamp
23-272	1.1	24.09.2023 19:30
23-272	1.2	24.09.2023 19:51
22-132	1.1	26.09.2023 14:06
22-132	1.2	29.09.2023 11:55
22-132	1.3	03.10.2023 07:39
23-272	1.3	04.10.2023 12:44
22-132	2.1	18.10.2023 13:29
22-132	2.2	01.11.2023 08:36
23-307	1.1	06.11.2023 08:14
23-307	1.2	06.11.2023 13:24
22-132	2.6	07.11.2023 18:31
23-307	1.3	15.11.2023 19:16

Now I need to calculate the leadtime for each step for each project, like shown in the example below

Where ever there is a NULL just means that the project is not finished with its current state. The NULL should be repleaced with leadtime from start of the state to todays date.

Project	Project Status	Project Status Entry Timestamp	leadtime
23-272	1.1	24.09.2023 19:30	0:20:56
23-272	1.2	24.09.2023 19:51	16:53:19
22-132	1.1	26.09.2023 14:06	21:49:18
22-132	1.2	29.09.2023 11:55	19:43:41
22-132	1.3	03.10.2023 07:39	5:50:28
23-272	1.3	04.10.2023 12:44	NULL
22-132	2.1	18.10.2023 13:29	19:07:11
22-132	2.2	01.11.2023 08:36	9:55:02
23-307	1.1	06.11.2023 08:14	5:09:40
23-307	1.2	06.11.2023 13:24	5:52:07
22-132	2.6	07.11.2023 18:31	NULL
23-307	1.3	15.11.2023 19:16	NULL

Is this possible to solve in a not to messy manner or do I need to put this thru same processing in our data modeling tool before Power BI can do its magic?

Thanks for your help

Best regards

Dan

[lbendlin]

Your sample data seems to be missing the seconds and your expected outcome seems to be missing the days.  Please clarify.  Please also indicate how to handle weekends, holidays, and business hours.

[h4_dkj]

Sorry for that.

It shoud only adopt to  business hours and all lead time should be in hours and minutes

[v-xinruzhu-msft]

Hi @h4_dkj 

What is the logic of calculating leadtime? Based on the information you have offed, I cannot understand the logic of the leadtime,should it have a start time of each status?  can you offer some information about the logic of calculating lead time?

Best Regards!

Yolo Zhu

[h4_dkj]

Hi

I have sorted the example table below make it more easy to explain 

Every project gets a statdate when entering state 1.1 and a new date when entering state 1.2

The leadtime fot state 1.1 for a project is then the difference between the dates in 1.1 and 1.2

The leadtime fot state 1.2 for a project is then the difference between the dates in 1.2 and 1.3

and so on

If the project has no next known state to calcluate leadtime then todays date should be uses

like project 23-272 and 23-307 who both has last know state 1.3 then leadtime is the difference betweem coresponding date for thate state and todaysdate

Hope this made it easier ?

Project	Project Status	Project Status Entry Timestamp
22-132	1.1	26.09.2023 14:06
22-132	1.2	29.09.2023 11:55
22-132	1.3	03.10.2023 07:39
22-132	2.2	01.11.2023 08:36
22-132	2.1	18.10.2023 13:29
22-132	2.6	07.11.2023 18:31
23-272	1.1	24.09.2023 19:30
23-272	1.2	24.09.2023 19:51
23-272	1.3	04.10.2023 12:44
23-307	1.1	06.11.2023 08:14
23-307	1.2	06.11.2023 13:24
23-307	1.3	15.11.2023 19:16

[v-xinruzhu-msft]

Hi @h4_dkj 

You can create a blank query and put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZFLDoMwDAWvUrFukZ8/gfgMqLuuKu5/jdrQEkg3Xjij57Hzfg8sD554uA8YEZV1pDoysdxQXSha5ExuZVjvF5r/aMsAFDdx1B3nB+QUXhquTiVbcK2Ouce39NpwuFk+VFdxRY9LesoI2nGaXGq0zC3s5159o/Wgwa4aredrWa7BvGljbqg4190jZgA9npViWXw9ZpfcMm5jTvzzEJqOm1A509DNmmJN6mm+0qmy08Yh05vkWJoaHSa47NiC8xywhsZ/lANdPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Status" = _t, #"Project Status Entry Timestamp" = _t, leadtime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Status", type number}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Project Status Entry Timestamp", type datetime}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Project Status Entry Timestamp", Order.Ascending}, {"Project", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Project"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Project Status", "Project Status Entry Timestamp", "leadtime", "Index"}, {"Project Status", "Project Status Entry Timestamp", "leadtime", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Count",{"Project", "Project Status", "Project Status Entry Timestamp", "Index", "leadtime"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each List.Min(Table.SelectRows(#"Reordered Columns",(x)=>x[Project]=[Project] and x[Index]=[Index]+1)[Project Status Entry Timestamp])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]<>null then Duration.Hours([Custom]-[Project Status Entry Timestamp]) else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom]<>null then Duration.Minutes([Custom]-[Project Status Entry Timestamp]) else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [Custom]<>null then Duration.Seconds([Custom]-[Project Status Entry Timestamp]) else null),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom3", {{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}, "en-US"),{"Custom.1", "Custom.2", "Custom.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns",each [Merged],each if [Custom]<>null then [Merged] else null,Replacer.ReplaceValue,{"Merged"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Index", "leadtime", "Custom"})
in
    #"Removed Columns"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[h4_dkj]

Thank you, I will test this 🙂

[lbendlin]

Please clarify what your interpretation of work days and business hours is.

[h4_dkj]

Hi

Workdays are monday to friday between 0745-1515