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

[bapillmore]

@Brian Cariveau

I am trying to repurpose your code for my own JIRA pull but I am a complete novice at M. In your getNextPage function, what values are allowed and what purpose do they serve?

[bapillmore]

Or @Mohamed Benziane if you have any idea :D

[Brian Cariveau]

Are you on prem JIRA or cloud.  

If on prem, you should be able to use the functions above as is - only changing the Jira filter if that is bolded.  

if using Jira cloud I would recommend pulling a fresh content pack from the Microsoft site as that has slightly different code for the cloud.   

what error are you seeing on your side?

[bapillmore]

@Brian Cariveau 

Our JIRA is hosted on AWS and we have been advised to use the REST API (hence why I tried to poach your code).

I do not see an error, persé, but when I try to invoke the function using your code, I am met with a grayed-out Invoke box indicating I need to supply a vale for GetNextPage.

Here's what I have based on your code:

let

Source = FetchPages("https://jira.aws.COMPANY.org", 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=90112",[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), //What does this do? --Added getNextPage to function --didn't work

(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)

in GenerateByPage

It won't let me execute otherwise :(

I'll try the content pack in the meantime to see if there is something there.

My current code (not yours, but what I am trying to replace with something better as this doesn't reliably fetch data (inconsistent row counts based on page size inputted):

let

Fetch_JIRA_data = (optional JIRA_API_text_excluding_base_url as nullable text) as table =>

let

JIRA_base_url = "https://jira.aws.COMPANY.org",

JIRA_API_text_excluding_base_url = if JIRA_API_text_excluding_base_url is null then "/rest/api/2/search?/?&jql=filter=90112"

else JIRA_API_text_excluding_base_url,

qryBase = JIRA_base_url & JIRA_API_text_excluding_base_url & "&startAt=",

Source = Json.Document(Web.Contents( qryBase & "0" )),

numIssues = Source[total],

startAtList = List.Generate(()=>0, each _ < numIssues, each _ +40),

urlList = List.Transform(startAtList, each qryBase & Text.From(_) ),

data = List.Transform(urlList, each Json.Document(Web.Contents(_))),

iLL = List.Generate(

() => [i=-1, iL={} ],

each [i] < List.Count(data),

each [

i = [i]+1,

iL = data{i}[issues]

],

each [iL]

),

issues = List.Combine(iLL),

#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

#"Converted to Table"

in Fetch_JIRA_dat