How to Retrieve Data in Power BI from Jira With Custom Search Criteria

Integrating Jira with Power BI empowers teams to analyze and visualize project data far beyond Jira’s built-in reports. Whether you’re managing sprints, tracking issue flow, or reporting SLAs, custom JQL (Jira Query Language) filters in Power BI make it easy to tailor data extraction.

In this updated guide, I’ll walk you through retrieving all Jira tickets based on specific search criteria, using Power BI’s Web connector and Jira’s REST API.

---

🔍 Scenario: Retrieve All Jira Tickets by Specific Search Criteria

Let’s say you want to get:

• All issues from project ABC
• Created in the last 90 days
• That are assigned to a specific team or user
• With selected fields (e.g., summary, status, created, assignee, priority)

We’ll do this using JQL with Power BI.

---

🛠 Step-by-Step: Connecting Power BI to Jira With Custom Search

✅ Step 1: Build Your JQL Query

JQL Example:

jqlCopyEditproject = ABC AND created >= -90d AND assignee = currentUser() ORDER BY created DESC

You can test your JQL in Jira’s advanced issue search to confirm results.

---

🔗 Step 2: Construct the API URL

REST API Format:

urlCopyEdithttps://yourdomain.atlassian.net/rest/api/3/search?jql=YOUR_QUERY&maxResults=100

For example:

urlCopyEdithttps://yourdomain.atlassian.net/rest/api/3/search?jql=project=ABC%20AND%20created>=-90d%20AND%20assignee=currentUser()%20ORDER%20BY%20created%20DESC&maxResults=100

🔄 Note: The API defaults to returning only 50 results. Use maxResults=100 (maximum allowed per request), and implement pagination for full data.

---

🔐 Step 3: Get Your API Token (For Jira Cloud)

1. Go to https://id.atlassian.com/manage/api-tokens
2. Generate a new token
3. Copy it and store securely

---

📈 Step 4: Open Power BI Desktop and Connect to Web

1. Open Power BI Desktop
2. Click Home > Get Data > Web
3. Choose Advanced and configure:
   • URL parts: Paste the full API URL
   • Or use Basic mode if preferred
4. When prompted for authentication:
   • Choose Basic
   • Username: Your Jira email
   • Password: Your API token

---

🧹 Step 5: Parse and Transform the JSON Response

Power BI will retrieve a nested JSON object like:

jsonCopyEdit{
  "startAt": 0,
  "maxResults": 100,
  "total": 350,
  "issues": [...]
}

In the Power Query Editor:

1. Expand the issues list
2. Drill into fields > Select the relevant fields (summary, status, assignee, etc.)
3. Convert nested records to flat table
4. Rename columns and format as needed

---

🔁 Step 6: Handle Pagination (Optional But Recommended)

To get more than 100 issues:

• Loop through pages using startAt parameter
• Example:

urlCopyEdithttps://yourdomain.atlassian.net/rest/api/3/search?jql=project=ABC&startAt=100&maxResults=100

You can automate this in Power Query using a function + list generator.

Here’s a basic example of how to create a paginated function in M:

powerqueryCopyEditlet
    GetJiraPage = (startAt as number) =>
        let
            Source = Json.Document(
                Web.Contents("https://yourdomain.atlassian.net/rest/api/3/search", [
                    Headers=[Authorization="Basic YOUR_BASE64_CREDENTIALS"],
                    Query=[
                        jql = "project=ABC AND created >= -90d",
                        startAt = Text.From(startAt),
                        maxResults = "100"
                    ]
                ])
            ),
            Issues = Source[issues]
        in
            Issues
in
    GetJiraPage

Then, generate a list like:

powerqueryCopyEditList.Generate(() => 0, each _ < 400, each _ + 100)

---

📌 Final Tip: Parameterize Your Queries

You can create Power BI parameters for:

• Project name
• Assignee email
• Date range

This makes your report dynamic and reusable for different teams or projects.

---

✅ Summary

Here’s a quick recap of how to retrieve custom Jira data in Power BI:

Step	Action
1	Write your JQL
2	Construct Jira REST API URL
3	Authenticate with API token
4	Connect via Power BI Web connector
5	Parse the JSON and expand fields
6	Use pagination to get full data