Jira Surpass MaxRsults>1000

[Anonymous]

Dear Everyone,
I Have meet issue with Jira maxResults only 1000.But i sovle the problem that i can get all records.

But when i publish to Power BI Server , It can not shcedule refresh.Due to this error.
Please anyone can help me.

This is error:

This is my M Function:

let
Pagination = List.Skip(List.Generate( () => [Page = 0, Counter=0], // Start Value
each [Counter]<40, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("URL"&Text.From([Page]))), // retrieve results per call
Page = [Page]+50,
Counter = [Counter]+1,// internal counter
Table = Table.FromRecords(WebCall[issues]) // steps of your further query
//Value = #"Converted to Table"{0}[Value] // last step of your further queries
]
,each [Table]

),1),
Custom1 = Table.Combine(Pagination),
#"Removed Other Columns" = Table.SelectColumns(Custom1,{"key", "fields", "changelog"}),
#"Renamed Columns6" = Table.RenameColumns(#"Removed Other Columns",{{"key", "Column1.key"}, {"fields", "Column1.fields"}, {"changelog", "Column1.changelog"}}),
#"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Renamed Columns6", "Column1.fields", {"customfield_11509","customfield_11508","assignee","reporter","created","project","summary","status","issuetype"}, {"Column1.fields.customfield_11509","Column1.fields.customfield_11508","Column1.fields.assignee","Column1.fields.reporter","Column1.fields.created","Column1.fields.project","Column1.fields.summary","Column1.fields.status","Column1.fields.issuetype"}),
#"Expanded Column1.fields.customfield_11509" = Table.ExpandRecordColumn(#"Expanded Column1.fields", "Column1.fields.customfield_11509", {"value"}, {"Column1.fields.customfield_11509.value"}),
#"Expanded Column1.fields.customfield_11508" = Table.ExpandRecordColumn(#"Expanded Column1.fields.customfield_11509", "Column1.fields.customfield_11508", {"value"}, {"Column1.fields.customfield_11508.value"}),
#"Renamed Columns5" = Table.RenameColumns(#"Expanded Column1.fields.customfield_11508",{{"Column1.fields.customfield_11508.value", "Main_Service_Type"}}),
#"Expanded Column1.fields.issuetype" = Table.ExpandRecordColumn(#"Renamed Columns5", "Column1.fields.issuetype", {"name"}, {"Column1.fields.issuetype.name"}),
#"Expanded Column1.fields.assignee" = Table.ExpandRecordColumn(#"Expanded Column1.fields.issuetype", "Column1.fields.assignee", {"displayName"}, {"Column1.fields.assignee.displayName"}),
#"Renamed Columns4" = Table.RenameColumns(#"Expanded Column1.fields.assignee",{{"Column1.fields.assignee.displayName", "Assignee name"}}),
#"Expanded Column1.fields.reporter" = Table.ExpandRecordColumn(#"Renamed Columns4", "Column1.fields.reporter", {"displayName"}, {"Column1.fields.reporter.displayName"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Column1.fields.reporter",{{"Column1.fields.reporter.displayName", "Reporter name"}}),
#"Expanded Column1.fields.project" = Table.ExpandRecordColumn(#"Renamed Columns3", "Column1.fields.project", {"name"}, {"Column1.fields.project.name"}),
#"Expanded Column1.fields.status" = Table.ExpandRecordColumn(#"Expanded Column1.fields.project", "Column1.fields.status", {"name"}, {"Column1.fields.status.name"}),
#"Expanded Column1.changelog" = Table.ExpandRecordColumn(#"Expanded Column1.fields.status", "Column1.changelog", {"histories"}, {"Column1.changelog.histories"}),
#"Expanded Column1.changelog.histories" = Table.ExpandListColumn(#"Expanded Column1.changelog", "Column1.changelog.histories"),
#"Expanded Column1.changelog.histories1" = Table.ExpandRecordColumn(#"Expanded Column1.changelog.histories", "Column1.changelog.histories", {"author", "created", "items"}, {"Column1.changelog.histories.author", "Column1.changelog.histories.created", "Column1.changelog.histories.items"}),
#"Expanded Column1.changelog.histories.author" = Table.ExpandRecordColumn(#"Expanded Column1.changelog.histories1", "Column1.changelog.histories.author", {"name", "emailAddress"}, {"Column1.changelog.histories.author.name", "Column1.changelog.histories.author.emailAddress"}),
#"Expanded Column1.changelog.histories.items" = Table.ExpandListColumn(#"Expanded Column1.changelog.histories.author", "Column1.changelog.histories.items"),
#"Expanded Column1.changelog.histories.items1" = Table.ExpandRecordColumn(#"Expanded Column1.changelog.histories.items", "Column1.changelog.histories.items", {"field", "fromString", "toString"}, {"Column1.changelog.histories.items.field", "Column1.changelog.histories.items.fromString", "Column1.changelog.histories.items.toString"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.changelog.histories.items1",{{"Column1.key", "Issue key"}, {"Column1.fields.project.name", "Project name"}, {"Column1.fields.summary", "Summary"}, {"Column1.fields.status.name", "Main status"}, {"Column1.changelog.histories.author.name", "Author name"}, {"Column1.changelog.histories.author.emailAddress", "Author email"}, {"Column1.changelog.histories.created", "Created"}, {"Column1.changelog.histories.items.field", "Status"}, {"Column1.changelog.histories.items.fromString", "fromString"}, {"Column1.changelog.histories.items.toString", "toString"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","+0700","",Replacer.ReplaceText,{"Created"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Created", type datetime}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Column1.fields.created", "Issue created"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns1","+0700","",Replacer.ReplaceText,{"Issue created"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Issue created", type datetime}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{ {"Column1.fields.issuetype.name", "Issue type"}, {"Column1.fields.customfield_11509.value", "Sub_Service_Type"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Sub_Service_Type", type text}, {"Main_Service_Type", type text}, {"Assignee name", type text}, {"Reporter name", type text}})
in
#"Changed Type2"

Thank you in advance for this help!!!!

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You may refer to this blog to see if it could help you:

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

[Anonymous]

Wow Cool.
Thank you. it works now

[amitchandak]

@Anonymous , Check if pagination can help.

refer if this can help

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

[Anonymous]

Hi, @amitchandak

I tried your link that you suggest, but i still face the same problem like image above.
This is code i follow your link:

let
BaseUrl = "URL",
EntitiesPerPage = 50,

GetJson = (Url) =>
let
RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,

GetPage = (Index) =>
let startIndex = Text.From(Index),
Url = BaseUrl & startIndex,
Json = GetJson(Url),
Value = Json[#"issues"]
in Value,
EntityCount = List.Max({ 0, 100}),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"key", "fields", "changelog"}, {"Column1.key", "Column1.fields", "Column1.changelog"})
in
#"Expanded Column1"

Thank you.