# Power Query calculate the difference between 2 dates for each ID

• ### 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

• 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