Forum Discussion

dnsyzygy's avatar
dnsyzygy
New Member
3 years ago

Need help overcoming pagination issue with API request (PowerBI <> Scoro)

Hi all,
This is my very first post in the community so please bear with my possible ignorance!..

I've been using chatGTP to help me with a power query to retrieve data from SCORO (www.scoro.com) via API, in this case Purchase Orders. I've been able to successfully establish the link and retrieve the first 100 rows of data, however can't seem to get the formula right to achieve the loop through the pagination and get all the records. I keep getting errors like "Expression.Error: The import getNextPage matches no exports. Did you miss a module reference?" and chatGTP is not having much success getting around them.

Below i've added a link to Scoro's documentation on its API and the latest code I go to. Was wondering if anyone knowledgeable of APIs with pagination could chip in, perhaps?

Much appreciated for any help in advance!
Best
dn

Scoro API documentation 

 

 

 

let
    apiKey = "YOUR_API_KEY",
    companyAccountId = "YOUR_COMPANY_ID",
    apiUrl = "https://YOUR_COMPANY.scoro.com/api/v2/purchaseOrders/list",
    options = [
        Headers = [Authorization="Bearer " & apiKey, company_account_id=companyAccountId],
        Content = Json.FromValue([
            lang = "eng",
            company_account_id = companyAccountId,
            apiKey = apiKey,
            request = [per_page = 100, page = 1]
        ])
    ],
    getNextPage = (url, opt, page) =>
        let
            content = Json.Document(Web.Contents(url, opt)),
            purchaseOrders = content[data],
            nextPage = if List.Count(purchaseOrders) = 100 then getNextPage(url, opt, page + 1) else {}
        in
            purchaseOrders & nextPage,
    allPurchaseOrders = getNextPage(apiUrl, options, 1),
    purchaseOrdersTable = Table.FromList(allPurchaseOrders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedColumns = Table.ExpandRecordColumn(purchaseOrdersTable, "Value", {"id", "no", "sum", "vat", "company_id", "company_name", "project_id", "project_name", "currency", "owner_id", "date", "status", "description", "is_sent", "lines", "created_date", "is_deleted"}),
    renamedColumns = Table.RenameColumns(expandedColumns, {{"Value.id", "id"}, {"Value.no", "no"}, {"Value.sum", "sum"}, {"Value.vat", "vat"}, {"Value.company_id", "company_id"}, {"Value.company_name", "company_name"}, {"Value.project_id", "project_id"}, {"Value.project_name", "project_name"}, {"Value.currency", "currency"}, {"Value.owner_id", "owner_id"}, {"Value.date", "date"}, {"Value.status", "status"}, {"Value.description", "description"}, {"Value.is_sent", "is_sent"}, {"Value.lines", "lines"}, {"Value.created_date", "created_date"}, {"Value.is_deleted", "is_deleted"}})
in
    renamedColumns

 

 

 

Need Help

2 Replies

    • dnsyzygy's avatar
      dnsyzygy
      New Member
      3 years ago

      Hi BA_Pete 

      thank you for the prompt reply.

      Unfortunately I'm still a newby and chatGTP is not managing to help, although I think the problem is more related with Scoro themselves more than anything..

      Was wondering if you could be more specific in terms of the code to use in the steps you set out? Or at least in the first, to see if I can get somewhere..?

      Probably based on the early part of the code I sent initially. Let me know if you're able to help.

      Once again, many thanks for any help you can provide!

      Best
      dn