I've made an updated version of the funciton that uses the v3 API (the old one is deprecated). Also runs a bit quicker.
Instructions on how to use this function:
- Copy the code below into a blank query in Power BI (using the advanced editor).
- Update the 'jiraDomain' variable using advanced editor (near the top of the code) with the domain you see when you login to your Jira.
- Generate and save an access token as per Manage API tokens for your Atlassian account | Atlassian Support.
- Invoke the function:
- Put the email address you use to login to Jira in the 'jiraUsername'.
- Put the token from step 3. above into the 'jiraAccessToken'.
- Put a comma demilted list of the fields you want to return in the 'fields' parameter e.g. 'summary, parent, status':
- This list is not case sensitive.
- You can enter custom field names here.
- If you have more than 1 custom field with the same name it will return both fields with the custom field id added as a suffix.
- Add a JQL statement that specifies the issues you want to return.
- If you want to return everything you can enter 'key IS NOT EMPTY'.
let
Source = (jiraUsername as text, jiraAccessToken as text, fields as text, jql as text) =>
let
//test data
// jiraUsername = jiraUser,
// jiraAccessToken = jiraToken,
// fields = "id, parent, summary",
// jql = "project = kan",
//!!!UPDATE THE BELOW VARIABLE!!!
jiraDomain = "https://<your-domain>.atlassian.net",
//authenication
auth = Binary.ToText(Text.ToBinary(jiraUsername & ":" & jiraAccessToken), BinaryEncoding.Base64),
//convert_field_names_to_ids function
convert_field_names_to_ids = (jiraUsername as text, jiraAccessToken as text, fields as text) =>
let
fieldsConverted = if fields = "id" then "key" else Text.Replace(Text.Replace(fields," ,",","),", ",","),
fieldsToGetAsList = Text.Split(Text.Lower(fieldsConverted),","),
jiraFieldNameList = Json.Document(Web.Contents(jiraDomain & "/rest/api/3/field", [Headers = [Authorization="Basic " & auth]])),
fieldRecordsToTable = Table.FromList(jiraFieldNameList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expandFieldRecords = Table.ExpandRecordColumn(fieldRecordsToTable, "Column1", {"id", "key", "name"}, {"id", "key", "name"}),
addGetFieldCheck = Table.AddColumn(expandFieldRecords, "getField?", each List.Contains(fieldsToGetAsList,Text.Lower([name]))),
filterToFieldsToGet = Table.SelectRows(addGetFieldCheck, each ([#"getField?"] = true)),
createFieldsString = Text.Combine(Table.Column(filterToFieldsToGet,"id"),",")
in
createFieldsString,
//get_jira_fields_with_rename_lists
get_jira_fields_with_rename_lists = (jiraUsername as text, jiraAccessToken as text)=>
let
auth = Binary.ToText(Text.ToBinary(jiraUsername & ":" & jiraAccessToken), BinaryEncoding.Base64),
jiraFieldNameList = Json.Document(Web.Contents(jiraDomain & "/rest/api/3/field", [Headers = [Authorization="Basic " & auth]])),
fieldRecordsToTable = Table.FromList(jiraFieldNameList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expandFieldRecords = Table.ExpandRecordColumn(fieldRecordsToTable, "Column1", {"id", "key", "name"}, {"id", "key", "name"}),
#"Added Custom" = Table.AddColumn(expandFieldRecords, "renameList", each if List.Count(let name = [name] in Table.Column(Table.SelectRows(expandFieldRecords, each ([name] = name)),"name"))>1 then {[id],[name] & " (" & [id] & ")"} else {[id],[name]})
in
#"Added Custom",
//field variables
fields_list = Text.Lower(Text.Replace(Text.Replace(fields," ,",","),", ",",")),
id_or_key_request = fields_list = "id" or fields_list = "key" or fields_list = "id,key" or fields_list = "key,id",
fields_for_query = if fields = null or id_or_key_request then {"key"} else List.RemoveItems(Text.Split(convert_field_names_to_ids(jiraUsername, jiraAccessToken, fields_list), ","), {"issuekey"}),
get_issues_list_function = (optional next_page_token as text) =>
Json.Document(Web.Contents(jiraDomain & "/rest/api/3/search/jql",
[
Headers = [
Authorization="Basic " & auth,
#"Content-Type" = "application/json"
],
Content = Json.FromValue(
[
jql = jql,
maxResults = 5000,
fields = fields_for_query,
nextPageToken = next_page_token
]
)
]
)),
get_issues =
List.Generate(
() => get_issues_list_function(),
each Record.HasFields(_, "issues"),
each try get_issues_list_function(_[nextPageToken]) otherwise []
),
#"Converted to Table" = Table.FromList(get_issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"issues"}, {"issues"}),
#"Expanded issues" = Table.ExpandListColumn(#"Expanded Column1", "issues"),
#"Expanded issues 1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"id", "key", "fields"}, {"id", "key", "fields"}),
#"Expanded fields" = if id_or_key_request then #"Expanded issues 1" else Table.ExpandRecordColumn(#"Expanded issues 1", "fields", fields_for_query),
rename_list = Table.SelectRows(get_jira_fields_with_rename_lists(jiraUser, jiraToken), each List.Contains(fields_for_query, [key]))[renameList],
#"Renamed columns" = Table.RenameColumns(#"Expanded fields", rename_list)
in
#"Renamed columns"
in
Source
