Dear Team,
Can someone help me in connecting Jira with Power BI without help of any plugins.
I have already tried using below solution but the table is showing as empty. Could you please help me here.
let
// Set your Jira Cloud URL details - change 'yourDomain' as appropriate
yourJiraInstance = "https://yourDomain.atlassian.net/rest/api/2/",
// ===== Firstly set the scope of your preferred query =======================
// basic requirements here are:
// jql - this variable is needed further below.
// - holds the JQL query that homes in on the issues of interest
// - you can get as creative as you want
// - start with "&jql=" and then add whatever is appropriate
//
// .. so choose one of the jql lines below (uncomment), but make sure you
// only have one definition of the jql variable!
//
// -=-=- OPTION 1 - All issues from your instance
// set jql to empty string to pull all issues from your instance
//jql = "",
//
// -=-=- OPTION 1b - Open/active issues
jql = "&jql=status+not+in+(Cancelled,+Closed,+Done)",
//
// -=-=- OPTION 2 - From a specific project
//jql = "&jql=project=yourProjectNameHere",
//
// -=-=- OPTION 2b - active open issues from a specfic project
//jql = "&jql=project=yourProjectNameHere+and+status+not+in+(Cancelled,+Closed,+Done)",
//
// -=-=- OPTION 3 - Open issues assigned to a specific person
//person = "nameOfSpecificPerson",
//jql = "&jql=assignee=" & person,
//
// -=-=- OPTION 4 - Something else .. make the JQL search as specific as you want
//jql = ...,
// So lets construct the actual base query string to send to Jira
qryBase = yourJiraInstance & "search?maxResults=100" & jql & "&startAt=",
// ===== Pull the records from Jira Cloud ======
// this next bit is thanks to and refined from Tiago Machado comments in this post:
// https://community.atlassian.com/t5/Marketplace-Apps-questions/All-data-not-displayed-in-Power-BI-from-Jira/qaq-p/723117
//
// So we need to figure out how many records there are.
// .. and an initial query starting at record 0 will give us that ...
Source = Json.Document(Web.Contents( qryBase & "0" )),
numIssues = Source[total],
// Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
startAtList = List.Generate(()=>0, each _ < numIssues, each _ +100),
urlList = List.Transform(startAtList, each qryBase & Text.From(_) ),
data = List.Transform(urlList, each Json.Document(Web.Contents(_))),
// ===== Consolidate records into a single list ======
// so we have all the records in data, but it is in a bunch of lists each 100 records
// long. The issues will be more useful to us if they're consolidated into one long list
//
// In essence we need extract the separate lists of issues in each data{i}[issues] for 0<=i<#"total"
// and concatenate those into single list of issues .. from which then we can analyse
//
// to figure this out I found this post particulary helpful (thanks Vitaly!):
// https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/
//
// so first create a single list that has as its members each sub-list of the issues,
// 100 in each except for the last one that will have just the residual list.
// So iLL is a List of Lists (of issues):
iLL = List.Generate(
() => [i=-1, iL={} ],
each [i] < List.Count(data),
each [
i = [i]+1,
iL = data{i}[issues]
],
each [iL]
),
// and finally, collapse that list of lists into just a single list (of issues)
issues = List.Combine(iLL),
// =============================================================
// so now we've got the long list of issues back from JIRA
// ... now to do something with this - extract the bits we want
//
// at this point you have two options
// 1. just keep all the code below in place
// .. and you'll see the results of this straight away. Do that first.
// 2. after you have done that option 1., I recommend:
// - re-edit this query in Advanced Editor
// - delete all the code below. yes, DELETE it all
// - close/save this query, and then
// - to figure out how all this works,
// - follow the guidance from this post:
// https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
// ... starting half way down with para that begins "Drilling from the top level of the record ..."
// Those details there made all the difference to me figuring this out
// righto .. so here is the code that is one way to split out the details into some fields that might be interesting to you
#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"issue", "fields"}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"assignee", "created", "creator", "description", "issuetype", "parent", "priority", "project", "reporter", "resolution", "resolutiondate", "status", "summary", "updated"}, {"assigneeF", "created", "creatorF", "description", "issuetypeF", "parentF", "priorityF", "projectF", "reporterF", "resolutionF", "resolutiondate", "statusF", "summary", "updated"}),
#"Expanded assignee" = Table.ExpandRecordColumn(#"Expanded fields", "assigneeF", {"key"}, {"assignee"}),
#"Expanded creator" = Table.ExpandRecordColumn(#"Expanded assignee", "creatorF", {"key"}, {"creator"}),
#"Expanded issuetype" = Table.ExpandRecordColumn(#"Expanded creator", "issuetypeF", {"name"}, {"issuetype"}),
#"Expanded priority" = Table.ExpandRecordColumn(#"Expanded issuetype", "priorityF", {"name"}, {"priority"}),
#"Expanded project" = Table.ExpandRecordColumn(#"Expanded priority", "projectF", {"key"}, {"project"}),
#"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded project", "reporterF", {"key"}, {"reporter"}),
#"Expanded resolution" = Table.ExpandRecordColumn(#"Expanded reporter", "resolutionF", {"name"}, {"resolution"}),
#"Expanded status" = Table.ExpandRecordColumn(#"Expanded resolution", "statusF", {"name"}, {"status"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded status",{{"created", type datetimezone}, {"resolutiondate", type datetimezone}, {"updated", type datetimezone}}),
#"Expanded parentF" = Table.ExpandRecordColumn(#"Changed Type", "parentF", {"key"}, {"parent"})
in
#"Expanded parentF"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.