Forum Discussion

mikem3's avatar
mikem3
New Member
3 years ago

Creating Buckets to add up the time between changes

My dataset has 3 columns that can look like this. 

 

STATUSCHANGEDATEWONUM
ASSIGN5/1/2023WO-23521
IN PROGRESS5/2/2023WO-23521
SUBMITTED5/10/2023WO-23521
IN REVIEW5/11/2023WO-23521
REJECTED5/13/2023WO-23521
IN PROGRESS5/14/2023WO-23521
SUBMITTED5/17/2023 WO-23521
IN REVIEW5/18/2023 WO-23521
APPROVED5/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?

 

Need Help

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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's avatar
      mikem3
      New Member
      3 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        3 years ago

        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.