Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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, ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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.