cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Rest api looping

Hi

 

i am new to Power BI . i have a requirment and i am  working on Rest api.how to loop the every 1000 records in using loop condition.and this is my url

let
Source = Json.Document(Web.Contents("https://url.com/rest/api/2/search?jql=category=10980+order+by+id&startAt=1&maxResults=1000")),
issues = Source[issues],
#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Column1 = #"Converted to Table"[Column1],
#"Converted to Table1" = Table.FromList(Column1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "self", "key", "fields"}, {"Column1.id", "Column1.self", "Column1.key", "Column1.fields"}),
#"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"resolution", "components", "reporter", "issuetype", "project", "updated", "description", "summary", "priority", "status","creator", "created","Assignee",
"customfield_14907","customfield_14731","customfield_14801","customfield_14802","customfield_14729","customfield_16000"},{"Column1.fields.resolution", "Column1.fields.components", "Column1.fields.reporter", "Column1.fields.issuetype", "Column1.fields.project","Column1.fields.updated", "Column1.fields.description", "Column1.fields.summary", "Column1.fields.priority", "Column1.fields.status","Column1.fields.creator", "Column1.fields.created","Column1.fields.customfield_14907","Column1.fields.customfield_14731", "Column1.fields.customfield_14801", "Column1.fields.customfield_14802","Column1.fields.customfield_14729","Column1.fields.customfield_16000","Column1.fields.Assignee"}),
#"Expanded Column1.fields.resolution" = Table.ExpandRecordColumn(#"Expanded Column1.fields", "Column1.fields.resolution", {"self", "id", "description", "name"}, {"Column1.fields.resolution.self", "Column1.fields.resolution.id", "Column1.fields.resolution.description", "Column1.fields.resolution.name"}),
#"Expanded Column1.fields.components" = Table.ExpandListColumn(#"Expanded Column1.fields.resolution", "Column1.fields.components"),
#"Expanded Column1.fields.components1" = Table.ExpandRecordColumn(#"Expanded Column1.fields.components", "Column1.fields.components", {"self", "id", "name", "description"}, {"Column1.fields.components.self", "Column1.fields.components.id", "Column1.fields.components.name", "Column1.fields.components.description"}),
#"Expanded Column1.fields.reporter" = Table.ExpandRecordColumn(#"Expanded Column1.fields.components1", "Column1.fields.reporter", {"self", "name", "key", "emailAddress", "avatarUrls", "displayName", "active", "timeZone"}, {"Column1.fields.reporter.self", "Column1.fields.reporter.name", "Column1.fields.reporter.key", "Column1.fields.reporter.emailAddress", "Column1.fields.reporter.avatarUrls", "Column1.fields.reporter.displayName", "Column1.fields.reporter.active", "Column1.fields.reporter.timeZone"}),
#"Expanded Column1.fields.issuetype" = Table.ExpandRecordColumn(#"Expanded Column1.fields.reporter", "Column1.fields.issuetype", {"id", "description", "name", "subtask"}, {"Column1.fields.issuetype.id", "Column1.fields.issuetype.description", "Column1.fields.issuetype.name", "Column1.fields.issuetype.subtask"}),
#"Expanded Column1.fields.project" = Table.ExpandRecordColumn(#"Expanded Column1.fields.issuetype", "Column1.fields.project", {"id", "key", "name", "projectCategory"}, {"Column1.fields.project.id", "Column1.fields.project.key", "Column1.fields.project.name", "Column1.fields.project.projectCategory"}),
#"Expanded Column1.fields.priority" = Table.ExpandRecordColumn(#"Expanded Column1.fields.project", "Column1.fields.priority", {"name", "id"}, {"Column1.fields.priority.name", "Column1.fields.priority.id"}),
#"Expanded Column1.fields.status" = Table.ExpandRecordColumn(#"Expanded Column1.fields.priority", "Column1.fields.status", {"description", "name", "id", "statusCategory"}, {"Column1.fields.status.description", "Column1.fields.status.name", "Column1.fields.status.id", "Column1.fields.status.statusCategory"}),
#"Expanded Column1.fields.creator" = Table.ExpandRecordColumn(#"Expanded Column1.fields.status", "Column1.fields.creator", {"name", "key", "emailAddress", "displayName", "timeZone"}, {"Column1.fields.creator.name", "Column1.fields.creator.key", "Column1.fields.creator.emailAddress", "Column1.fields.creator.displayName", "Column1.fields.creator.timeZone"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.fields.creator",{{"Column1.fields.customfield_14731", "Project Admin"}, {"Column1.fields.customfield_14801", "Service-Gate"}}),
#"Expanded Service-Gate" = Table.ExpandRecordColumn(#"Renamed Columns", "Service-Gate", {"value", "id"}, {"Service-Gate.value", "Service-Gate.id"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Service-Gate",{{"Column1.fields.customfield_14802", "GDM Name"}}),
#"Expanded GDM Name" = Table.ExpandRecordColumn(#"Renamed Columns1", "GDM Name", {"value", "id"}, {"GDM Name.value", "GDM Name.id"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded GDM Name",{{"Column1.fields.customfield_14729", "Opportunity-ID"}, {"Column1.fields.customfield_16000", "Sub-Stream"}}),
#"Expanded Sub-Stream" = Table.ExpandRecordColumn(#"Renamed Columns2", "Sub-Stream", {"value", "id"}, {"Sub-Stream.value", "Sub-Stream.id"}),
#"Expanded Column1.fields.Assignee" = Table.ExpandListColumn(#"Expanded Sub-Stream", "Column1.fields.Assignee"),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Column1.fields.Assignee",{{"Column1.key", "key"}, {"Column1.fields.updated", "updated"}, {"Column1.fields.summary", "summary"}, {"Column1.fields.status.name", "status.name"}, {"Column1.fields.resolution.name", "resolution.name"}, {"Column1.fields.reporter.name", "reporter.name"}, {"Column1.fields.project.name", "project.name"}, {"Column1.fields.project.key", "project.key"}, {"Column1.fields.priority.name", "priority.name"}, {"Column1.fields.created", "created"}, {"Column1.fields.creator.name", "creator.name"}, {"Column1.fields.issuetype.name", "issuetype.name"}, {"Column1.fields.Assignee", "Assignee"}})
in
#"Renamed Columns3"

 

 

 

and how to loop the next 1000 records .Please provide any solution and next url it should be like this

 

https://url.com/rest/api/2/search?jql=category=10980+order+by+id&startAt=1001&maxResults=2000.Can you please provide power query.

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: Rest api looping

Hi @jayendra,

After research, there is no "looping" in Power Query, only recursion, please review this article. For your scenario, you can upload each 1000 records data into different table, then you can append the table in Power Query. For example, you upload first 1000 records data in to Table1, next 1000 records into Table2, append Table2 to Table1, you will get first 2000 records.

Thanks,
Angelia


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors