Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
This is already too late. Look at the FunctionToken code instead.
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
You are missing the RelativePath part.
Do you need to request a new token for each call or can you re-use tokens?
I can re-use the same tokens. I have not done the relative path before. Can you help?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.