Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |