Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JohanvA
Resolver I
Resolver I

API expand column

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. 

 

1 ACCEPTED SOLUTION
JohanvA
Resolver I
Resolver I

@v-yifanw-msft 
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. 

View solution in original post

2 REPLIES 2
JohanvA
Resolver I
Resolver I

@v-yifanw-msft 
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. 

v-yifanw-msft
Community Support
Community Support

Hi @JohanvA ,

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors