How to connect Power BI to JIRA using Rest API and without third party app

[Vinod Kotiya]

Hi All

I really don't know why this basic feature is not provided by JIRA. Seems more focus on selling third party apps. Tried searching alot for solution but solutions are flooded with to use particular third party app for connectivity with Power BI.

Finally found a way out from this curl snippest:

Using CURL

echo -n EMAIL:API_TOKEN | base64

curl -D- \
-X GET \
-H "Authorization: Basic TOKEN" \
-H "Content-Type: application/json" \
"https://DOMAIN.atlassian.net/rest/api/2/issue/ISSUE"

In power BI > Open Data > Web

I used https://www.base64encode.org/ to get base64 encode string from

myemail@company.com:API_Token

Add two headers as shown.

In Authorization enter Basic [Space] your base 64 token

Click ok and you will be connected successfully.

[Rami Al Halabi]

This is the only correct answer on the whole WWW. You are a lifesaver.

[Vinod Kotiya]

Glad it helped you

[Andrea Gonzalez Mercado]

Hi, in this case I´n trying to do it exactly like you describe above, but I can't find the "Authorization" option.

[Nicoly De Lima Anjos]

...

[Harsh]

Hi @Vinod Kotiya 

I am trying to connect JIRA to Power BI for reporting purpose. But I am unable to get the the basic authentication token, can you help me with this. How do I generate the API token and the connect to POWER PI. 

I also want to restrict the data fields for a specific project too.

This is the closest and detailed explanation thread I found, but still since I am new in integration, any help would be appreciated,

Thank you

[Rami Al Halabi]

1.  Generate an API token from here: https://id.atlassian.com/manage-profile/security/api-tokens
2. Go to https://www.base64encode.org/ and encode the following string to base 64: your_email@your_company.com:Jira_API_Token
3. Create a Web Query in Power BI. The connection method has to be Anonymous
4. When creating the query add two headers and their values:
   1. Key: Authorization
      1. Value: Basic <Base64_String_From_Step_2>
   2. Key: Content-Type
      1. Value: text/csv or application/json depending on your URL and what you are trying to get. I use CSV so I don't have to expand a JSON file100 times.
5. You'll notice that I added "&tempMax=1000&pager/start=0" to get only 1000 records as the API is limited to 1000. I appended several source queries to get 5000 records. Do not use parameters in your URL if you want this query to refresh automatically. If you do, you will get an error in Power BI Service that refresh is not supported.

Your source m query should look something like this:

Source1 = Csv.Document(Web.Contents("https://YOUR_COMPANY.atlassian.net/sr/jira.issueviews:searchrequest-csv-all-fields/temp/SearchRequest.csv?jqlQuery=project+%3D+%22<PROJECT_SHORT_CODE_HERE>%22+ORDER+BY+created+DESC&atl_token=XXXXXX_lin&tempMax=1000&pager/start=0", [Headers=[Authorization="Basic <Base64_API_TOKEN_STRING>", #"Content-Type"="text/csv"]]),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),

#"Promoted Headers1" = Table.PromoteHeaders(Source1, [PromoteAllScalars=true]),

Again, I chose to export a CSV, but you may export a JSON by using the same URL you use to get the JSON file in your browser.

[Harsh]

just a follow up question - is base64encode allowed in companies. Since we are integrating a third party tool. Data security is the main factor. 

Just confirming in that case.

[Rami Al Halabi]

No idea really. You are just using it to generate a string and then you won't use it again. You can use any other base64 encoding tool I guess.

[Harsh]

Ok. Thank you. Will check if this works. 

Thanks again. 

[Harsh]

Hi @Rami Al Halabi 

I have another question, lets assume if we do not perform a basic authentication and just want to get the Jira data to PowerBI, so I wondering that in that case what will be the approach.

Also do we have restriction on how many records we can pull and how to pull only the fields of one project.

Thanks for the help and your support.

[Rami Al Halabi]

Hello, in my experience, Basic Authentication did not work. That is why the steps above use an Anonymous connection with an Authorization header that has your generated API token. This is what is authenticating you.

I think there is a 1,000-record restriction on API calls. If you need more records you have to do multiple calls, but feel free to experiment with that. My Jira project does not have 1000 records yet.

[Missy Lankard]

Hi! How did you confirm your domain in the link used? and is your url pointing to a filter, or just the project space? thanks!

[Rami Al Halabi]

For the URL I used, I went to the Issues screen, I filtered the project I want to see > Export Issues Menu > Left-Click on Export CSV (all fields) > Copy Link Address.

Now that I think about it, I'm not using an API call in reality, so the 1000 items limit might or might not be in effect. You'll have to play around with it.

I added "&pager/start=0" to the end of the URL to tell it where to start in case it is returning more than 1000 records.

[Mario Lazaros]

Oh man, I feel your pain with JIRA! I've been in that same boat before, but your solution seems pretty solid. And it's great that you found a workaround without having to rely on those third-party apps. They can be such a pain sometimes, right? Funny story, I actually had a similar issue when I was working on a project after taking a Power BI training course to get certified as a data analyst. I was super excited to apply my new skills, but integrating JIRA and Power BI turned out to be quite the challenge. I ended up having to do a deep dive into forums just like you did. The struggle is real, haha! Anyway, glad you got it sorted out! Your solution could be a lifesaver for someone else in the same situation. Keep up the good work! 😄👍

[Vinod Kotiya]

Thanks Mario