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

Brian Cariveau Sep 11, 2018

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
Mohamed Benziane Sep 11, 2018

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 Sep 12, 2018

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 Sep 12, 2018

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 in Next-gen

Introducing subtasks for breaking down work in next-gen projects

Teams break work down in order to help simplify complex tasks. This is often done iteratively, with tasks being broken down into smaller tasks and so on until the work is accurately captured in well-...

8,308 views 25 36
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you