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://XXX.vendorlink.nl/API/auth/login",
body = "{
u:""username"",
p:""password""
}",
Source = Json.Document(Web.Contents(url
,[Headers=
[#"Authorization" = "Basic"],
Content = Text.ToBinary(body)
]
)),
accessToken = Source[accessToken]
in
accessToken
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"