Extract from JIRA to Power BI (using Power Query)

[@chadbonadonna3778]

Very excited to try this, thanks for the thorough video!

[@introverted.manager]

Thanks for watching! 😊

[@shashankkm1507]

Thank you for uploading this video. It works as expected with manual refresh. But when we try to schedule the refresh we get error: " You can't schedule refresh for this semantic model because the following data sources currently don't support refresh: Data source for Query1" this is due to dynamic data source. So, I am having difficulties while adding relativepaths within M query. Could you please suggest on this?

[@introverted.manager]

Hi, this is indeed the case - there are lots of limitations imposed on refreshes in the service. There are certain workaround, let me have a look whenever I get time.

[@josephvelasquez8550]

I loveu :,v this help me a lot to share Ticket status :,v

[@introverted.manager]

I'm glad!

[@antoniomori3652]

whats the difference btween using the power bi conector for jira extension instead ?

[@introverted.manager]

M approach is for cases when you’re unable to add additional extensions or cannot query DB (for on-prem) but still want to collect data, API on the other hand is *usually* freely accessible

[@shibaj1401]

Thank you for the video. Very insightful. I was able to connect successfully connect successfully to Jira, however, I have found that many of the render fields, which are populated in the Jira interface, remain unpopulated in the Power Bi import. Do you have any guess as to why this may be? Thanks again.

[@introverted.manager]

Hi, Hasn’t occurred to me ever, unless those are custom fields - those might require more tailored approach to api calls.

[@MeghaShree-ff3om]

Is there a step missing after I generate the API token, where do I use it? I am getting the error (400): Bad Request

[@introverted.manager]

You have to add token in api key parameter within Power BI in Power Query

[@p337awesome]

Were you able to fix this? I'm having the same issue. I don't get a request from PBI to provide my API token when I run the script and I'm struggling to include it in the header manually.

[@RichardTeelSys]

@@p337awesome I ran into this same issue and was able to resolve it. Most likely you have already connected to the data source and supplied the API Key in the Header tag or some other method. (At least that was what I had done in my case.) On the Home Ribbon, click "Data source settings", then edit the data source that you are connecting to using Basic, username and API key as the password, just as shown in the video at 13:38 mark. You may need to select "Global permissions" option in the "Data source settings" dialog if you do not see your data source. Once I did that, everything worked. Hope this helps in your case.

[@p337awesome]

@@RichardTeelSys thank you Richard! I managed to include the API token in the header in the meantime - it was just like you explained 👍🏻

[@dvd23m]

Hello, first of all thanks for this video, it is awesome!! I have a question for you. In case than we have issues with more than 20 worklogs inside but the field maxResult is 20, is there a way to get all this work logs?? Me and a lot of people are struggling with this but we can't to find a solution. Thank you for advance!

[@introverted.manager]

It supposed to get all worklogs. Is it not working for you?

[@hyenatron]

So I am trying to pull data from multiple projects together, as I am trying to get information for a team that has multiple locations, each with their own project. Is it possible to change out the project part of this code for a filter instead?

[@introverted.manager]

You can specify multiple project via , if needed. If that isn’t sufficient - yes, you can change it to any other JQL.

[@VietQuanT]

Hi, how can I include the created dates in the BaseUrl? For example from 4/1/2024 to 5/1/2024? Thank you in advance!

[@introverted.manager]

Hi! You can add this string into baseUrl: and created >="yyyy/MM/dd" AND created

[@jozefientachtig8386]

Is this code published so I can make a copy? Do I need a connector for Jira to Power BI or vice versa?

[@introverted.manager]

I published files for previous videos on my website - you can find it here www.maxanatsko.com/blog (it’s behind password, received in email).

[@snehapillay5712]

Will it be refreshed automatically when we publish this report to power bi service

[@introverted.manager]

For this please watch my newer video - “Fixing Dynamic Source refresh error”. It specifically addresses refresh in Power BI Service. kzbin.info/www/bejne/nHynoqxmiN2Ngrcsi=ZgBbykZVYlz743T9

[@snehapillay5712]

@@introverted.manager thanks a ton you are awesome

[@snehapillay5712]

Unfortunately I couldn't figure out how to make that work.. missing something in between it would be helpful if you can share pbix file and if you are ok with it

[@introverted.manager]

PBIX is shared on my blog: www.maxanatsko.com/blog

[@stretch91]

Awesome video, thank you! When publishing to the PowerBI Web Service, it doesn't recognise the data source as valid. Do you have a workaround for this? I've found some online, but none of them seem to work.

[@introverted.manager]

Could you share the exact error you’re getting?

[@dizbik]

Hello, I have tried the above with our JIRA instance and anytime I publish the version I get an error message Some data sources may not be listed because of hand-authored queries. Based on that it is not possible to schedule an automated refresh. Do you have any pointers or directions that can help?

[@introverted.manager]

I would have to agree with Inna’s comment. In its current state this M script isn’t production ready, as Power BI service applies a lot of limitations to dynamic scripts. Consider this video as an educational about M capabilities and working with APIs, applicable for ad-hoc analysis. If you have opportunity to use custom connector, go with the connector - that would be easier.

[@dizbik]

@@introverted.manager I found a solution to the problem with publishing. Modify the query based on the article "Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code" by Chris Webb and article "Dynamic Web.Contents() and Power BI Refresh Errors" by Stacia Varga

[@introverted.manager]

Yes, I made video about this recently too - Fix 'Dynamic Data Source' Refresh Errors in Power BI Service with RelativePath kzbin.info/www/bejne/nHynoqxmiN2Ngrc

[@seHmo]

not mater what kind of authentication I try always got the same error Expression.Error: Access to the resource is forbidden. Im using the Datacenter version

[@introverted.manager]

Could that be that your admins disabled api access? Have you tried accessing api outside power bi?

[@salevski88]

Hod did you extract the date from Jira and import to power Bi that part is not much clear to me because u have no no knowledge of Bi, so minute 1:57 not much clear to me, tnx!

[@introverted.manager]

You can download source file (in the video description) and open it in Power BI Desktop to review. Make sure to change api key and URL in the parameters (click Transform data to get there).

[@samahkahina9269]

@@introverted.manager where we must change the API KEY please ?

[@introverted.manager]

There is parameter called API Key, in Transform Data (power query)

[@p337awesome]

@@introverted.manager I just downloaded the files but there is no such parameter. I'm currently struggle with the authentification as PowerBI is not asking me for any auth info, when I run the script.

[@dincerguner656]

Hi Can I use this query for also Excel ? Thanks

[@introverted.manager]

PowerQuery is the same, I believe. So yes, you should be able to

[@josueserrano6541]

Were you able to use it for Excel? I'm trying to do the same thing. Having to use the jira addin in different tabs is tedious 🫠

[@dincerguner656]

@@josueserrano6541 Yes I could use it with little modifications