Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

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

1 vote
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

@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?

Or @Mohamed Benziane if you have any idea :D

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?

@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

Suggest an answer

Log in or Sign up to answer
TAGS

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