Forum Discussion

Alina12's avatar
Alina12
Helper I
2 years ago
Solved

How to connect Jira REST API as data source? Power BI Service friendly and with pagination??

Hi to all I've spent hours and hours trying to load the Jira data into Power BI via its REST API. I have now managed to do it, but with the well-known problem of the error message of a dynamic data ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Alina12 ,

     

    Hope all is going well.

     

    Please follow these steps:

     

    1. The error you are encountering in the function is due to attempting to concatenate a binary () with a string. Since is used with , it is treated as a binary.

     

    You should modify your approach to use and options within for pagination as well.GetJiraIDCountBaseUrlBaseUrlWeb.ContentsRelativePathQueryWeb.Contents

     

    2. Modify the GetPage function:

    GetPage = (Index) =>
    let
        Skip = Text.From(Index * JiraIDPerPage),
        Top = Text.From(JiraIDPerPage),
        PageData = Web.Contents(
            https://XXX.XXX.net/jira/rest/api,
            [
                RelativePath="2/search",
                Query=
                [
                    jql="filter=525545",
                    startAt=Skip,
                    maxResults=Top,
                    fields="issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200"
                ],
                Headers=[Authorization="Bearer XXX"]
            ]
        ),
        Json = Json.Document(PageData),
        Value = Json[#"issues"]
    in Value,

     

    If you have other questions, please contact me at any time.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Alina12 ,

 

Hope all is going well.

 

Please follow these steps:

 

1. The error you are encountering in the function is due to attempting to concatenate a binary () with a string. Since is used with , it is treated as a binary.

 

You should modify your approach to use and options within for pagination as well.GetJiraIDCountBaseUrlBaseUrlWeb.ContentsRelativePathQueryWeb.Contents

 

2. Modify the GetPage function:

GetPage = (Index) =>
    let
        Skip = Text.From(Index * JiraIDPerPage),
        Top = Text.From(JiraIDPerPage),
        PageData = Web.Contents(
            https://XXX.XXX.net/jira/rest/api,
            [
                RelativePath="2/search",
                Query=
                [
                    jql="filter=525545",
                    startAt=Skip,
                    maxResults=Top,
                    fields="issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200"
                ],
                Headers=[Authorization="Bearer XXX"]
            ]
        ),
        Json = Json.Document(PageData),
        Value = Json[#"issues"]
    in Value,

 

If you have other questions, please contact me at any time.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Alina12's avatar
Alina12
Helper I
2 years ago

hi Anonymous ,

 

thanks a lot for your reply! 

I tried your recommendation and fortunately it works fine now in power bi desktop and I get all hits, but unfortunately I still get the error message with the dynamic data sources in Power BI Services. Do I have to use Web.Contents, Relative Path and Query everywhere instead of BaseUrl and not only in the PageData? Can the problem also be caused by the pagination?

My code so far:

 

let 
    BaseUrl = 
    Web.Contents(
        "https://XXX.XXX.net/jira/rest/api",
        [
            RelativePath="2/search",
            Query=
            [
                jql="filter=525545",
                maxResults="1000",
                fields="issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200"
            ],
            Headers=
            [
                Authorization="Bearer XXX"
            ]
        ]
        
    )  ,
 
    JiraIDPerPage = 1000,

    GetJson = (Url) =>
        let 
            RawData = BaseUrl,
            Json    = Json.Document(RawData)
        in  Json,

    GetJiraIDCount = () =>
        let Url   = BaseUrl,
            Json  = GetJson(Url),
            Count = Json[#"total"]
        in  Count,

    GetPage = (Index) =>
    let
        Skip = Text.From(Index * JiraIDPerPage),
        Top = Text.From(JiraIDPerPage),
        PageData = Web.Contents(
            "https://XXX.XXX.net/jira/rest/api",
            [
                RelativePath="2/search",
                Query=
                [
                    jql="filter=525545",
                    startAt=Skip,
                    maxResults=Top,
                    fields="issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200"
                ],
                Headers=[Authorization="Bearer XXX"]
            ]
        ),
        Json = Json.Document(PageData),
        Value = Json[#"issues"]
    in Value,

    JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),

    PageCount   = Number.RoundUp(JiraIDCount / JiraIDPerPage),

    PageIndices = { 0 .. PageCount - 1 },

    Pages       = List.Transform(PageIndices, each GetPage(_)),

    JiraID    = List.Union(Pages),

    Table       = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    ....