Forum Discussion
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
and
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
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.
2 Replies
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 ExpandedNestedDataYou 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.
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.