All data not displayed in Power BI from Jira

[Takieddine BELKADI]

Hi,

now, i'm using Power BO to get Data from Jira.

Power BI get its own connection to connect to JIRA.

I found an issue : Power bI didn't load all data from JIRA.

I was asking my sefl if there is any limitation from JIRA to extract data to an other software (like Power BI).

Power BI is in cloud.

JIRA is in cloud

[Nick Cerneaz]

Hi all, my initial attempts to follow all the suggestions were troublesome and took me a while to figure it out ... especially the consolidation of the issues in batches of 100 into a single list.  So, just in case this helps some others (and as a memory jog for myself next time I try to do all this again), here is a summary of what it took to get it all to work.  Hope this helps ... 

Firstly credit to @Tiago Machado (above in this same post) who got me started (and as per the comments in your code Tiago there are a few lines that can be trimmed out, as below).

So open a new Power BI report (in Power BI Desktop), select Get Data > Other > Blank Query > Advanced Editor (right-click the query in the browser on LH side of screen to get context menu with the Advanced Editor option on it).  In the editor paste all this:

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"

Save it ... and with luck you'll see the issues in the data browser.

And from there you can Close & apply the query, and then build reports to your heart's content.

[Ahmad Al-Abbadi]

Thanks @Tiago Machado & @Nick Cerneaz 

[Raphaël Boesch]

Thanks to all to share about this topic,

I am testing your solution Nick,

In your code I have changed following elements
1) Replace by YourDomain our instance
2) For "jql", use Option 1 instead of option 1b 
// set jql to empty string to pull all issues from your instance
jql = "",

and I got the following error message.

-----------------------
IN FRENCH : 
Expression.Error : Désolé... Nous n'avons pas trouvé la colonne « Column1 » de la table.
Détails :
Column1

--------------------------------------------------------------------------

IN ENGLISH :
Sorry we haven't found "Column1" of the table.
------------------------

It seems that just after "Converted table", the table is empty.

See following picture :

Do you have an idea ?

I have a question regarding security, where do we give the authification information ?

Regards,

Raphaël

[Nick Cerneaz]

Hi Raphaël .. I suspect it is a default language thing (English on my system, French on yours).  The line before that .. this one:

#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

on your system probably splits the table into columns with column names in French .. and the subsequent code is trying to find a column called "Column 1" .. which I am guessing is not present in your table. 

Hence, delete everything beneath that line and try again.  From there you can probably just follow the second approach I suggested (and hopefully) that will work.

Good luck.

[Raphaël Boesch]

I Nick,

Thanks for you quick feedback :)

I cancelled all lines after

#"Converted to Table" =

The problem is that  I have nothing in the dataset. Normally I should the ability to convert table ...

Do you have an idea ?

I come back on my second question : where do we give the authification information ?

Regards,

[Ahmad Al-Abbadi]

Hi @Raphaël Boesch

In Edit Queries page select Data Source Settings --> Edit Permission --> then Edit type (Basic to use your Jira Credentials ) as in screenshot below.

[San]

Hello @Nick Cerneaz 

Thank you for the code.

I've tried your code and it works perfectly fine, but do you know how can I use the search string with a combination of project name and epic link?

For example jql= "&jql=project+in+(my project name)+epic link+in+(Key-121,+key-122,+key123)",

When I execute the code, Power BI fails to get the contents. Am I not doing something right?

Please excuse my ignorance. I am very new to Power BI.

Thanks.

San

[San]

I've figured out the query, instead of using Epic Link, try parentEpic -

jql = "&jql=parentEpic+in+(yourepickey-121,+yourepickey-122)",

Cheers!

[Raphaël Boesch]

Thanks Ahmad,

It works perfectly.
But I was hoping to have more fields. Too bad it's not enough for me).

Do you know an other way to get JIRA DATA without to use JIRA plugin ?

Regards,

Raphaël

[Ahmad Al-Abbadi]

Check this Post link 

[Les James]

Cheers for the post Nick! Very informative and saved me so much time! 

[Ray Brosius]

This works great in PowerBI desktop but I tried to publish this to the PowerBI Service and schedule a refresh.. but got the below error..

[Deleted user]

Has anyone found a way to overcome this issue? I would like to be able to schedule this dataset in the service, but it clearly does not like the dynamic nature of the URL that this script generates. 

[Regional Administrator]

To solve schedule refresh issue, you can use Jira PowerBI contect pack.

link: https://community.powerbi.com/t5/Desktop/Jira-and-Power-BI/td-p/393785

[Deleted user]

My organization does not allow the content pack to be used. I have an issue with the TLS SSL certificate when I configure the dataset to schedule refresh through a personal gateway. Any one have suggestions on getting this resolved?

[jasper.jorissen]

-delete-

[jasper.jorissen]

-delete-

[Luca Giorgio]

@Nick Cerneaz 

Hey, I tried your method but not having any luck.

I don't get any errors but I have nothing my results its completely empty. One thing I noticed is that you don't have any authentication in here. Where would I put this? Would I put it after the 0 in here: Source = Json.Document(Web.Contents( qryBase & "0" )),

[philip.buchwald]

@Nick Cerneaz 

Thanks your mehtod. Almost works for me. But I have one more question.
I want to use a jql containing my jira project name. The problem is, the project name contains a blank. Dont know how to handle that. Using %20 or + instead of blank do not work.

Any idea?

Best regards!

[Tiago Machado]

Hi @philip.buchwald 

You could use the project key instead of the project name on your JQL or use quotes.

Let's say you have a project named "Long name with spaces" and its issues are LNWS-1, LNWS-2, and so on. Your query could be:

project = "Long name with spaces"

or

project = LNWS

Since I'm supposing you are editing this query inside PowerBI, I suggest the second (with the project key) to avoid any confusion with quotes

[Tiago Machado]

Hi @Takieddine BELKADI,

I had no luck with the JIRA connector in Power BI - very few items were shown.

So I had to develop an advanced query to run a custom search in JIRA and, from there process the JSON results.

I'm not that happy with the performance, but, at least, the data is retrieved. If you want, I can share the code here... (just let me know how familiar you are with advanced query editor in Power BI - "I have seen it" or "What is he talking about")

Regards,

Tiago.

[Takieddine BELKADI]

Thank you very much for your help.

Yes it would be fantastic to have your script.

I'm not expert en Power BI....But I can learn.

Best Regards,

Takieddine

[Tiago Machado]

Sure.

Before pasting the code, here some brief explanation on what we are going to do:

What we are going to use JIRA REST to make a query using JQL.

This isn't natively supported in Power BI, so we are going to create a custom query in order to do it.

So:

1. Open a new report in Power BI
2. Click in Edit Queries
3. Click in New Source and choose Blank Query
4. Right click in your Query1 and choose Advanced Editor
5. Paste the code below:

This simple code will bring all the issues you are currently assigned to. (Remember to replace xxxx.atlassian.net for your actual domain)

let
    Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/search?jql=assignee=currentUser()")),
in
    Source

So you could start working with your data. But it isn't so simple... One query can retrieve only 100 records per time - so, maybe we need more iterations to get all the records, depending on how our query is formatted. The code below is a little more complex but takes care of it for us:

let

    // The same query as before
    Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/search?jql=assignee=currentUser()")),

    // Converting data from List to Table, so
    #"Converted to Table" = Record.ToTable(Source),

    // we will be able to transpose it
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),

    // and make the field names the column headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),

    // Now we are going to assign the correct data types for each column
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),

    // And keep only the total column
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}),

    // Now we are going to get the first (and only) record and assign it to a variable named #"total"
    #"total" = #"Removed Other Columns"{0}[total],

    // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
    #"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100),

    // Converting the startAt list into Table - not sure if this is really necessary
    #"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // and renaming its single column
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}),

    // Now let's create a new column URL with the URL we are interested at. Note the maxResults and startAt parameters this time
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each "https://xxxxxx.atlassian.net/rest/api/2/search?maxResults=100&jql=assignee=currentUser()&startAt=" & Text.From([startAt])),

    // And finally retrieve them all
    data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_)))
in
    data

I tried to leave all the lines commented in order to be somehow self explanatory - let me know if you need any further explanation on any of them.

If you want, you could set the query as a parameter in order to do not repeat the URL twice during the query processing, avoiding errors during the query change.

Please let me know if it was useful...

Tiago.

[Takieddine BELKADI]

Thank you very much.

I will read all the script and let you know if I have any comment.

Regards,

Takieddine

[Takieddine BELKADI]

Hi,

I found an error whe I try to execute my querry :

// Requête1
let

// The same query as before
Source = Json.Document(Web.Contents("https://gazeuropeen.atlassian.net/rest/api/2/search?jql=assignee=currentUser())),

// Converting data from List to Table, so
"Converted to Table" = Record.ToTable(Source),

-----------------------------The error is here !!

// we will be able to transpose it
"Transposed Table" = Table.Transpose(#"Converted to Table"),

// and make the field names the column headers
"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),

// Now we are going to assign the correct data types for each column
"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),

// And keep only the total column
"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}),

// Now we are going to get the first (and only) record and assign it to a variable named #"total"
"total" = #"Removed Other Columns"{0}[total],

// Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100),

// Converting the startAt list into Table - not sure if this is really necessary
"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// and renaming its single column
"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}),

// Now let's create a new column URL with the URL we are interested at. Note the maxResults and startAt parameters this time
"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each "https://gazeuropeen.atlassian.net/rest/api/2/search?maxResults=100&jql=assignee=currentUser()&startAt=" & Text.From([startAt])),

// And finally retrieve them all
data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_)))
in
data

I don't understand why there is an error at this point ?

best Regards,

[Tiago Machado]

Yes - I missed one " on the line before the error.

The correct one is:

Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/search?jql=assignee=currentUser()"))

I already edited the previous post with the fix

[Takieddine BELKADI]

Thank you :

I have now an other issue : 

{"expand", type text}, 

The column expand is not found ?

Regards,

[Tiago Machado]

Hi,

Do you mean on this line:

// Now we are going to assign the correct data types for each column
"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),

?

If so, "expand" is one of the values returned on the JSON of my JIRA instance. Maybe it has a different name or it isn't returned on yours - You could remove it if it isn't being used.

The most important value to get on this line is "total"

[Takieddine BELKADI]

Hi,

I removed the column expand.

I face an other issue : 

= Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}})

Column 1 not found ?

Regards,

[Tiago Machado]

Again, it is a difference between my environment and yours.

You must rename the "Column1" to the name of the column your Power Bi created. Just click on the "startAt list" step and you will find it.

[Takieddine BELKADI]

I tryed to remove the column1.

I have an other issue : 

= List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_)))

It said : we expect the value RenameOperations

I don't understand.

Regards,

[Takieddine BELKADI]

Sorry if i'm not enough familair with Power bI.

When I click on startAt List I have the following : 

= List.Generate(()=>0, each _ < #"total", each _ +100)

Regards,

[Takieddine BELKADI]

I want to say, I don't know where can i find the name of column1 in my power bi

Regards,

[Takieddine BELKADI]

When I click on StartAt List : I have ==> Liste.

I tryed to replace Column1 by "Liste"...I got the same error.

Power Bi coulnd find "Liste" !!

[Brian B_]

Hi Nick Cerneaz or anyone,

Tried numerous ways to get my query to pull more than 100 records. Currently it is limiting at 100-108 no matter what I do. Was trying to build out a hybrid query that included commands from the Content Pack and the custom one that you built. Any help is much appreciated. I know in my VS project I have at least 8000 records. Was hoping to report on anything in the VS project that is created from Sept 2017 onwards. Can't seem to pull more than 100-ish records. See attached.

Getting desperate after days of triage. 

Thanks,
Brian

let
VS_v1 = let
yourJiraInstance = "https://vesonjira.atlassian.net/rest/api/2/",

 

jql = "&jql=project=VS",

qryBase = "https://vesonjira.atlassian.net/rest/api/2/" & "search?maxResults=100" & jql & "&startAt=",



Source = Json.Document(Web.Contents(qryBase & "0")),
numIssues = Source[total],

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(_))),


iLL = List.Generate(
() => [i=-1, iL={} ],
each [i] < List.Count(data),
each [
i = [i]+1,
iL = data{i}[issues]
],
each [iL]
),

issues = List.Combine(iLL)

 

in
Source,
issues = VS_v1[issues],
#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table",....



And then 40-60 rows of columns I built out.

[Alicja Marcyniuk]

Hey, great solution, I wanted to ask if anyone know how to modify jql to get all data for specific period. Is it possible? 

[Aravin Rangasamy]

Hi @Nick Cerneaz Thank you for the detailed query.

Iam getting the following error :

Expression.Error: We cannot apply operator & to types Logical and Text.
Details:
Operator=&
Left=FALSE
Right=0

for this line in your query:

= Json.Document(Web.Contents( qryBase & "0" ))

[graemejohnson]

@Nick Cerneaz I realise this is going back a number of years, but the code snippet you put together back in 2018 towards the top of this thread has been a life saver.  I've used it to pull issue and sprint data (see example below) with great success.  

The problem I have is that the data source is treated by PowerBI as dyamic and therefore I cannot publish to a workspace and have the data refresh on a schedule. This is the same blocker encountered by @Ray Brosius and I'm not in the fortunate position of being able to purchase a JIRA/PowerBI connector plug in to work around this.

Am wondering if there's an approach that can be taken that means PowerBI does not treat this as a dynamic data source?

Thinking this post here might be a guide - but I've reached the limit of my coding capability to figure this one out :-(

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

let
// Set some constant values to be used when setting values for query columns
division = "A",
subdivision = "B",

// Set your Jira Cloud URL details - change 'yourDomain' as appropriate
yourJiraInstance = "https://yourDomain/rest/agile/1.0/board/",

// Set Board ID of Sprint Board
boardID = "1023",

// So lets construct the actual base query string to send to Jira
qryBase = yourJiraInstance & boardID & "/sprint?maxResults=50" & "&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 ...
numSprintsToRetrieve = 500,

// Now it is time to build a list of startAt values, starting on 0, incrementing 50 per item
startAtList = List.Generate(()=>0, each _ < numSprintsToRetrieve, each _ +50),
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 50 records
// long. The sprints will be more useful to us if they're consolidated into one long list
// 
// In essence we need extract the separate lists of sprints 
// and concatenate those into single list .. 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 sprints, 
// 50 in each except for the last one that will have just the residual list. 
// So sprintChunks is a List of Lists (of sprints): 
sprintChunks = List.Generate(
() => [i=-1, iL={} ],
each [i] < List.Count(data),
each [
i = [i]+1,
iL = data{i}[values]
],
each [iL]
),
#"Converted to Table1" = Table.FromList(sprintChunks, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table1", "Column1"),
#"Removed Blank Rows" = Table.SelectRows(#"Expanded Column1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Removed Blank Rows", "Column1", {"id", "state", "name", "startDate","endDate", "activatedDate","completeDate", "goal"}, {"Sprint ID", "State", "Name", "Sprint Start Date", "Sprint End Date","Sprint Activated Date","Sprint Complete Date", "Goal"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column2",{{"Sprint ID", Int64.Type}, {"State", type text}, {"Name", type text}, {"Goal", type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"State", Text.Proper, type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Capitalized Each Word",{{"Sprint Start Date", type datetimezone}, {"Sprint End Date", type datetimezone}, {"Sprint Activated Date", type datetimezone}, {"Sprint Complete Date", type datetimezone}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Sprint Start Date", type date}, {"Sprint End Date", type date}, {"Sprint Activated Date", type date}, {"Sprint Complete Date", type date}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Start Date", each if [State] = "Future" then [Sprint Start Date] else [Sprint Activated Date]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Start Date", type date}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type3", "End Date", each if [State] = "Closed" then [Sprint Complete Date] else [Sprint End Date]),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"End Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Sprint Start Date", "Sprint End Date", "Sprint Activated Date", "Sprint Complete Date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Sprint ID", "State", "Name", "Start Date", "End Date", "Goal"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Division", each division),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Sub-Division", each subdivision),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Division", "Sub-Division", "Sprint ID", "State", "Name", "Start Date", "End Date", "Goal"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Division", type text}, {"Sub-Division", type text}})
in
#"Changed Type5"

[Sruthi]

How to pull display name instead of user name in the assignee column?

[Martin_Butkiewicz]

Hello @Tiago Machado, hello all,

I try to fetch data from an on-premise JIRA Server with local client PowerBI Desktop

Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/search?jql=assignee=currentUser()"))

At your example,

taking the string "https://..." into the webbrowser I receive a result, displaying what I expect.

With PowerBI I get the http error 400

DataSource.Error: Web.Contents failed to get contents from 'https://jira.serverurl.sys/rest/api/2/search?jql=project=AAA' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://jira.serverurl.sys/rest/api/2/search
Url=https://jira.serverurl.sys/rest/api/2/search?jql=project=AAA

Used authentication method is Windows, alternative credentials with e-mail/password for login

JIRA v7.2.13

Please suggest, thanks in advance

Cheers, Martin

[Tiago Machado]

Hi @Martin_Butkiewicz 

This looks like a problem with the way Power BI tries to authenticate with your JIRA server.

You can try to figure out what your JIRA server is receiving by using a sniffer tool like Fiddler or any other you fell more comfortable with.

Compare the HTTP Headers of the packages the browser and Power BI are sending. The answer is probably there.

The final resolution will vary according the differences you found between one and another.

[Martin_Butkiewicz]

Good morning @Tiago Machado, at first thanks for your reply. Appreciation.

At this organization I need to involve the IT administration for further checks (decyrpt https traffic). Looking forward to it and will let you know the result.

Cheers, Martin  

[Ad Smeets]

I have used this example and it really helped me in getting the correct information in powerBI and understand how things work.  But this works for issues and the total amount of issues is given as result of the rest api call. 

Now I am struggeling with sprints and possible other objects in the future. I understand how to build the call, how the pagination works, and how to work with the data I retrieve. Problem is that this call does not return the total amount of sprints. Given back is if the page is the last page using the variable 'isLast'. Is there an example available how to loop through all pages and incorporate them in one list?

I was hoping to do this in the condition with the  list.generate option. Unfortunately my knowledge is to limited to solve this.

[Viktoria Krane]

Hey Ad, did you figure out how to download sprints data? I was able to follow the instructions so far, but I also need that sprints data. 

[Ad Smeets]

I solved it by using the data available in issues. So component's, sprints, releases etc. can be fount by querying for the information and expanding the coulumns in powerbi.

You will be missing unused object, but to me they where not relevant. 

Hope this helps.

[Tiago Machado]

Hi @Viktoria Krane , @Ad Smeets 

You can try to use these URL's to fetch the information you are looking for:

https://developer.atlassian.com/cloud/jira/software/rest/#api-group-Sprint

So, instead of using a JQL to get a collection of issues, you would use the URL's documented and have a collection of Sprints.

What information you need from each sprint and how to fetch them would vary from use case to use case.

Please, let us know if it has worked for you.

Thanks a lot.

[Dominic Giroux]

I'm struggling with retrieving the Sprint name from the issue data.  PowerBI reports that my "Sprints" column is a list, but I don't think it really is:  When I look at other lists, I get something like:

But on my "Sprints" column, I get something like:

com.atlassian.greenhopper.service.sprint.Sprint@1ada16ec[id=24,rapidViewId=87,state=FUTURE,name=Prioritized Backlog,startDate=<null>,endDate=<null>,completeDate=<null>,sequence=24,goal=<null>]

I was trying to extract the name as I do for other list but the 2nd step fails:

#"Expanded sprints" = Table.ExpandListColumn(#"Expanded sdls1", "Sprints"),
#"Expanded sprints1" = Table.ExpandRecordColumn(#"Expanded sprints", "Sprints", {"name"}, {"Sprint.name"}),

The error I get is:

Expression.Error: We cannot convert the value "com.atlassian.greenh..." to type Record.
Details:
Value=com.atlassian.greenhopper.service.sprint.Sprint@1ada16ec[id=24,rapidViewId=87,state=FUTURE,name=Prioritized Backlog,startDate=<null>,endDate=<null>,completeDate=<null>,sequence=24,goal=<null>]
Type=[Type]

Can anyone share how they extracted the Sprint info out of issues?

Thanks!

[Dominic Giroux]

So I looked at the raw output of the REST call and realized I was misreading what PowerBI was showing me.

Yes, Sprint is a "list" with only one value, and that value unfortunately is not JSON, like the other Lists, which is why that 2nd step is failing.  So I'll change my question:

Anyone knows how to parse this custom grasshopper string and extract its attributes, as a PowerBi query step?

[Shraddha Ukalkar]

Did you resolved the problem? I am having the same problem. Can you guide me?

[thomas.hargreaves]

I'm having the exact same problem. Any pointer in the right direction would be greatly appreciated.

[thomas.hargreaves]

@Shraddha Ukalkar

@Dominic Giroux

I found a solution. After you've retrieved the data and have the table with sprint "lists" which only contain one element you can use the "Split Column" function and then use the "," as the delimiter.

This will give you  columns with "name=Prioritized Backlog" values for example which still isn't very pretty but you can do the same thing again on these columns with the "=" as the delimiter.

Afterwards delete the excess columns and rename the ones you need. This can all be done using the UI buttons and it will add the query lines for you. The query isn't pretty but it works.

If anyone has another way then please let me know as well.


Edit: Forgot to mention that you have to expand the list first:

#"Expanded sprints" = Table.ExpandListColumn(#"Expanded components1", "customfield_10008"),

[Juliana Berossa Steffen]

@thomas.hargreaves you mentioned above to first expand the list but when I am trying to expand the custom field that contains the sprint list it says that cannot convert list to text. 

I notice my field is a list of records :(
I need some help

[James Zicrov]

Hello everyone I have some information related to the question asked here which is a blog on"How to import Rest API into Power BI" which might prove useful and so here's the link:

https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

[Ahmad Al-Abbadi]

Hi @Tiago Machado

Thanks for sharing this great experience..

I'm currently struggling using using PowerBI Content Pack, and need your help in editing the script in advanced query, to get all needed fields with the right data type and values.

Please have a look on this post.

Thanks in advance :)

[David Honig]

For me, the solution was change the pagination from 500 which was in bundle to 100. Then it loaded all records from API.

In Queries > GetIssues > Applied steps "Source" > Settings (gear) pageSize=100