API expand column

[Anonymous]

Hi everyone, 

I am loading a table via an API. Therefore, I am using a script and an access and a refresh token. However, in one of the columns, there is another table, which should be expanded, in the same manner I am loading the first API. Unfortunately, I don't know how to do this. The script in the Query Editor is as follows (I had to remove some lines which were private): 

let

    initialTime = DateTime.LocalNow,

    initialUrl = "https://start.exactonline.nl/api/v1/ "&Text.From(Division)&"/bulk/SalesInvoice/SalesInvoices?&$select=Description,DueDate,InvoiceDate,InvoiceNumber,InvoiceTo,Journal,OrderDate,PaymentCondition,SalesInvoiceLines,Salesperson,SelectionCode,StatusDescription,TypeDescription,YourRef,Currency,AmountDiscountExclVat,AmountDiscount,Division",

    url = "https://exactconnectorb1.azurewebsites.net/api/KeyAfhEO ",

    headers = [klantToken = "", accessToken="def", refreshToken="def", method="GET", clientId="def", clientSecret="def", urlExact="def", #"Content-Type"="application/json"],

    payload = "{""ABC"": ""ACCEPT""}",

    response = Web.Contents("https://exactconnectorb1.azurewebsites.net/api/KeyAfhEO ", [Headers=[klantToken = "", accessToken="def", refreshToken="def", method="GET", #"Content-Type"="application/json"], Content=Text.ToBinary("{""ABC"": ""ACCEPT""}")]),

    jsonResponse = Json.Document(response),

    initialAccessToken = jsonResponse[accessToken],

    initialRefreshToken = jsonResponse[refreshToken],   

    // Define a function to handle HTTP requests with retries

    FnGetOnePage = (urlExact, accessToken, refreshToken) as record =>

    let

        response = Web.Contents("https://exactconnectorb1.azurewebsites.net/api/KeyAfhEO ", [Headers=[klantToken="def", accessToken=accessToken, refreshToken=refreshToken, method="REFRESH", clientId="1234", clientSecret="123", urlExact=urlExact, #"Content-Type"="application/json"], Content=Text.ToBinary("{""ABC"": ""ACCEPT""}")]),

        jsonresponse2 = Json.Document(response),

        d = jsonresponse2[d],

        data = d[results],

        accessTokenRes = d[accessToken],

        refreshTokenRes = d[refreshToken],

        next = try d[__next] otherwise null,

        res = [Data=data, Next=next, AccessToken=accessTokenRes, RefreshToken=refreshTokenRes]

    in

        res,

    fnCreateList = (list1 as list, url as text, accessToken as text, refreshToken) as list =>

    let

        res = FnGetOnePage(url, accessToken, refreshToken),

        combinedList = List.Combine({list1, if res[Data] <> null then res[Data] else {}}),

        resultRefresh = if res[Next] = null then

                    List.Transform(combinedList, each Record.AddField(_, "refreshToken", res[RefreshToken]))

                 else

                    combinedList,

        resultAccess = if res[Next] = null then

                  List.Transform(resultRefresh, each Record.AddField(_, "accessToken", res[AccessToken]))

                 else

                    combinedList,

        finalResult = if res[Next] <> null then Function.InvokeAfter(()=>@fnCreateList(resultAccess, res[Next], res[AccessToken], res[RefreshToken]),#duration(0,0,0,1)) else resultAccess

    in

        finalResult,

    GeneratedList = fnCreateList(Json.Document("[]"), initialUrl, initialAccessToken, initialRefreshToken),

    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"SalesInvoiceLines", "Description", "DueDate", "InvoiceDate", "InvoiceTo", "InvoiceNumber", "Journal", "OrderDate", "PaymentCondition", "Salesperson", "SelectionCode", "StatusDescription", "TypeDescription", "YourRef", "Currency", "AmountDiscountExclVat", "AmountDiscount", "Division", "accessToken", "refreshToken"}),

    #"ExpandSalesInvoiceLines" = ????????

     // Add an index column

    AddIndex = Table.AddIndexColumn(#"Expanded Column1", "Index", 1, 1, Int64.Type),

    // Sort by the index column in descending order

    SortRows = Table.Sort(AddIndex, {{"Index", Order.Descending}}),

    // Get the last value from the desired column

    accessTokenUpdate = if Table.RowCount(SortRows) > 0 then SortRows{0}[accessToken] else null,

    refreshTokenUpdate = if Table.RowCount(SortRows) > 0 then SortRows{0}[refreshToken] else null,

    headersPost = [klantToken = "", accessToken=accessTokenUpdate, refreshToken=refreshTokenUpdate, method="POST", clientId="def", clientSecret="def", urlExact="def", #"Content-Type"="application/json"],

    payloadPost = "{""ABC"": ""ACCEPT""}",

    responsePost = Web.Contents("https://exactconnectorb1.azurewebsites.net/api/KeyAfhandelingExactOnline ", [Headers=headersPost, Content=Text.ToBinary(payload)]),

    waardeUitkomstPost = responsePost[accessToken],

    TussentijdseMutaties = #"Expanded Column1",

    Grootboekmutaties = TussentijdseMutaties,

    jsonResponsePost = Json.Document(responsePost),

    GrootboekmutatiesUitkomst = Table.AddColumn(Grootboekmutaties, "Token", each jsonResponsePost, type text),

    #"Removed Columns" = Table.RemoveColumns(GrootboekmutatiesUitkomst,{"Token", "accessToken", "refreshToken"})

in

                #”Removed Columns”

In the line with all the question marks, I have a column with values like 

https://start.exactonline.nl/api/v1/bulk/salesinvoice/SalesInvoices(guid'1234abcd')/SalesInvoiceLine... 

and

https://start.exactonline.nl/api/v1/bulk/salesinvoice/SalesInvoices(guid'5678efgh')/SalesInvoiceLine...   

Via this guid, I should be able to retrieve the remaining columns. 
I know this is farfetched, but I hope someone knows which step I have to take to be alble to load the columns. 

[Anonymous · Accepted solution]

@Anonymous 
Thank you so much for your reply and answer! In between, I discovered that I could retrieve the remaining columns by loading in a table, with those columns (because there were also accessible via exact.online.nl, which I didn't know earlier on). So I could access the remaining columns and merge them with their ID on the original table via the code after the guid. 

However, since we are still in our beta phase, I can't apply the data in the report view. So I am now sure if the refresh and access token will be broken due to the merge. Therefore, I could be possible that I need your code after all. For now, the issue is solved. 

[Anonymous]

Hi @Anonymous ,

You can try something like below:

let
    ...
    ExpandedColumn1 = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"SalesInvoiceLines", ...}),
    // Function to fetch and parse nested data
    FetchAndParseNestedData = (SalesInvoiceLinesUrl as text) => let
        response = Web.Contents(SalesInvoiceLinesUrl, [Headers=[Authorization="Bearer " & accessToken]]),
        parsedJson = Json.Document(response),
        table = Table.FromRecords({parsedJson})
    in
        table,
    // Apply the function to each row
    ExpandedNestedData = Table.TransformColumns(ExpandedColumn1, {"SalesInvoiceLines", each FetchAndParseNestedData(_)}),
    ...
in
    ExpandedNestedData

You can adapt it to your requirements and data structure.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.