none
Power Query calculate the difference between 2 dates for each ID RRS feed

  • Question

  • Hi,

    As you can see on this table i've got a "bug_id" and for each bug_id they change their status (Planned -> in Progress) and (In progress -> to something) and me i want to calculate how much it take to change the status (Planned -> in Progress) to (In progress -> ???)

    For example "bug_id" = 5253 and his status changed to (Planned -> in Progress) at date "1557472940" and his status changed again to (In progress -> ???) at date "1557751772" i want to calculate the difference (1557751772 - 1557472940) for each "bug_id"

    Thanks

    Monday, May 27, 2019 9:42 AM

Answers

  • Hi,

    If for each bug_id "In Progress" occurs only once in old_value column (same for new_value column), you may use following code:

    let
        Source = YourTable,
        group = Table.Group(Source, {"bug_id"}, {"Δ", each
                _{[old_value = "In Progress"]}[date_modified] -
                _{[new_value = "In Progress"]}[date_modified]})
    in
        group

    Otherwise, technique may be like this (in this case first occurrence of "In Progress" will be kept):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        group = Table.Group(Source, {"bug_id"}, {"Δ", each
                      Table.SelectRows(_, each [old_value] = "In Progress")[date_modified]{0} -
                      Table.SelectRows(_, each [new_value] = "In Progress")[date_modified]{0}})
    in
        group
    Wednesday, May 29, 2019 9:18 AM

All replies

  • Hi,

    If for each bug_id "In Progress" occurs only once in old_value column (same for new_value column), you may use following code:

    let
        Source = YourTable,
        group = Table.Group(Source, {"bug_id"}, {"Δ", each
                _{[old_value = "In Progress"]}[date_modified] -
                _{[new_value = "In Progress"]}[date_modified]})
    in
        group

    Otherwise, technique may be like this (in this case first occurrence of "In Progress" will be kept):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        group = Table.Group(Source, {"bug_id"}, {"Δ", each
                      Table.SelectRows(_, each [old_value] = "In Progress")[date_modified]{0} -
                      Table.SelectRows(_, each [new_value] = "In Progress")[date_modified]{0}})
    in
        group
    Wednesday, May 29, 2019 9:18 AM
  • If @Aleksei's reply didn't sort this for you, please post that actual table rather than just a picture. That would let us jump right in.

    Ian

    Wednesday, May 29, 2019 1:20 PM