Creating Buckets to add up the time between changes

[mikem3]

My dataset has 3 columns that can look like this. 

STATUS	CHANGEDATE	WONUM
ASSIGN	5/1/2023	WO-23521
IN PROGRESS	5/2/2023	WO-23521
SUBMITTED	5/10/2023	WO-23521
IN REVIEW	5/11/2023	WO-23521
REJECTED	5/13/2023	WO-23521
IN PROGRESS	5/14/2023	WO-23521
SUBMITTED	5/17/2023	WO-23521
IN REVIEW	5/18/2023	WO-23521
APPROVED	5/19/2023	WO-23521

I need a way to sum up the time the project has each status.  So in my example above, my project was in the assign bucket for 1 day because it had that status for one day before changing.  It was in the "IN PROGRESS" bucket for 11 total days (8 the first time, 3 the second time), it was in the "SUBMITTED" bucket for 2 days (1 +1), etc.

I have tried several solutions, ranging from creating measures, custom columns, tables, etc.  To me the solution would likely be a custom column that takes the current row's changedate and subtracts the value from the previous row.  Since all my projects end with Approved, there would be no value needed for that status.  Any ideas out there?

[Vijay_A_Verma]

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwO9nT3U9JRMtU31DcyMDIGMsP9dY2MTY0MlWJ1opU8/RQCgvzdg1yDg8GqjLCqCg518vUMCXF1gZhkgMuoINcwT9dwiCLs9gW5erk6ww0yJspNhibEOMocpkgBn6sssKtyDADaGAYzyhKLolgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STATUS = _t, CHANGEDATE = _t, WONUM = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STATUS", type text}, {"CHANGEDATE", type date}, {"WONUM", type text}}),
    BuffList = List.Buffer(#"Changed Type"[CHANGEDATE]),
    DiffList = List.Generate(()=>[x=Duration.Days(BuffList{1}-BuffList{0}),y=0], each [y]<List.Count(BuffList), each [y=[y]+1, x=try Duration.Days(BuffList{y+1}-BuffList{y}) otherwise 0], each [x]),
    result = Table.FromColumns(Table.ToColumns(#"Changed Type") & {DiffList}, Table.ColumnNames(#"Changed Type") & {"Diff"}),
    #"Grouped Rows" = Table.Group(result, {"STATUS"}, {{"Total Duration", each List.Sum([Diff]), type number}}) 
in
    #"Grouped Rows"

[mikem3]

This is fantastic thank you.  The issue I run into is it I am not sure of the correct way to update the source from 

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwO9nT3U9JRMtU31DcyMDIGMsP9dY2MTY0MlWJ1opU8/RQCgvzdg1yDg8GqjLCqCg518vUMCXF1gZhkgMuoINcwT9dwiCLs9gW5erk6ww0yJspNhibEOMocpkgBn6sssKtyDADaGAYzyhKLolgA"

 to my actual source which is a SQL database with a query to my dataset.  I tried adding how I thought it would work but I get an agument error so I'm probably putting missing/inserting an extra ( or , somewhere

Source = Sql.Database("ogssqlbi03ptul", "public_rpt", [Query="select * from maximo.MAXIMO_OGSLOCUSWOSTATUS"], let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STATUS = _t, CHANGEDATE = _t, WONUM = _t]),

[Vijay_A_Verma]

Pull your data from DB through PQ and a source line will be generated. Just paste this source line replacing source line in my code.