PowerQuery Loop Every 50 Records Until the Last Record

Question

In Microsoft PowerBI have written the below M code to fetch records via a web API. The query works, but it fetches only the first 50 records. I would like for the query to repeat itself, until it has fetched all the records from the source. Can anybody put me on the right track?

let 
    postData = Json.FromValue([fStartIndex=1]),
    Source = Web.Contents("https://XXX.appiancloud.com/suite/webapi/XXX",[Content = postData,Headers=[#"Content-Type"="application/json", #"Appian-API-Key"="xxxxxxxxxxxxyyyyyyyyyyyyyyyzzzzzzzzzzzzzzz"]]),
    #"Imported JSON" = Json.Document(Source),
    #"Converted to Table" = Table.FromRecords({#"Imported JSON"}),
    #"Parsed JSON" = Table.TransformColumns(#"Converted to Table",{{"recommendations", Json.Document}}),
    #"Expanded recommendations" = Table.ExpandListColumn(#"Parsed JSON", "recommendations"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded recommendations",{{"totalCount", Int64.Type}, {"nextBatchStartIndex", Int64.Type}, {"recommendations", type any}}),
    #"Expanded recommendations1" = Table.ExpandRecordColumn(#"Changed Type", "recommendations", {"updatedDateFormatDate", "createdDateFormatDate", "recommendationSource", "iosRecommendationId", "secondaryDepartmentEmail", "sourceLink", "meetingDate", "delegateToDate", "delegateFromDate", "delegatee", "dateOfissuanceText", "updatedDate", "updatedBy", "createdDate", "createdBy", "gbReport", "status", "customField5", "customField4", "customField3", "customField2", "customField1", "generalComment", "recommendationBODeadline", "flmComment", "flmApprovalDecision", "flmName", "initialManagementResponse", "crossReferencing", "riskLevel", "priorityLevel", "recommendationDeadline", "externaltargetAudience", "secondaryDepartments", "associateBusinessOwner", "businessOwnerName", "businessOwnerDepartment", "division", "acceptanceComment", "whoAcceptance", "openEndedProcess", "theme3", "theme2", "theme1", "category", "recommendation", "agendaItemName", "agendaItem", "sourceReportNo", "dateOfissuance", "meeting", "portal", "indexNo", "recommendationId"}, {"Column3.updatedDateFormatDate", "Column3.createdDateFormatDate", "Column3.recommendationSource", "Column3.iosRecommendationId", "Column3.secondaryDepartmentEmail", "Column3.sourceLink", "Column3.meetingDate", "Column3.delegateToDate", "Column3.delegateFromDate", "Column3.delegatee", "Column3.dateOfissuanceText", "Column3.updatedDate", "Column3.updatedBy", "Column3.createdDate", "Column3.createdBy", "Column3.gbReport", "Column3.status", "Column3.customField5", "Column3.customField4", "Column3.customField3", "Column3.customField2", "Column3.customField1", "Column3.generalComment", "Column3.recommendationBODeadline", "Column3.flmComment", "Column3.flmApprovalDecision", "Column3.flmName", "Column3.initialManagementResponse", "Column3.crossReferencing", "Column3.riskLevel", "Column3.priorityLevel", "Column3.recommendationDeadline", "Column3.externaltargetAudience", "Column3.secondaryDepartments", "Column3.associateBusinessOwner", "Column3.businessOwnerName", "Column3.businessOwnerDepartment", "Column3.division", "Column3.acceptanceComment", "Column3.whoAcceptance", "Column3.openEndedProcess", "Column3.theme3", "Column3.theme2", "Column3.theme1", "Column3.category", "Column3.recommendation", "Column3.agendaItemName", "Column3.agendaItem", "Column3.sourceReportNo", "Column3.dateOfissuance", "Column3.meeting", "Column3.portal", "Column3.indexNo", "Column3.recommendationId"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded recommendations1",{"totalCount", "nextBatchStartIndex"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column3.updatedDateFormatDate", "updatedDateFormatDate"}, {"Column3.createdDateFormatDate", "createdDateFormatDate"}, {"Column3.recommendationSource", "recommendationSource"}, {"Column3.iosRecommendationId", "iosRecommendationId"}, {"Column3.secondaryDepartmentEmail", "secondaryDepartmentEmail"}, {"Column3.sourceLink", "sourceLink"}, {"Column3.meetingDate", "meetingDate"}, {"Column3.delegateToDate", "delegateToDate"}, {"Column3.delegateFromDate", "delegateFromDate"}, {"Column3.delegatee", "delegatee"}, {"Column3.dateOfissuanceText", "dateOfissuanceText"}, {"Column3.updatedDate", "updatedDate"}, {"Column3.updatedBy", "updatedBy"}, {"Column3.createdDate", "createdDate"}, {"Column3.createdBy", "createdBy"}, {"Column3.gbReport", "gbReport"}, {"Column3.status", "status"}, {"Column3.customField5", "customField5"}, {"Column3.customField4", "customField4"}, {"Column3.customField3", "customField3"}, {"Column3.customField2", "customField2"}, {"Column3.customField1", "customField1"}, {"Column3.generalComment", "generalComment"}, {"Column3.recommendationBODeadline", "recommendationBODeadline"}, {"Column3.flmComment", "flmComment"}, {"Column3.flmApprovalDecision", "flmApprovalDecision"}, {"Column3.flmName", "flmName"}, {"Column3.initialManagementResponse", "initialManagementResponse"}, {"Column3.crossReferencing", "crossReferencing"}, {"Column3.riskLevel", "riskLevel"}, {"Column3.priorityLevel", "priorityLevel"}, {"Column3.recommendationDeadline", "recommendationDeadline"}, {"Column3.externaltargetAudience", "externaltargetAudience"}, {"Column3.secondaryDepartments", "secondaryDepartments"}, {"Column3.associateBusinessOwner", "associateBusinessOwner"}, {"Column3.businessOwnerName", "businessOwnerName"}, {"Column3.businessOwnerDepartment", "businessOwnerDepartment"}, {"Column3.division", "division"},{"Column3.acceptanceComment", "acceptanceComment"}, {"Column3.whoAcceptance", "whoAcceptance"}, {"Column3.openEndedProcess", "openEndedProcess"}, {"Column3.theme3", "theme3"}, {"Column3.theme2", "theme2"}, {"Column3.theme1", "theme1"}, {"Column3.category", "category"}, {"Column3.recommendation", "recommendation"}, {"Column3.agendaItemName", "agendaItemName"}, {"Column3.agendaItem", "agendaItem"}, {"Column3.sourceReportNo", "sourceReportNo"}, {"Column3.dateOfissuance", "dateOfissuance"}, {"Column3.meeting", "meeting"}, {"Column3.portal", "portal"}, {"Column3.indexNo", "indexNo"}, {"Column3.recommendationId", "recommendationId"}})
in
        #"Renamed Columns"

you could see if one of these helps you https://stackoverflow.com/questions/74387508/power-bi-pagination-how-to-pull-all-data-from-api-using-nextpagelink https://gorilla.bi/power-query/list-generate-api-calls/ https://stackoverflow.com/questions/74177542/power-query-list-generate-to-loop-paginated-api-calls — horseyride, Nov 10 '22 at 19:37

score 0 · Accepted Answer · answered Nov 24 '22 at 14:02

After many trials and thanks to guidance found on the net, I managed to create the function below:

    (fStartIndex) =>
let 
    postData = Json.FromValue([fStartIndex=fStartIndex]),
    Source = Web.Contents("https://XXXXXX.appiancloud.com/suite/webapi/recommdata_powerbipost",[Content = postData,Headers=[#"Content-Type"="application/json", #"Appian-API-Key"="enter the api key here"]]),
    #"Imported JSON" = Json.Document(Source),
    #"Converted to Table" = Table.FromRecords({#"Imported JSON"}),
    #"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"totalCount", "nextBatchStartIndex"})
in
    #"Removed Columns"

Then I created the following query, which is based on the function above:

    let
    ExpandedRecommendations = let
    ExpandedRecommendations = List.Generate(
    () => [Offset = 50, Recommendation = GetRecommendations(1) ],
    each not ( List.Single( [Recommendation][recommendations] ) = "[]"),
    each [Recommendation = GetRecommendations( [Offset] ),
        Offset = [Offset] + 50],
    each ([Recommendation][recommendations])
),
    #"Converted to Table" = Table.FromList(ExpandedRecommendations, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table",
    #"Expanded Column1" = Table.ExpandListColumn(ExpandedRecommendations, "Column1"),
    #"Parsed JSON" = Table.TransformColumns(#"Expanded Column1",{},Json.Document),
    #"Expanded Column2" = Table.ExpandListColumn(#"Parsed JSON", "Column1"),
    #"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1", {"updatedDateFormatDate", "createdDateFormatDate", "recommendationSource", "iosRecommendationId", "secondaryDepartmentEmail", "sourceLink", "meetingDate", "delegateToDate", "delegateFromDate", "delegatee", "dateOfissuanceText", "updatedDate", "updatedBy", "createdDate", "createdBy", "gbReport", "status", "customField5", "customField4", "customField3", "customField2", "customField1", "generalComment", "recommendationBODeadline", "flmComment", "flmApprovalDecision", "flmName", "initialManagementResponse", "crossReferencing", "riskLevel", "priorityLevel", "recommendationDeadline", "externaltargetAudience", "secondaryDepartments", "associateBusinessOwner", "businessOwnerName", "businessOwnerDepartment", "division", "acceptanceComment", "whoAcceptance", "openEndedProcess", "theme3", "theme2", "theme1", "category", "recommendation", "agendaItemName", "agendaItem", "sourceReportNo", "dateOfissuance", "meeting", "portal", "indexNo", "recommendationId"}, {"Column1.updatedDateFormatDate", "Column1.createdDateFormatDate", "Column1.recommendationSource", "Column1.iosRecommendationId", "Column1.secondaryDepartmentEmail", "Column1.sourceLink", "Column1.meetingDate", "Column1.delegateToDate", "Column1.delegateFromDate", "Column1.delegatee", "Column1.dateOfissuanceText", "Column1.updatedDate", "Column1.updatedBy", "Column1.createdDate", "Column1.createdBy", "Column1.gbReport", "Column1.status", "Column1.customField5", "Column1.customField4", "Column1.customField3", "Column1.customField2", "Column1.customField1", "Column1.generalComment", "Column1.recommendationBODeadline", "Column1.flmComment", "Column1.flmApprovalDecision", "Column1.flmName", "Column1.initialManagementResponse", "Column1.crossReferencing", "Column1.riskLevel", "Column1.priorityLevel", "Column1.recommendationDeadline", "Column1.externaltargetAudience", "Column1.secondaryDepartments", "Column1.associateBusinessOwner", "Column1.businessOwnerName", "Column1.businessOwnerDepartment", "Column1.division", "Column1.acceptanceComment", "Column1.whoAcceptance", "Column1.openEndedProcess", "Column1.theme3", "Column1.theme2", "Column1.theme1", "Column1.category", "Column1.recommendation", "Column1.agendaItemName", "Column1.agendaItem", "Column1.sourceReportNo", "Column1.dateOfissuance", "Column1.meeting", "Column1.portal", "Column1.indexNo", "Column1.recommendationId"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column3",{{"Column1.updatedDateFormatDate", "updatedDateFormatDate"}, {"Column1.createdDateFormatDate", "createdDateFormatDate"}, {"Column1.recommendationSource", "recommendationSource"}, {"Column1.iosRecommendationId", "iosRecommendationId"}, {"Column1.secondaryDepartmentEmail", "secondaryDepartmentEmail"}, {"Column1.sourceLink", "sourceLink"}, {"Column1.meetingDate", "meetingDate"}, {"Column1.delegateToDate", "delegateToDate"}, {"Column1.delegateFromDate", "delegateFromDate"}, {"Column1.delegatee", "delegatee"}, {"Column1.dateOfissuanceText", "dateOfissuanceText"}, {"Column1.updatedDate", "updatedDate"}, {"Column1.updatedBy", "updatedBy"}, {"Column1.createdDate", "createdDate"}, {"Column1.createdBy", "createdBy"}, {"Column1.gbReport", "gbReport"}, {"Column1.acceptanceComment", "acceptanceComment"}, {"Column1.agendaItem", "agendaItem"}, {"Column1.agendaItemName", "agendaItemName"}, {"Column1.flmName", "flmName"}, {"Column1.customField5", "customField5"}, {"Column1.customField2", "customField2"}, {"Column1.associateBusinessOwner", "associateBusinessOwner"}, {"Column1.businessOwnerName", "businessOwnerName"}, {"Column1.businessOwnerDepartment", "businessOwnerDepartment"}, {"Column1.category", "category"}, {"Column1.crossReferencing", "crossReferencing"}, {"Column1.customField1", "customField1"}, {"Column1.customField3", "customField3"}, {"Column1.dateOfissuance", "dateOfissuance"}, {"Column1.customField4", "customField4"}, {"Column1.division", "division"}, {"Column1.recommendationBODeadline", "recommendationBODeadline"}, {"Column1.priorityLevel", "priorityLevel"}, {"Column1.indexNo", "indexNo"}, {"Column1.flmComment", "flmComment"}, {"Column1.externaltargetAudience", "externaltargetAudience"}, {"Column1.generalComment", "generalComment"}, {"Column1.flmApprovalDecision", "flmApprovalDecision"}, {"Column1.meeting", "meeting"}, {"Column1.initialManagementResponse", "initialManagementResponse"}, {"Column1.portal", "portal"}, {"Column1.openEndedProcess", "openEndedProcess"}, {"Column1.secondaryDepartments", "secondaryDepartments"}, {"Column1.riskLevel", "riskLevel"}, {"Column1.recommendationId", "recommendationId"}, {"Column1.recommendation", "recommendation"}, {"Column1.recommendationDeadline", "recommendationDeadline"}, {"Column1.sourceReportNo", "sourceReportNo"}, {"Column1.status", "status"}, {"Column1.theme2", "theme2"}, {"Column1.theme1", "theme1"}, {"Column1.theme3", "theme3"}, {"Column1.whoAcceptance", "whoAcceptance"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "dateOfissuanceYear", each Text.Middle([dateOfissuance],Text.PositionOf([dateOfissuance]," ")-4,4)),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"recommendationSource", "Source"}, {"meeting", "Meeting"}, {"dateOfissuanceYear", "Year"}, {"category", "Category"}, {"openEndedProcess", "Open Ended"}, {"division", "Division"}, {"businessOwnerDepartment", "BO Department"}, {"riskLevel", "Risk level"}, {"status", "Status"}, {"gbReport", "GB report/MS engagement"}}),
    #"Parsed Date" = Table.TransformColumns(#"Renamed Columns1",{{"updatedDateFormatDate", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Added Custom1" = Table.AddColumn(#"Parsed Date", "dateOfIssuanceDate", each Date.FromText(Text.From(Text.Middle([dateOfissuance],Text.PositionOf([dateOfissuance]," ")-4,4) & "-"& Text.Range([dateOfissuance],0,Text.PositionOf([dateOfissuance],"/")) & "-" & Text.Middle([dateOfissuance],Text.PositionOf([dateOfissuance]," ")-7,2))))
in
    #"Added Custom1"

It works like a beauty.

PowerQuery Loop Every 50 Records Until the Last Record

1 Answers1