Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Too much data? (Consuming the JIRA ChangeLogs)

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.

Need Help
Tutorial Requests

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Anonymous,

        Could you check that REST is enabled for your JIRA? 


        Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous, 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's avatar
        Anonymous
        Not applicable
        7 years ago

        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