Forum Discussion

JasonC's avatar
JasonC
Regular Visitor
8 years ago
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...
Need Help
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

What would be the result from your example?

  • JasonC's avatar
    JasonC
    Regular Visitor
    8 years ago

    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's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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