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?
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.
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
Hi and thank you very much for your video, I have watched it a few times now but I don't understand how to incorporate a specific JQL in the "// Construct the base URL for JIRA API call" part. for example, i have a JQL that in Jira looks like this "project in (P1, P2, P3, P4) and Labels in (Group-A, Group-B, Group-C)" how could I call for the same in JIRA API call ?
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.
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.
@@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.
For this please watch my newer video - “Fixing Dynamic Source refresh error”. It specifically addresses refresh in Power BI Service. ruclips.net/video/fKDqy0SxZLU/видео.htmlsi=ZgBbykZVYlz743T9
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
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?
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.
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
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?
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.
@@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
Yes, I made video about this recently too - Fix 'Dynamic Data Source' Refresh Errors in Power BI Service with RelativePath ruclips.net/video/fKDqy0SxZLU/видео.html
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!
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).
@@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.
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!
@@hashiniwickramasinghe735 Hi ! Although my solution isn't the most eficient I've solved it using a recursive function. I hope this help you. obtainingWorklog = (code as text) => let apiUrl = Your link, relativePath = "/rest/api/2/issue/" & code & "/worklog", response = Json.Document(Web.Contents(apiUrl, [RelativePath = relativePath])) in response, [...] Worklogs = Table.AddColumn(""Yourstep"", "Worklog", each obtainingWorklog([key]), type any), ** key is the name of my field, if you have id_issue, then put this For each key I get all the worklogs.
Underrated content. Truly enjoyed it. 💞
Thank you 🙌
Hi @introverted.manager , there's a way to retrieve custom fields of the issues? for example story point estimate, resolution, status ? thanks again
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?
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.
whats the difference btween using the power bi conector for jira extension instead
?
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
Hi and thank you very much for your video, I have watched it a few times now but I don't understand how to incorporate a specific JQL in the "// Construct the base URL for JIRA API call" part.
for example, i have a JQL that in Jira looks like this "project in (P1, P2, P3, P4) and Labels in (Group-A, Group-B, Group-C)" how could I call for the same in JIRA API call ?
Very excited to try this, thanks for the thorough video!
Thanks for watching! 😊
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.
Hi,
Hasn’t occurred to me ever, unless those are custom fields - those might require more tailored approach to api calls.
I have the same problem, even fields like Issuetype are returning null
Is there a step missing after I generate the API token, where do I use it? I am getting the error (400): Bad Request
You have to add token in api key parameter within Power BI in Power Query
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.
@@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.
@@RichardTeelSys thank you Richard! I managed to include the API token in the header in the meantime - it was just like you explained 👍🏻
Will it be refreshed automatically when we publish this report to power bi service
For this please watch my newer video - “Fixing Dynamic Source refresh error”. It specifically addresses refresh in Power BI Service.
ruclips.net/video/fKDqy0SxZLU/видео.htmlsi=ZgBbykZVYlz743T9
@@introverted.manager thanks a ton you are awesome
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
PBIX is shared on my blog: www.maxanatsko.com/blog
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?
You can specify multiple project via , if needed.
If that isn’t sufficient - yes, you can change it to any other JQL.
Is this code published so I can make a copy? Do I need a connector for Jira to Power BI or vice versa?
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).
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.
Could you share the exact error you’re getting?
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!
Hi!
You can add this string into baseUrl:
and created >="yyyy/MM/dd" AND created
Hi Can I use this query for also Excel ? Thanks
PowerQuery is the same, I believe. So yes, you should be able to
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 🫠
@@XLForAnyone Yes I could use it with little modifications
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
Could that be that your admins disabled api access?
Have you tried accessing api outside power bi?
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?
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.
@@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
Yes, I made video about this recently too - Fix 'Dynamic Data Source' Refresh Errors in Power BI Service with RelativePath
ruclips.net/video/fKDqy0SxZLU/видео.html
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!
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).
@@introverted.manager where we must change the API KEY please ?
There is parameter called API Key, in Transform Data (power query)
@@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.
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!
It supposed to get all worklogs. Is it not working for you?
Hi, I also have the same issue of getting only 20 worklogs per one Issue. Were you able to find any solution for that?
@@hashiniwickramasinghe735
Hi ! Although my solution isn't the most eficient I've solved it using a recursive function.
I hope this help you.
obtainingWorklog = (code as text) =>
let
apiUrl = Your link,
relativePath = "/rest/api/2/issue/" & code & "/worklog",
response = Json.Document(Web.Contents(apiUrl, [RelativePath = relativePath]))
in
response,
[...]
Worklogs = Table.AddColumn(""Yourstep"", "Worklog", each obtainingWorklog([key]), type any),
** key is the name of my field, if you have id_issue, then put this
For each key I get all the worklogs.
Thank you. The constant zoomin out is very annoying, but great content!
Thank you for the feedback!
Make sure to check out video about RelativePath, which continues this one.
I loveu :,v this help me a lot to share Ticket status :,v
I'm glad!
Thank you for the effort but can you make another video explaining in simple way for non developer people!!