Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,464,714
Community Members
 
Community Events
176
Community Groups

Connect Jira with powerbi without using plugins

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"

 

1 answer

Hi, did you get any solution on this? I am also facing the same issue. 

Vikrant Yadav Community Leader Aug 03, 2022

@Omkar Chogale  With the help of JQL REST API you can fetch data from JIRA to Power BI 

In Power BI >> select Data Source >> Web >> Enter GET issue search using JQL API something like this :-  https://your-domain.atlassian.net/rest/api/3/search?jql=project%20%3D%20HSP

Then Power Query editor will open, parse json data into power BI. 

https://www.youtube.com/watch?v=MJsZaWO9Uho

https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-search/#api-rest-api-3-search-get

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events