It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

PowerBI content pack only pulling 50 rows from API

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)

 

1 answer

1 accepted

0 votes
Answer accepted

Hi @Brian Cariveau

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

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

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)

Hi @Brian Cariveau 

I'm glad i helped you

 

Regards

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published Apr 09, 2019 in Portfolio for Jira

Portfolio for Jira 3.0 is here!

The wait is over... Portfolio for Jira Server and Data Center 3.0 is now officially here! Platform releases offer Atlassian an opportunity to shift our strategy, make bold predictions about t...

1,363 views 14 26
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you