Forum Discussion
I need help parsing a complex string into multiple fields
I'm using Power BI to generate reports from Jira (a web-based project management software).
Sprint is one of the fields that I'm reporting on. For those unfamiliar with project management methodologies, sprints are basically two week periods with a defined start date and end date. Tasks are assigned to a sprint, which basically defines the due date for that task. If a task isn't completed during the assigned sprint, the task is re-assigned to the next sprint, effectively bumping the due date by 2 weeks.
For every task, Jira records every sprint that it's been assigned to. Most tasks are only associated with one sprint, while others can be associated with multiple sprints.
Jira's REST API returns JSON data. Each task is a row. Each row contains a single sprint field, that field contains a string with the information for every sprint that the task has been assigned to.
Below is an example of the Sprint field for a task that's been assigned to 3 different sprints. I've reformatted it a bit to make it a little easier to read.
com.atlassian.greenhopper.service.sprint.Sprint@d5d5a74[id=18,rapidViewId=44,state=CLOSED,name=2017 November A,goal=,startDate=2017-11-21T23:08:48.321Z,endDate=2017-12-05T23:08:00.000Z,completeDate=2017-12-05T19:42:17.866Z,sequence=18],
com.atlassian.greenhopper.service.sprint.Sprint@417c465f[id=20,rapidViewId=57,state=ACTIVE,name=2017 December A,goal=,startDate=2017-12-05T17:00:28.208Z,endDate=2017-12-19T17:00:00.000Z,completeDate=<null>,sequence=20],
com.atlassian.greenhopper.service.sprint.Sprint@7f4d1004[id=15,rapidViewId=44,state=CLOSED,name=Dongle,goal=,startDate=2017-11-07T21:00:39.622Z,endDate=2017-11-16T21:00:00.000Z,completeDate=2017-11-21T23:06:23.957Z,sequence=15]
I know how to separate the string into individual fields, that's easy. The problem is that the string isn't in any particular order, and I only need the first and last sprint, which may or may not be the first and last sprints in the string.
So how can I configure Power BI to find the section of the string with the lowest ID (the oldest sprint) and the section with the highest ID (the newest sprint)?
This would be my Power Query solution:
let Source = #table(type table[Sprint = text],{{"com.atlassian.greenhopper.service.sprint.Sprint@d5d5a74[id=18,rapidViewId=44,state=CLOSED,name=2017 November A,goal=,startDate=2017-11-21T23:08:48.321Z,endDate=2017-12-05T23:08:00.000Z,completeDate=2017-12-05T19:42:17.866Z,sequence=18], com.atlassian.greenhopper.service.sprint.Sprint@417c465f[id=20,rapidViewId=57,state=ACTIVE,name=2017 December A,goal=,startDate=2017-12-05T17:00:28.208Z,endDate=2017-12-19T17:00:00.000Z,completeDate=<null>,sequence=20], com.atlassian.greenhopper.service.sprint.Sprint@7f4d1004[id=15,rapidViewId=44,state=CLOSED,name=Dongle,goal=,startDate=2017-11-07T21:00:39.622Z,endDate=2017-11-16T21:00:00.000Z,completeDate=2017-11-21T23:06:23.957Z,sequence=15] "}}), AddedStartDates = Table.AddColumn(Source,"StartDates", each List.Transform(List.Skip(Text.Split([Sprint],"startDate=")), each Date.From(Text.Start(_,10)))), AddedEndDates = Table.AddColumn(AddedStartDates,"EndDates", each List.Transform(List.Skip(Text.Split([Sprint],"endDate=")), each Date.From(Text.Start(_,10)))), AddedNames = Table.AddColumn(AddedEndDates,"Names", each List.Transform(List.Skip(Text.Split([Sprint],"name=")), each Text.BeforeDelimiter(_,",")), type {text}), AddedDatesAndNamesTables = Table.AddColumn(AddedNames, "DatesAndNamesTables", each Table.FromColumns({[Names],[StartDates],[EndDates]},{"Name","StartDate","EndDate"})), AddedFirstRecord = Table.AddColumn(AddedDatesAndNamesTables, "FirstRecord", each Table.Min([DatesAndNamesTables], "StartDate")), ExpandedFirstRecord = Table.ExpandRecordColumn(AddedFirstRecord, "FirstRecord", {"Name", "StartDate"}, {"First Sprint Name", "First Sprint Start Date"}), AddedLastRecord = Table.AddColumn(ExpandedFirstRecord, "LastRecord", each Table.Max([DatesAndNamesTables], "StartDate")), ExpandedLastRecord = Table.ExpandRecordColumn(AddedLastRecord, "LastRecord", {"Name", "EndDate"}, {"Last Sprint Name", "Last Sprint End Date"}), RemovedColumns = Table.RemoveColumns(ExpandedLastRecord,{"Sprint", "StartDates", "EndDates", "Names", "DatesAndNamesTables"}), Typed = Table.TransformColumnTypes(RemovedColumns,{{"First Sprint Name", type text}, {"First Sprint Start Date", type date}, {"Last Sprint Name", type text}, {"Last Sprint End Date", type date}}) in Typed
3 Replies
What would be the result from your example?
The desired result would be having the data split into 4 fields, the oldest sprint and it's start date, and the most recent sprint and it's end date. Based on the sample data above, those results would be....
First Sprint Name = Dongle
First Sprint Start Date = 11/7/17
Last Sprint Name = 2017 December A
Last Sprint End Date = 12/19/17
This would be my Power Query solution:
let Source = #table(type table[Sprint = text],{{"com.atlassian.greenhopper.service.sprint.Sprint@d5d5a74[id=18,rapidViewId=44,state=CLOSED,name=2017 November A,goal=,startDate=2017-11-21T23:08:48.321Z,endDate=2017-12-05T23:08:00.000Z,completeDate=2017-12-05T19:42:17.866Z,sequence=18], com.atlassian.greenhopper.service.sprint.Sprint@417c465f[id=20,rapidViewId=57,state=ACTIVE,name=2017 December A,goal=,startDate=2017-12-05T17:00:28.208Z,endDate=2017-12-19T17:00:00.000Z,completeDate=<null>,sequence=20], com.atlassian.greenhopper.service.sprint.Sprint@7f4d1004[id=15,rapidViewId=44,state=CLOSED,name=Dongle,goal=,startDate=2017-11-07T21:00:39.622Z,endDate=2017-11-16T21:00:00.000Z,completeDate=2017-11-21T23:06:23.957Z,sequence=15] "}}), AddedStartDates = Table.AddColumn(Source,"StartDates", each List.Transform(List.Skip(Text.Split([Sprint],"startDate=")), each Date.From(Text.Start(_,10)))), AddedEndDates = Table.AddColumn(AddedStartDates,"EndDates", each List.Transform(List.Skip(Text.Split([Sprint],"endDate=")), each Date.From(Text.Start(_,10)))), AddedNames = Table.AddColumn(AddedEndDates,"Names", each List.Transform(List.Skip(Text.Split([Sprint],"name=")), each Text.BeforeDelimiter(_,",")), type {text}), AddedDatesAndNamesTables = Table.AddColumn(AddedNames, "DatesAndNamesTables", each Table.FromColumns({[Names],[StartDates],[EndDates]},{"Name","StartDate","EndDate"})), AddedFirstRecord = Table.AddColumn(AddedDatesAndNamesTables, "FirstRecord", each Table.Min([DatesAndNamesTables], "StartDate")), ExpandedFirstRecord = Table.ExpandRecordColumn(AddedFirstRecord, "FirstRecord", {"Name", "StartDate"}, {"First Sprint Name", "First Sprint Start Date"}), AddedLastRecord = Table.AddColumn(ExpandedFirstRecord, "LastRecord", each Table.Max([DatesAndNamesTables], "StartDate")), ExpandedLastRecord = Table.ExpandRecordColumn(AddedLastRecord, "LastRecord", {"Name", "EndDate"}, {"Last Sprint Name", "Last Sprint End Date"}), RemovedColumns = Table.RemoveColumns(ExpandedLastRecord,{"Sprint", "StartDates", "EndDates", "Names", "DatesAndNamesTables"}), Typed = Table.TransformColumnTypes(RemovedColumns,{{"First Sprint Name", type text}, {"First Sprint Start Date", type date}, {"Last Sprint Name", type text}, {"Last Sprint End Date", type date}}) in Typed
