cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasonC Frequent Visitor
Frequent Visitor

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)?

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: I need help parsing a complex string into multiple fields

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
Specializing in Power Query Formula Language (M)
3 REPLIES 3
MarcelBeug Super Contributor
Super Contributor

Re: I need help parsing a complex string into multiple fields

What would be the result from your example?

Specializing in Power Query Formula Language (M)
JasonC Frequent Visitor
Frequent Visitor

Re: I need help parsing a complex string into multiple fields

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

MarcelBeug Super Contributor
Super Contributor

Re: I need help parsing a complex string into multiple fields

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
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 345 members 3,629 guests
Please welcome our newest community members: