Too much data? (Consuming the JIRA ChangeLogs)

[BeeEyeNoob]

I have been successful in using the JIRA PBI Desktop PBIT published by this community - yet I also seek the changelog data for each issue.

-Referencing the JIRA REST API for the changelog  I am able to see the data I need in a web browser .

-So I created a new function: 

let
    SearchIssue = () =>
    let 
        Source = FetchPages("", 500),
        table = Table.ExpandRecordColumn(Source, "Column1", {"id"}, {"id"}),
        Search = (id) => 
        let
            Issue = Json.Document(Web.Contents(URL & "/rest/api/2/issue/" & id & "?expand=changelog"))
        in
            Issue,
        Output = Table.AddColumn(table, "incidencia",each Search([id])) 
    in
        Output  
in
    SearchIssue

-The query takes over 24 hours to run with over 200,000 rows - but eventually I get "OLE DB or ODBC error" as I belive I am requesting too much data at once from JIRA and they kill the connection. 

-After the error, I am able to see the success of the table and rows it DOES get in the "Edit Queries" page, but once I hit "close and apply" PBI attempts to reload the data again, I get a similar error, and no new table 😞 

-I tried to analyze the original query in the JIRA content pack in an effort to resolve my issue:

//Query Name: GenerateByPage
(getNextPage as function, optional tableType as type) as table =>
    let
        listOfPages = List.Generate(
            () => getNextPage(null),
            (lastPage) => lastPage <> null,
            (lastPage) => getNextPage(lastPage)
            ),
        tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
        firstRow = tableOfPages{0}?,
        keys = if tableType = null then Table.ColumnNames(firstRow[Column1])
               else Record.FieldNames(Type.RecordFields(Type.TableRow(tableType))),
        appliedType = if tableType = null then Value.Type(firstRow[Column1]) else tableType
    in
        if tableType = null and firstRow = null then
            Table.FromRows({})
        else
        Value.ReplaceType(Table.ExpandTableColumn(tableOfPages, "Column1", keys), appliedType)

-I have yet to get a firm enough grasp on M to be able to see what im doing wrong.

Your help would be much appreciated.

[Anonymous]

Hi @BeeEyeNoob, The below does not load because of the following reasons:

1. A field e.g., status when it undergoes a standard lifecycle i.e., to do, in progress, testing and done it creates 4 unique entries for a user story

2. the above is only for a field imagine a scenario for every field and also for every custom field introduced within JIRA.

How to resolve this:

1. When you expand, choose only those fields you need the history for initially, e.g., sprint or status

2. Once the above is done, use the group by option to group by key and list all fields as 'table'

3. Then use the custom column and use the syntax of Table.Column ([column name], "the field you want to expand"

4. Then use the list expand option to list it as a value by using a delimiter

5. Remove duplicates and load

Note: The load will take some time but will definitely work and yes, I used your M query and it worked like a charm 🙂

Hope this helps and considering that this is not resolved for you.

Regards,

Anand

[Anonymous · Accepted solution]

Hi all,

I modified the queries to avoid the lead time to generate information following the steps mentioned in the URL below and it works like a charm. Hope this helps who are looking for solution to retrieve data from JIRA.

Kind regards,

A!

https://stackoverflow.com/questions/46904641/play-with-paginated-data-with-power-bi

[Anonymous]

Hello @Anonymous ,

Thanks for sharing the link and your solution. I am also trying to read changelogs from JIRA like you did. But I am not able to follow the link and implement those steps for the M query you wrote above:

let
    SearchIssue = () =>
    let 
        Source = FetchPages("", 500),
        table = Table.ExpandRecordColumn(Source, "Column1", {"id"}, {"id"}),
        Search = (id) => 
        let
            Issue = Json.Document(Web.Contents(URL & "/rest/api/2/issue/" & id & "?expand=changelog"))
        in
            Issue,
        Output = Table.AddColumn(table, "incidencia",each Search([id])) 
    in
        Output  
in
    SearchIssue

Can you please help with some pointers on how you achieved it? Really appreciate your help in this regard.

Regards,

Anuj

[v-yuezhe-msft]

@BeeEyeNoob,

Could you please post the full error message that you get? Also please take a look at the following similar thread about pagination using M function.

http://community.powerbi.com/t5/Desktop/how-to-create-a-query-that-paginates/td-p/20047

Regards,
Lydia

[BeeEyeNoob]

Thank you for your response, while I am reading over the link you posted, here is my most current error:

OLE DB or ODBC error: [DataSource.Error] Web.Contents failed to get contents from 'https://mycompany.atlassian.net/rest/api/2/issue/33438/changelog?maxResults=500&startAt=0' (404): Not Found.

When entering this URL in a browser, the json data is there and should be readable - so I'm not sure what is going on.

Thanks again!

[v-yuezhe-msft]

@BeeEyeNoob,

Could you check that REST is enabled for your JIRA? 

Regards,

[BeeEyeNoob]

REST is indeed enabled as I am succussfully able to use the PBI JIRA content pack as well as access the urls in a browser.

[v-yuezhe-msft]

@BeeEyeNoob,

Please check the JIRA URL as suggested in the following similar thread.

https://community.atlassian.com/t5/Jira-questions/Does-anyone-know-why-I-get-the-the-error-404-not-f...

Regards,
Lydia