Forum Discussion
Solved
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 meth...
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
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