Power BI only pulling in 1st entry from a JIRA issue and all entries needed

[mattholland1985]

Hello, I have never posted on anything like this before so not sure if the below makes sense or not?

I am hoping that someone can help me with a query or way of extracting a field from JIRA that has multiple entries. Power BI is only pulling in the 1st entry.

The field from JIRA that I am looking to extract is the market field (above) which I can do , but Power BI (below) only seems to be retrieving the 1st entry from the JIRA field.

Is there a query or way of extracting both entries from this field? I have looked at extracting a .CSV but the field is split out over up to 8 columns sometimes if there are multiple market entries?

If any other information is needed please let me know.

[PiEye]

Hi Matt, how are you connecting to JIRA? via API?

Pi

[PiEye]

Interesting, I've used PowerBI's native JSON functionality and dont have the same issue for the data that I'm looking at. My first step is:-

Source = Json.Document(Web.Contents("https://jira.atlassian.com/rest/api/latest/search"))

Let me see if I can recreate this

Pi

[PiEye]

Yes, so unfortunately I can't use FetchPages as I dont have that package at home

I can, however expand using JSON. When there are multiple entries in one field, there's an expansion icon which will give you the option to expand into new rows.

Click to expand it once, then it gives you the options of which sub-fields you wish to bring in

The other thing that I am wondering, is if JIRA has more than one field for market, sometimes when people set it up there might be a custom field for market1, market2 ... etc. 

Let me know if any of this works! I would also like to bring our JIRA data in and not sure whether to go with the custom content pack from Atlassian, or to go it alone.

https://www.atlassian.com/blog/add-ons/jira-content-pack-for-microsoft-power-bi

Pi

[mattholland1985]

Hi Pi, Thank you for the response again.

I have used the code that you sent for JSON and it has pulled in information, I have expanded the field for market to new rows and get the below and it still seems to only pull in 1 item and not all items entered. This column is a custom field (I am not sure if that makes any difference?). 

If I extract the values instead of expand to a new row I get the error message below, again not sure if it is because the field is a custom field.

[PiEye]

Hi Matt. 

Are you able to enter the record into a browser and look at the raw result JSON? What does it look like around custom field 11001?

I'm just trying to figure out how PBI is parsing this.

Pi

[mattholland1985]

Hi Pi, Thank you for your continued support. The extract below seems to show that custom field 11001 splits the values out into "custom field option" with unique numbers assigned to them? I have highlighted as best I can below. The red boxes are the markets that should be pulled in. The single box is what is being pulled in. 

[mattholland1985]

Hi Pi,

Yes the connection to JIRA is via API. If there is any other information please let me know.

[mattholland1985]

Please also see below picture to the custom field / market code currently

[v-kkf-msft]

Hi @mattholland1985 ,

Not familiar with JIRA, I looked up some documentation, but I still haven't found how to achieve what is presented in the image. Could you please share some relevant documentation?

Best Regards,
Winniz