Calculate time between two dates in same JSON

[FlorentS17]

Hi,

I have a list of issues from Jira in a table a created a custom field to retrieve changelog for each issue:

Json.Document(
Web.Contents(
Text.Combine({"https://mydomain/rest/api/2/issue/",[issues.key], "?expand=changelog&fields=changelog"})))

 This sub query returns, for each parent row, a JSON like this:

{
    "changelog": {
        ...
        "histories": [{
            "created": "2023-08-29T11:44:45.876+0200",
            "items": [{
                "field": "Status",
                "fromString": "To Do"
           }
           },{
            "created": "2023-08-29T16:44:45.876+0200",
            "items": [{
                "field": "Status",
                "fromString": "Done"
           }
...

 I would like to extract time between dates in "created" where related "items"'s field's value is "Status" and from "fromString" is "Done" and "To Do".

I don't know how to extract all these data.

[Mahesh0016]

@FlorentS17  I hope this helps you. Thank You.

To extract the time between dates in the "created" field where the related "items" have a field value of "Status" that transitions from "To Do" to "Done," you can follow these steps in Power Query Editor in Power BI or Excel Power Query.

Assuming you already have the JSON data loaded into a table and you want to create a new column with the time difference, here's how you can do it:

1. Load your data into Power Query.

2. Select the column that contains the JSON data (let's assume it's called "JsonData").

3. Add a custom column that extracts the time difference using the following M code:

```M
let
Source = YourSourceTable,
Custom1 = Table.AddColumn(Source, "Changelog", each Json.Document([JsonData])),
Custom2 = Table.AddColumn(Custom1, "TimeBetweenChanges", each
let
histories = [Changelog][histories],
filteredHistories = List.Select(histories, each List.NonNullCount([items]) > 0),
relevantChanges = List.Select(filteredHistories, each
List.NonNullCount(List.Select([items], each [field] = "Status" and [fromString] = "To Do" or [fromString] = "Done")) > 1),
timeDifferences = List.Transform(relevantChanges, each
let
changes = [items],
fromToDo = List.First(List.Select(changes, each [field] = "Status" and [fromString] = "To Do")),
fromDone = List.First(List.Select(changes, each [field] = "Status" and [fromString] = "Done")),
timeDifference = Duration.From([created] - fromToDo[created])
in
timeDifference
)
in
List.Sum(timeDifferences)
)
in
Custom2
```

Make sure to replace "YourSourceTable" with the actual name of your table.

This code adds a new column called "TimeBetweenChanges" that calculates the total time difference between "To Do" and "Done" status transitions for each issue based on the "created" field within the "changelog" data.

4. Close and apply the changes to your table.

Now, you should have a new column in your table that shows the total time between "To Do" and "Done" status transitions for each issue.