Connect Jira with powerbi without using plugins

[himaja]

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"

[Omkar Chogale]

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

[Vikrant Yadav]

@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