Power BI with JIRA Source - Refresh Error

[Gaurav Malpani]

Hi Experts,

I build a report in Power BI desktop where source is JIRA Cloud. I am able to refresh in Power Bi desktop but when I trigger a manual refresh, it gives me an error message given below -
Data source error Unable to refresh the model (id=11272710) because it references an unsupported data source.

JIRA Cloud by default push only 50 rows per page and if you define MaxResults, it gives 1000 rows. This is I believe JIRA defaut behaviour.
So, I was using other recommended way to use functions Fetch Pages/page and get all records. Code is given below 

Generate by Page function:

//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)​

Fetch Page function:

//Query Name: FetchPage
let
    FetchPage = (server as text, url as text, pageSize as number, skipRows as number) as table =>
    let
        //Here is where you run the code that will return a single page        
        contents = Web.Contents(server,[RelativePath=url, Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)]]),
        json = Json.Document(contents),
        Value = json[issues],
        table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        table meta [skipRows = skipRows + pageSize, total = 500]
in
    FetchPage​

Fetch Pages Function:

//Query Name: FetchPages
let
    FetchPages = (server as text, url as text, pageSize as number) => 
let 
    Source = GenerateByPage(
    (previous) =>
    let
        skipRows = if previous = null then 0 else Value.Metadata(previous)[skipRows],
        totalItems = if previous = null then 0 else Value.Metadata(previous)[total],
        table = if previous = null or Table.RowCount(previous) = pageSize then 
                    FetchPage(server, url, pageSize, skipRows) 
		else null
    in table,
    
    type table [Column1])
in
    Source
in
    FetchPages​

 Next I am using above functions to import rows in Power BI:

let
    Source = FetchPages("https://jiradc2.int.net.nokia.com", "/rest/api/2/search?jql=project%20in%20(NE1,NE2,NE3,NE4,NE5,NE6,NE7)%20AND%20issuetype%20in%20(Epic,SAFe_Solution_Epic,SAFe_Capability,Improvement,Story,%22New%20Feature%22)%20AND%20created>%3D2020-04-04&fields=customfield_41593&fields=customfield_12157&fields=updated&fields=resolutiondate&fields=lastViewed&fields=duedate&fields=created&fields=versions&fields=summary&fields=priority&fields=components&fields=comment&fields=assignee&fields=subtasks&fields=status&fields=resolution&fields=reporter&fields=project&fields=issuetype&fields=fixVersions&fields=creator&fields=labels&expand=changelog", 500)
    
in
    Source​

With this I am successfully loading 45K+ rows in Power BI desktop but when I published this is giving me an error mentioned above.

Option 2 -

Thinking may be Power Bi service is not recognising these functions, I tries Web.contents with Relative path - 

 -

let
    Source = Json.Document(Web.Contents("https://jiradc2.int.net.nokia.com", [RelativePath="/rest/api/2/search?jql=project%20in%20(NEO1,NEO2, NEO3,NEO4,NEO5,NEO6)%20AND%20issuetype%20in%20(Epic,SAFe_Solution_Epic,SAFe_Capability,Improvement,Story,%22New%20Feature%22)%20AND%20created>%3D2017-01-01&fields=customfield_41593&fields=customfield_12157&fields=updated&fields=resolutiondate&fields=lastViewed&fields=duedate&fields=created&fields=versions&fields=summary&fields=priority&fields=components&fields=comment&fields=assignee&fields=subtasks&fields=status&fields=resolution&fields=reporter&fields=project&fields=issuetype&fields=fixVersions&fields=creator&fields=labels&expand=changelog"])),
    issues = Source[issues],
    #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"​

With this query I managed to refresh on Power Bi service but now I am getting only 50 rows instead of ~45K. Even if define maxResults to 10000, it gives in return only 1000 rows as it is a default behaviour of JIRA.

I have read response from (@Tiago_Machado) for one of the similar query but since I am not an expert I couldn't make much use of it. 

If the either of the options code can be modified in a way that it gives all rows and also refresh on Power Bi service, it will be a great hep for us.
Because of this issue many Power Bi reports are stuck in our department.

Thanks again !

[Thierry Dalon]

I have shared my learnings/ solution here: https://tdalon.blogspot.com/jira2powerbi