Is there a way to filter by Status\issue type using JQL Rest API and URL filtering in PowerBI?

[Mari]

I am trying to connect to JIRA using Power BI.  I connect and pull in data, but I would like to filter by Status and Issuetype using URL since there is a limit on # of records I can pull in at a time.

When I test what is coming out via Postman it appears the values for these field are a level down?  When I try issuetype=story it gets ignored.

This is what I was trying to use: https://mysite.com/rest/api/latest/search?jql=project=X&issuetype=story

Example json of what comes back using -  https://mysite.com/rest/api/latest/search?jql=project=X&fields=key,summary,status,issuetype&maxResults=100

"expand": "schema,names",
"startAt": 0,
"maxResults": 100,
"total": 50
"issues": [
{
"expand": "operations,versionedRepresentations,editmeta,changelog,renderedFields",
 "id": "149856",
 "self": "https://mysite.com/rest/api/latest/issue/1498506",
"key": "X-12636",
"fields": {

"summary": "Create Prototype",

"issuetype": {

"self": "https://mysite.com/rest/api/2/issuetype/5",
"id": "5",
"description": "The sub-task of the issue",
"name": "Sub-task",
"subtask": true,
},

"status": {
"self": "https://mysite.com/rest/api/2/status/10001",
"description": "",
"name": "Done",
"id": "10001",
"statusCategory": {
"self": "https://mysite/rest/api/2/statuscategory/3",
"id": 3,
"key": "done",
"colorName": "green",
"name": "Done"

[Bill Sheboy · Accepted solution]

Hi @Mari 

Looking at the JQL in your REST API calls, you appear to be using an ampersand symbol rather than the AND keyword. 

Please try creating and validating your desired queries with advanced filter search, and then you can grab the valid JQL from the browser URL to use in the search call.

Kind regards,
Bill

[Mari]

Thanks Bill

I have been playing around with this approach but unfortunately the url will only work if I sign in with Username and Password and I am trying to use solely a Bearer token in Power BI. 

[Mari]

Figured it out. It was a combo of your suggestion and my original URL.  I had to just adjust the jql portion bolded but the others ampersands could remain.

https://mysite.com/rest/api/latest/search?jql=project=ProjectX%20AND%20status=10000%20AND%20issuetype=Story&fields=projectkey,key,summary,issuetype,status&maxResults=-1

[Mari]

I also found with my original approach, that it allows me to filter by either issuetype OR Project but not both at the same time so example:

I can use https://mysite.com/rest/api/latest/search?jql=project=ProjectX&fields=id,summary

OR https://mysite.com/rest/api/latest/search?jql=issuetype=Story&fields=id,summary

but I can't use https://mysite.com/rest/api/latest/search?jql=project=ProjectX&issuetype=Story&fields=id,summary

IS there a way to filter by both or is this restricted? I am in an environment with several projects and issues so would like to filter out as much as possible before pulling in the data