Forum Discussion

Ryan0096's avatar
Ryan0096
Frequent Visitor
2 years ago
Solved

Need date differences by status change when status changes

We have work IDs that can go through status 0-10  and it is not uncommon for it to go forward and backwards.  Trying to get the date changes between the sequences so min when it ws in status x and mi...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Your updated example makes more sense, except for the missing Datediff entry on row 4 which I assume is a typo.  Try this:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", Int64.Type}, {"Date", type date}}),

//Note use of GroupKind.Local
//Add a column showing the minimum date for each status group
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Status"}, {
        {"all", (t)=> Table.AddColumn(t, "Min Date", each List.Min(t[Date])),     
        type table [ID=nullable number, Status=nullable number, Date=nullable date, Min Date = nullable date]}
        }, GroupKind.Local),

    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Min Date"}),

//Shift the min date column for easier comparisons
    #"Shifted Min Date" = Table.FromColumns(
        Table.ToColumns(#"Expanded all") &
        {{null} & List.RemoveLastN(#"Expanded all"[Min Date])},
        type table [ID=nullable number, Status=nullable number, Date=nullable date, Min Date = nullable date, Shifted Min = nullable date]
        ),

//Add the Datediff column at the change in status position
    #"Add Need"= Table.AddColumn(#"Shifted Min Date", "Datediff on Change", 
            each if Duration.Days([Min Date] - [Shifted Min])= 0 then null else Duration.Days([Min Date] - [Shifted Min]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Add Need",{"Shifted Min", "ID", "Min Date"})
in
    #"Removed Columns"

     

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Hi Ryan0096, what about this?

 

For last row there is no end date so it calculates today minus 7th of April 2024 which is 32 days.

 

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCgAxCAPQu7gulETnd5bS+19jqN1VXbh5hEAcQ0C1S5pgXUenyWwnPydzp63wL3femTPWwxmFVzV8M9ce4nAGc2e61uJv1Hmn5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", Int64.Type}, {"Date", type date}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"ID", "Status"},
        {{"ChangeDate", each [Date]{0}, type date}},
        GroupKind.Local,
        (s,c)=> Byte.From( (s[ID] = c[ID] and s[Status] <> c[Status]) or s[ID] <> c[ID] ) ),
    Fn_TotalDays = (myTable as table)=>
        [ a = List.Buffer(myTable[ChangeDate]),
          lg = 
            List.Generate(
                ()=> [ x = 0, y = Duration.TotalDays(a{x+1} - a{x}) ],
                each [x] < List.Count(a),
                each [ x = [x]+1, y = Duration.TotalDays((try a{x+1} otherwise Date.From(DateTime.FixedLocalNow())) - a{x}) ],
                each [y]
            ),
        toTable = Table.FromColumns(Table.ToColumns(myTable) & {lg}, Value.Type(myTable & #table(type table[Total Days=Int64.Type],{})))
        ][toTable],
    GroupedRows2 = Table.Group(GroupedRows, {"ID"}, {{"All", each 
        [ a = Fn_TotalDays(_),
          b = Table.Group(a, {"ID", "Status"}, {{"Total Days", (x)=> List.Sum(x[Total Days]), Int64.Type}})
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows2[All])
in
    CombinedAll