Hi,
I recently had to pull some data out of Jira, but couldn't see an easy way to do it natively in Power BI / Power Query, so created a custom function that does it for me based on a JQL statement.
Thought it might be useful to others, so have posted below (for my Jira instance this also works with an auto refresh in the service).
All you need to do is paste the below code into a blank query, then update the jiraDomain to your domain name.
You'll then see a function with 2 parameters, the first wants a JQL statement and the second needs a comma seperated list of the fields that you want from Jira (e.g. "key,Summary,Assignee,Status,Created,Project").
To autheniticate, I've been using basic auth with the username as my company email and the password as a Jira API token.
let Source = (jql as text, optional columnsToKeep as text) =>
let
// demo data
// jql = "project = JSD order by created DESC",
// columnsToKeep = "key,Summary,Assignee,Status,Created,Project",
jiraDomain = "https://<your-domain>.atlassian.net",
//get data from Jira using JQL
getJqlResult = (jql as text)=>
let
createQueryTable = Table.FromList(List.Generate(() => 0, each _ < Json.Document(Web.Contents(jiraDomain & "/rest/api/3/search",[RelativePath="?fields=key&jql="&jql]))[total], each _ + 50), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
addQueries = Table.AddColumn(createQueryTable, "queryLists", each Json.Document(Web.Contents(jiraDomain & "/rest/api/3/search",[RelativePath="?startAt="&Text.From([Column1])&"&jql="&jql]))[issues]),
removeStartAt = Table.RemoveColumns(addQueries,{"Column1"}),
expandQueryLists = Table.ExpandListColumn(removeStartAt, "queryLists"),
expandIssues = Table.ExpandRecordColumn(expandQueryLists, "queryLists", {"id", "key", "fields"}, {"id", "key", "fields"})
in
expandIssues,
//get data a list of Jira fields and what the display names are
getJiraFieldsAndNames = () =>
let
jiraFieldNameList = Json.Document(Web.Contents(jiraDomain & "/rest/api/3/field")),
fieldRecordsToTable = Table.FromList(jiraFieldNameList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expandFieldRecords = Table.ExpandRecordColumn(fieldRecordsToTable, "Column1", {"id", "key", "name"}, {"id", "key", "name"})
in
expandFieldRecords,
//create a list of lists that can be used to rename from field system names
createJiraRenameList = (listOfColumnsToRename as list)=>
let
fieldNames = getJiraFieldsAndNames(),
countNameInstances = Table.AddColumn(fieldNames, "# of Name Columns", each let thisName = [name] in Table.RowCount(Table.SelectRows(fieldNames, each [name] = thisName))),
checkWhetherFieldRequired = Table.AddColumn(countNameInstances, "renameRequired", each List.Contains(listOfColumnsToRename,[key])),
filterToRequired = Table.SelectRows(checkWhetherFieldRequired, each ([renameRequired] = true) and ([#"# of Name Columns"] = 1)),
addRenameListItems = Table.AddColumn(filterToRequired, "renameListItem", each {[key],[name]}),
createRenameList = Table.Column(addRenameListItems,"renameListItem")
in
createRenameList,
//main code
columnsToKeepAsList =
if columnsToKeep = null
then null
else Text.Split(Text.Replace(Text.Replace(columnsToKeep," ,",","),", ",","),","),
jqlQueryResult = getJqlResult(jql),
columnNamesAsList = Table.Column(Record.ToTable(jqlQueryResult{0}[fields]),"Name"),
expandIssueFields = Table.ExpandRecordColumn(jqlQueryResult, "fields", columnNamesAsList, columnNamesAsList),
renameColumns = Table.RenameColumns(expandIssueFields,createJiraRenameList(columnNamesAsList)),
selectColumnsToKeep =
if columnsToKeep = null
then renameColumns
else Table.SelectColumns(renameColumns,columnsToKeepAsList)
in
selectColumnsToKeep
in Source
