❗
Warning:
This article may contain outdated information.
We do no longer provide support or documentation for PowerBI use.
General API documentation is available.
This article may contain outdated information.
We do no longer provide support or documentation for PowerBI use.
General API documentation is available.
Login
The ISPnext API required the user to authenticate using a Bearer token. This token can be fetched from the API by creating a function in PowerBI:
The code for this function:
() => let
url = "https://{URL}/API/auth/login",
jsonBody = Json.FromValue([u="{USERNAME}", p="{PASSWORD}"]),
response = Web.Contents(
url,
[
Content = jsonBody,
Headers = [#"Content-Type" = "application/json"]
]
),
result = Json.Document(response)[accessToken]
in
result
Executing queries
Using this function, other queries can be executed on the ISPnext API by creating other queries in PowerBI:
The code for this (example) Contract function is as follows:
let
url = "https://XXX.vendorlink.nl/API/generic/execute",
body = "{
dataname: ""Contracts"",
page: 1,
pagesize: 10,
parameters: {
archived: 0,
my: 0,
concept: 0
}
}",
Source = Json.Document
(
Web.Contents(url,
[
Headers = [Authorization = "Bearer " & #"GET access token"()],
Content = Text.ToBinary(body)
]
)
),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
{"reference", "contractId", "vendorReference", "vendorName", "contractNumber"},
{"reference", "contractId", "vendorReference", "vendorName", "contractNumber"})
in
#"Expanded Column1"