Excel PowerQuery: Get data from Jira

Lately I’ve done a lot with Excel and PowerQuery. I love(!) data and analyzing and getting everything valuable for me in short amount of time. As soon as possible, at best with just one click.

For a few month now I’m a project manager and need to answer to several questions. For some of them I need to join data from one system with data from jira.

And it’s easy!

There are two ways I’ve tried (our Jira is currently still on premise but for the cloud I think it’s the same).

Getting Jira data via CSV export

This is the first thing that worked for me but it’s not ideal. Especially since you have to take care, that your query won’t work if the amount of issues found will exceed 1000 results.

let
   Quelle = Csv.Document(Web.Contents("https:/[jourjira.com]/sr/jira.issueviews:searchrequest-csv-current-fields/[Your Query Id]/SearchRequest-[Your Query Id again].csv"),[Delimiter=","])
in
   Quelle

That’s it. You’ll have your fields and everything ready. But you’ll have to update your query within the query explorer and can’t just use “update” within the Excel ribbon.

Getting Jira data via Rest API

At first: I was also only able to get 1000 results even though I set maxResults within my request to 3000. Maybe someone has an idea?

let
    Quelle = Json.Document(Web.Contents("https://[yourjira.com]/rest/api/2/search?jql=filter=[your query id]&maxResults=3000"), 65001),

    issues = Quelle[issues],
    issuetable = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    records = Table.ExpandRecordColumn(issuetable, "Column1", {"id", "self", "key", "fields"}),
    recordtable = Table.ExpandRecordColumn(records, "fields", {"issuetype", "summary", "priority", "status", "resolution", "assignee", "lastviewed", "updated", "duedate", "labels", "timeoriginalestimate"}),

    expandtype = Table.ExpandRecordColumn(recordtable, "issuetype", {"name"}, {"type"}),

    expandprio = Table.ExpandRecordColumn(expandtype, "priority", {"name"}, {"priority"}),

    expandstatus = Table.ExpandRecordColumn(expandprio, "status", {"name"},{"status"}),

    expandresolution = Table.ExpandRecordColumn(expandstatus, "resolution", {"name"},{"resolution"}),

    expandassignee = Table.ExpandRecordColumn(expandresolution, "assignee", {"displayName"},{"assignee"}),

    labelstostring = Table.TransformColumns(expandassignee, {"labels", each Text.Combine(List.Transform(_, Text.From), ","), type text}),

    estimatetoPT = Table.AddColumn(labelstostring, "Effort in PT", each ([timeoriginalestimate] / 60 / 60 / 8), type number) 
in
    estimatetoPT

Leave a Reply

Your email address will not be published. Required fields are marked *