Extracting values from JSON with Power Query

[alexey-denisov]

Hi everyone!

I have a connection to Jira Cloud instance where the data is obtained. There's a number of fields that I already use like "project,issuetype,status,reporter,priority,created,updated,resolution,resolutiondate,components".
I'd like to add "customfield_" data into columns but the values are located deep inside the JSON structure so it's a long way of expanding records, lists and getting values.

I need to get SLA breach time from this part of JSON:

"customfield_10038": {
	"id": "58",
	"name": "Time to first response",
	"_links": {
		"self": "https://domain.atlassian.net/rest/servicedeskapi/request/29418/sla/58"
	},
	"completedCycles": [
		{
			"startTime": {
				"iso8601": "2021-03-12T10:13:05+0300",
				"jira": "2021-03-12T10:13:05.877+0300",
				"friendly": "12.03.2021 10:13",
				"epochMillis": 1615533185877
			},
			"stopTime": {
				"iso8601": "2021-03-15T22:22:34+0300",
				"jira": "2021-03-15T22:22:34.071+0300",
				"friendly": "15.03.2021 22:22",
				"epochMillis": 1615836154071
			},
			"breached": true,
			"goalDuration": {
				"millis": 900000,
				"friendly": "15m"
			},
			"elapsedTime": {
				"millis": 302968194,
				"friendly": "84h 9m"
			},
			"remainingTime": {
				"millis": -302068194,
				"friendly": "-83h 54m"
			}
		}
	]
},

When I add this customfields I can see only columns of records whic need to be digged into:

So after expanding those columns to get Breach time value I have this long journey in query:

let
    Source = JiraData,
    #"Removed Columns" = Table.RemoveColumns(Source,{"expand", "Meta", "Timestamp", "self", "id"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Removed Columns", "fields", {"resolutiondate", "created", "customfield_10038", "customfield_10037"}, {"resolutiondate", "created", "timetoresponse", "timetoresolve"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded fields",{{"resolutiondate", type datetimezone}, {"created", type datetimezone}}),
    #"Add date created" = Table.AddColumn(#"Changed Type", "Date Created", each Date.From([created]), type date),
    #"Add week created" = Table.AddColumn(#"Add date created", "Week Created", each Date.EndOfWeek([Date Created]), type date),
    #"Add time created" = Table.AddColumn(#"Add week created", "Time Created", each Time.From([created]), type time),
    #"Add hour created" = Table.AddColumn(#"Add time created", "Hour Created", each Time.Hour([created]), Int64.Type),
    #"Add date resolved" = Table.AddColumn(#"Add hour created", "Date Resolved", each Date.From([resolutiondate]), type date),
    #"Add week resolved" = Table.AddColumn(#"Add date resolved", "Week Resolved", each Date.EndOfWeek([Date Resolved]), type date),
    #"Add time resolved" = Table.AddColumn(#"Add week resolved", "Time Resolved", each Time.From([resolutiondate]), type time),
    #"Add hour resolved" = Table.AddColumn(#"Add time resolved", "Hour Resolved", each Time.Hour([resolutiondate]), Int64.Type),
    #"Expanded timetoresponse" = Table.ExpandRecordColumn(#"Add hour resolved", "timetoresponse", {"completedCycles"}, {"completedCycles"}),
    #"Expanded completedCycles" = Table.ExpandListColumn(#"Expanded timetoresponse", "completedCycles"),
    #"Expanded completedCycles1" = Table.ExpandRecordColumn(#"Expanded completedCycles", "completedCycles", {"stopTime"}, {"stopTime"}),
    #"Expanded stopTime" = Table.ExpandRecordColumn(#"Expanded completedCycles1", "stopTime", {"iso8601"}, {"iso8601"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded stopTime",{{"iso8601", type datetimezone}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"iso8601", type datetime}})
in
    #"Changed Type2"

And I can see the result in the table:

I guess there should be an easy way to get the value instead of taking 3 steps to get to the value.

I've been struggling with my report for quite a long time because I have tons of those fields any the query becomes too huge to read and to work with.

Kindly help me understand how to get the value "customfield_10038" -> "completedCycles" -> "stopTime" -> "iso8601" in a shortest way

[d_gosbell]

---

@alexey-denisov wrote:

Kindly help me understand how to get the value "customfield_10038" -> "completedCycles" -> "stopTime" -> "iso8601" in a shortest way

---

I can't see the output of "customfield_10038" in any of your screenshots, but assuming that it is showing up as a  record type you should be able to reference into it but doing "Add Custom Column" with an expression like the following:

= [customfield_10038][completedCycles][stopTime][iso8601]

[alexey-denisov]

Hi @d_gosbell 

Thanks for your reply.

You can see the initial data for this customfield, it has been added as "timetoresponse" column name which then was expanded up to "iso8601".

Your proposed method will only work if you have already exposed those columns. And my idea is to shorten the steps (last 6 steps) as much as possible to get to the data.

[d_gosbell]

---

@alexey-denisov wrote:

Your proposed method will only work if you have already exposed those columns. And my idea is to shorten the steps (last 6 steps) as much as possible to get to the data.

---

No, you don't need to expose these as columns at all. The expression syntax I showed will reference into the nested record structure.

Below is a screenshot showing your original json, then a column where I parsed into the same record form that you have and then a single step to extract the iso8601 field. The only change I had to make was to add the {0} reference after the [completedCycles] reference because I missed seeing the square brackets around that which indicate that this is an array of cycles. I don't know if you have extra logic you need to implement if there are multiple cycles as the expression I have here just grabs the first one.

= [customfield_10038][completedCycles]{0}[stopTime][iso8601]