You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.
View groupJoin the community to find out what other Atlassian users are discussing, debating and creating.
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)
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Connect with like-minded Atlassian users at free events near you!
Find an eventConnect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.
Host an eventYou're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.