Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Alina12
Helper I
Helper I

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 source in Power BI Service. Here my working code so far:

 

    BaseUrl = "https://XXX.XXX.net/jira/rest/api/2/search?jql=filter=525545&maxResults=1000&fields=issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200",
  
    JiraIDPerPage = 1000,

    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url,[Headers=[Authorization="Bearer XXX"]]),
            Json    = Json.Document(RawData)
        in  Json,

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

    GetPage = (Index) =>
        let Skip  = "&startAt=" & Text.From(Index * JiraIDPerPage),
            Top   = "&maxResults=" & Text.From(JiraIDPerPage),
            Url   = BaseUrl & Skip & Top,
            Json  = GetJson(Url),
            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),

...

 

 

Now that Power BI Desktop is working, I need to address the dynamic data source issue and I kept coming across this post while browsing the community:
https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i... 
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power... 


My modified 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 = Web.Contents(Url,[Headers=[Authorization="Bearer XXX"]]),
            Json    = Json.Document(RawData)
        in  Json,

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

    GetPage = (Index) =>
        let Skip  = "&startAt=" & Text.From(Index * JiraIDPerPage),
            Top   = "&maxResults=" & Text.From(JiraIDPerPage),
            Url   = BaseUrl & Skip & Top,
            Json  = GetJson(Url),
            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)
    
...

 


What is not described in the blog post, however, is how I can manage the pagination. Now I may have managed the query, but my current error message refers specifically to GetJiraIDCount=...

Expression.Error: We cannot apply operator & to types Binary and Text.
Details:
Operator=&
Left=[Binary]
Right=&maxResults=1000

I'm a complete beginner in this field and I'm getting really lost. Somehow it has to work???? I really would appreciate any help!!

1 ACCEPTED SOLUTION
v-huijiey-msft
Community Support
Community Support

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!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @Alina12,

 

Integrating Jira and Power BI can be challenging, but fortunately, you can use two main methods to achieve this. One option is the Jira REST API, which requires technical skills and can be complex. The second option is Power BI Connector for Jira.
It is a user-friendly app that enables you to export Jira data to Power BI without the need for coding skills. Learn more about the second method here: 

https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=cloud&tab=overvie... it's available for Jira Cloud / Server / Data Center.

 

Daria

v-huijiey-msft
Community Support
Community Support

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!

hi @v-huijiey-msft ,

 

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),

    ....

 

Hi @Alina12 ,

 

You mentioned that it works fine in Power BI Desktop, but there is a problem in Service. One possible influencing factor is:

 

Please check that the authentication token is still valid and is passed correctly with every request. Token expiration or authentication issues can sometimes manifest as errors with dynamic data sources.

 

Here is a revised approach for the pagination part:

GetJiraIDCount = () =>
    let
        Json = Web.Contents(
            https://XXX.XXX.net/jira/rest/api,
            [
                RelativePath="2/search",
                Query=[jql="filter=525545", maxResults="0", fields=""],
                Headers=[Authorization="Bearer XXX"]
            ]
        ),
        Data = Json.Document(Json),
        Count = Data[total]
    in
        Count,

GetPage = (Index) =>
    let
        Skip = Text.From(Index * JiraIDPerPage),
        PageData = Web.Contents(
            https://XXX.XXX.net/jira/rest/api,
            [
                RelativePath="2/search",
                Query=[
                    jql="filter=525545",
                    startAt=Skip,
                    maxResults="1000",
                    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,

 

For more information on using in Power BI, I recommend reviewing the following documentation:

Web.Contents - PowerQuery M | Microsoft Learn

 

If you have any further questions please feel free to contact me.

 

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
Helper I
Helper I

UPDATE

I have managed to get the code to work with Web.Contents, Relative Path and Query. The problem now is that a) I only get the first 1000 hits instead of approx. 5,500 hits (although JiraIDCount shows the correct number of total hits) and b) it still shows me a dynamic data source and the data refresh is not possible.

So it seems that my problem might be with the pagination, because the whole www says that the dynamic data refresh should work with Web.Contents, Relative Path and Query. Can anyone help me?

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  = "&startAt=" & Text.From(Index * JiraIDPerPage),
            Top   = "&maxResults=" & Text.From(JiraIDPerPage),
            Url   = Text.From(BaseUrl) & Skip & Top,
            Json  = GetJson(Url),
            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),

...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.