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.

All steps are in this article https://www.linkedin.com/pulse/how-connect-power-bi-jira-using-rest-api-without-apps-vinod-kotiya-e5z7c/?trackingId=IIO74wGrYdL6NVabqaluPA%3D%3D

[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.

[Daniel French]

^^^^ This is finally what worked for me to get the filter results that I wanted into PowerBI. Thanks, Rami!

[Gustavo Oliveira]

Hello! I know this forum is a bit old, but I can still say that it is the only one that worked for this direct connection from PowerBI to Jira.

But has anyone managed to pull only the columns (i.e., the fields) of the project they are looking for?

I made the connection as mentioned by @Vinod Kotiya and @Rami Al Halabi, but the return was more than 600 columns, because it came with fields from all projects.

Has anyone managed to return only the fields of the project that the JQL filter is on?

Hugs and thanks in advance.

[Vinod Kotiya]

Hi its quite simple. Just type "search?JQL=project=xyz&fields=duedate,assignee

This will return only limited columns.

I will write a detailed tutorial on this and publish here.

[Vinod Kotiya]

All steps are in this article https://www.linkedin.com/pulse/how-connect-power-bi-jira-using-rest-api-without-apps-vinod-kotiya-e5z7c/?trackingId=IIO74wGrYdL6NVabqaluPA%3D%3D

[Rami Al Halabi]

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

[Vinod Kotiya]

Glad it helped you

[AnyUser]

Hello.

First of all, thanks and congratulations for this article. It was so helpful and, thanks to that, I could connect my report.

The only issue I am having is that I had to use a gateway to be able to do it, but actually it should not be needed, because Atlassian allows external connections. 

When I try to connect without the gateway, I receive an authentication issue in the PowerBI webpage telling that The credentials provided for the Web source are invalid. I need some help, because with empy credentials in the gateway it works, but the same empty credentials in the configuration of the semantic model give error without the gateway.

[Rachael Ekstrom]

Hi, 

Can someone answer the question above posed by Andrea? 

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

[Vinod Kotiya]

You have to add headers manually by typing in Advance tab.

[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.

[Vinod Kotiya]

Why you are looking in drop dowm. You have to add headers manually by typing it. 

[Ravi Nayinala]

I am repeating the same steps but there is an error saying "We couldn't authenticate with the credentials provided. Please try again".

I did select Anonymous.

And also my URL is slightly different as it does not have .atlassian.net in it.

Thank you for the help.