PowerBI content pack only pulling 50 rows from API

[Brian Cariveau]

Good morning,

I am using a content pack supplied here by the PowerBI team.

https://powerbi.microsoft.com/en-us/blog/explore-your-jira-data-with-power-bi/  

We are on JIRA version 7.10.00 - on premise and I am having success pulling 50 rows, however it should be returning much more than this given the filter I am connected to. The content pack appears to have code to loop through to pull all data however it does not appear to be working.  Does anyone spot an error in the code below?

Thanks in advance for any help!!

Brian 

-------------------------------------------------------------------------------

Source = FetchPages("https://JIRAURL.com/rest/api/2/search?jql=filter=34233", 20)

-------------------------------------------------------------------------------

FetchPages = (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(url, pageSize, skipRows)
else null
in table,

type table [Column1])
in
Source

----------------------------------------------------------------------------------- 

FetchPage = (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(url),
json = Json.Document(contents),
Value = json[issues],
table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
table meta [skipRows = skipRows + pageSize, total = 2000]

-----------------------------------------------------------------------------------------------

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)

[Mohamed Benziane · Accepted solution]

Hi @Brian Cariveau

You have to set the maxResults attribut at the value you want

/rest/api/2/search?jql=[jqlQuery]&maxResults=100

[Brian Cariveau]

Thanks Mohamed - you sparked the solution!  Trying to get this work, and pull the correct projects we had played with the code quite a bit - so we went back to the original content pack code, and had to slightly modify the areas in bold to get everything to work.  This does indeed set the maxResults properly and now I am able to pull all 5k+ issues I was hoping for!  

-------------------------------------------------

FetchPages("https://JIRAURL.com", 100)

-----------------------

FetchPages = (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(url, pageSize, skipRows)
else null
in table,

type table [Column1])
in
Source

------------------------------------------------------------

FetchPage = (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(URL&"/rest/api/2/search?jql=filter=34233",[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]

------------------------------------------------------------------------

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)

[Mohamed Benziane]

Hi @Brian Cariveau 

I'm glad i helped you

Regards