Query takes too much time to load in Power Pivot Data Model

[Ackbar-Learner]

Hi

I am using Power Query and a software key to feed in an API, which then returns me a token and then using that token, I can access the databases as set in the software documentation. I have converted that token into a function.

I have a case here where I can get Invoice IDs in an Invoices table and if I want to get the invoice lines, I need to feed in the Invoice ID each time in an API. To make things easier, I have created a query to automate the whole process so that I can get all invoice lines for all Invoice IDs inside one table and the query does not take much time to run inside Power Query.

The issue occurs when i need to load the data into a Power Pivot Data Model as it is iterating the functions all over again. Below is the M code I used to do this. I need some help to make this query load quickly in Power Pivot. I guess we should implement some code to make the iteration smoother. I have tried to use List.Buffer initially but I am not sure if I did it well or List.Buffer is not appropriate in my case. Here is the code:

let
    Source = FunctionToken("invoices"),
    Buffer = List.Buffer(Source),
    #"Converted to Table" = Table.FromList(Buffer, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"InvoiceId", "CustomerId", "CompanyId", "InvoiceNr", "InvoiceDate", "DueDate", "Currency", "VatIncl", "InvAmount", "VatAmount", "TotalAmount", "PaidAmount", "OpenSaldo", "StructuredCommunication", "PONumber", "AccountingYear", "CoContractor", "CreditRestriction", "CreditRestrictionPerc", "AdministrativeCost", "AdministrativeCostPerc", "Discount", "DiscountPerc", "FileId", "InvoiceMailed", "DirectDebit", "IsCreditNote"}, {"InvoiceId", "CustomerId", "CompanyId", "InvoiceNr", "InvoiceDate", "DueDate", "Currency", "VatIncl", "InvAmount", "VatAmount", "TotalAmount", "PaidAmount", "OpenSaldo", "StructuredCommunication", "PONumber", "AccountingYear", "CoContractor", "CreditRestriction", "CreditRestrictionPerc", "AdministrativeCost", "AdministrativeCostPerc", "Discount", "DiscountPerc", "FileId", "InvoiceMailed", "DirectDebit", "IsCreditNote"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"CustomerId", "CompanyId", "InvoiceNr", "InvoiceDate", "DueDate", "Currency", "VatIncl", "InvAmount", "VatAmount", "TotalAmount", "PaidAmount", "OpenSaldo", "StructuredCommunication", "PONumber", "AccountingYear", "CoContractor", "CreditRestriction", "CreditRestrictionPerc", "AdministrativeCost", "AdministrativeCostPerc", "Discount", "DiscountPerc", "FileId", "InvoiceMailed", "DirectDebit", "IsCreditNote"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "invoicelinesid", each "invoicelines/"&Number.ToText([InvoiceId])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"InvoiceId"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"invoicelinesid", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Result", each FunctionToken([invoicelinesid])),
    #"Expanded Result" = Table.ExpandListColumn(#"Added Custom1", "Result"),
    #"Expanded Result1" = Table.ExpandRecordColumn(#"Expanded Result", "Result", {"InvoiceId", "LineId", "LineNr", "LineGrootboekrekening", "LineDate", "LinePersonName", "LineDescription", "LineQuantity", "LineTariff", "LineAmount", "LineType", "LineVat", "LineNoVat"}, {"InvoiceId", "LineId", "LineNr", "LineGrootboekrekening", "LineDate", "LinePersonName", "LineDescription", "LineQuantity", "LineTariff", "LineAmount", "LineType", "LineVat", "LineNoVat"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Result1",{{"InvoiceId", Int64.Type}, {"LineId", Int64.Type}, {"LineNr", Int64.Type}, {"LineGrootboekrekening", Int64.Type}, {"LinePersonName", type text}, {"LineDescription", type text}, {"LineTariff", type number}, {"LineQuantity", Int64.Type}, {"LineAmount", type number}, {"LineType", type text}, {"LineVat", Percentage.Type}, {"LineNoVat", type logical}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","T"," ",Replacer.ReplaceText,{"LineDate"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"LineDate", type datetime}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"LineDate", type date}})
in
    #"Changed Type3"

Thanks in advance!

[lbendlin]

This is already too late. Look at the FunctionToken code instead.

[Ackbar-Learner]

This is the function token code

let
     // Step 1: Define the Software Key parameter
    softwareKey = "zzzzz",

    // Step 2: Define a function to obtain the token
    GetToken = () =>
    let
        url = "http://xxxxx/api/v1/token/"& softwareKey,
        response = Web.Contents(url),
        tokenResponse = Json.Document(response),
        token = tokenResponse[TokenGuid]
    in
        token,

    // Step 3: Obtain the token
    token = GetToken(),

    // Step 4: Define a function for API requests with token
    GetAPIData = (endpoint as text) =>
    let
        apiUrl = "http://xxxxx/api/v1/" & endpoint,
        response = Web.Contents(apiUrl, [
            Headers = [
                header = token,
                #"Content-Type"="application/json"
            ]
        ]),
        data = Json.Document(response)
    in
        data

in
    GetAPIData

 How to make this more efficient then?

Thanks for replying

[lbendlin]

You are missing the RelativePath part.

Do you need to request a new token for each call or can you re-use tokens?

[Ackbar-Learner]

I can re-use the same tokens. I have not done the relative path before. Can you help?

[lbendlin]

Please follow the documentation: Web.Contents - PowerQuery M | Microsoft Learn

[Ackbar-Learner]

It used to take 15 mins. Now it is around 4 mins 😀. I am writing the code below just in case someone  has another suggestion to bring down that 4 min. 

let
     // Step 1: Define the Software Key parameter
    softwareKey = "zzzzz",

    // Define the base URL
    baseUrl = "http://xxxxx/api/v1/",

    // Step 2: Define a function to obtain the token
    GetToken = () =>
    let
        response = Web.Contents(baseUrl, [
            RelativePath = "token/" & softwareKey
        ]),
        tokenResponse = Json.Document(response),
        token = tokenResponse[TokenGuid]
    in
        token,

    // Step 3: Obtain the token
    token = GetToken(),

    // Step 4: Define a function for API requests with token
    GetAPIData = (endpoint as text) =>
    let
        response = Web.Contents(baseUrl, [
            RelativePath = endpoint,
            Headers = [
                header = token,
                #"Content-Type"="application/json"
            ]
        ]),
        data = Json.Document(response)
    in
        data

in
    GetAPIData

Thanks again @lbendlin