Login
The VendorLink 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 VendorLink 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"