Forum Discussion

UdoMo's avatar
UdoMo
Frequent Visitor
5 years ago
Solved

Problems connecting to API (Jira)

I'm trying to connect Power BI Desktop to our Jira on premise instance using the following query (found here with some modifications):

 

let 
    BaseUrl = "https://<jira-server-name>/rest/api/2/search?jql=project in ('<Project>')",
    JiraIDPerPage = 1000,
 
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            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),
    Table1 = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table1, "Column1", {"id", "key", "fields"}, {"issue.id", "issue.key", "issue.fields"})
in
    #"Expanded Column1"

 

 

This query works fine in Power Query Editor and gets all Tickets from Jira. When I press "Close & Apply" the following Message pops up after several seconds:

Replacing "Column1" with Table.ColumnNames(Table§){0} in the query results in

As I'm new to Power Bi I'm stuck. Can someone tell me what I'm doing wrong? 

Thanks in advance!

PS: I'm using Microsoft Power BI Desktop Version: 2.88.1385.0 64-bit (Dezember 2020)

4 Replies