BI Analytics API request examples
This article provides example requests for working with the BI Analytics API. You can use them as a starting point for connecting Power BI or any other BI platform that supports REST APIs.
Depending on your requirements, you can choose one of the following examples:
-
Basic example — suitable for learning the API, testing the connection, and building simple integrations. It demonstrates the minimum steps required to authenticate, send a request, and retrieve data.
-
Advanced example — recommended for production environments and scheduled data refreshes. In addition to authentication and data retrieval, it includes API error handling, response validation, empty result handling, and automatic data transformation into a format suitable for Power BI.
For production deployments, we recommend using the Advanced example, as it provides more robust error handling and makes the integration easier to maintain.
|
Basic query example
let
BaseUrl = "https://<server_address>",
LoginJson =
Json.Document(
Web.Contents(
BaseUrl,
[
RelativePath = "api/bi/login.php",
Headers = [#"Content-Type" = "application/json"],
Content = Json.FromValue([
username = "YOUR_LOGIN",
password = "YOUR_PASSWORD"
]),
ManualStatusHandling = {400, 401, 403, 404, 500}
]
)
),
Token = LoginJson[access_token],
NodeId = LoginJson[user][node_id],
AnalyticsJson =
Json.Document(
Web.Contents(
BaseUrl,
[
RelativePath = "api/bi/analytics.php",
Query = [node = Text.From(NodeId)],
Headers = [
#"Authorization" = "Bearer " & Token,
#"Content-Type" = "application/json"
],
Content = Json.FromValue([
date_from = "2026-01-01",
date_to = "2026-12-31",
metrics = {
"veh_driving_dist",
"veh_fuel_cons",
"average_speed",
"veh_cars_counter"
},
source = "vehicle",
group_by = "source_day",
format = "flat"
]),
ManualStatusHandling = {400, 401, 403, 404, 500},
ExcludedFromCacheKey = {"Authorization"}
]
)
),
DataList = AnalyticsJson[data],
Tbl = Table.FromList(DataList, Splitter.SplitByNothing(), {"Column1"}),
Expanded = Table.ExpandRecordColumn(
Tbl,
"Column1",
{"source_id", "date", "veh_driving_dist", "veh_fuel_cons", "average_speed", "veh_cars_counter"}
)
in
Expanded
|
Advanced query example
It includes API error handling, response validation, and automatic conversion of the returned data into a Power BI table.
let
BaseUrl = "https://pilot-gps.com",
// --- API ERROR HANDLING ---
ApiJson =
(Response as binary, Context as text) as record =>
let
Status =
try Value.Metadata(Response)[Response.Status]
otherwise 200,
ParsedJson =
try Json.Document(Response)
otherwise
error Error.Record(
Context,
"API response is not valid JSON",
[
status = Status,
body = Text.FromBinary(Response)
]
),
ErrorMessage =
if Record.HasFields(ParsedJson, "error_description") then
ParsedJson[error_description]
else if Record.HasFields(ParsedJson, "msg") then
ParsedJson[msg]
else if Record.HasFields(ParsedJson, "errors") then
Text.Combine(List.Transform(ParsedJson[errors], each Text.From(_)), "; ")
else if Record.HasFields(ParsedJson, "error") then
Text.From(ParsedJson[error])
else
"API request failed",
CheckedJson =
if Status >= 400 then
error Error.Record(
Context,
ErrorMessage,
ParsedJson
)
else if Record.HasFields(ParsedJson, "success")
and ParsedJson[success] = false then
error Error.Record(
Context,
ErrorMessage,
ParsedJson
)
else
ParsedJson
in
CheckedJson,
// --- LOGIN ---
LoginResponse =
Web.Contents(
BaseUrl,
[
RelativePath = "api/bi/login.php",
Headers = [#"Content-Type" = "application/json"],
Content = Json.FromValue([
username = "LOGIN",
password = "PASSWORD"
]),
ManualStatusHandling = {400, 422, 429, 500}
]
),
LoginJson = ApiJson(LoginResponse, "BI Analytics login"),
Token = LoginJson[access_token],
NodeId = LoginJson[user][node_id],
NodeQuery =
if NodeId = null then
[]
else
[node = Text.From(NodeId)],
// --- ANALYTICS REQUEST ---
AnalyticsResponse =
Web.Contents(
BaseUrl,
[
RelativePath = "api/bi/analytics.php",
Query = NodeQuery,
Headers = [
#"Authorization" = "Bearer " & Token,
#"Content-Type" = "application/json"
],
Content = Json.FromValue([
date_from = "2026-01-01",
date_to = "2026-12-31",
metrics = {
"veh_driving_dist",
"veh_fuel_cons",
"average_speed",
"veh_cars_counter"
},
source = "vehicle",
group_by = "source_day",
format = "flat",
page = 1,
per_page = 100
]),
ManualStatusHandling = {400, 422, 429, 500},
ExcludedFromCacheKey = {"Authorization"}
]
),
AnalyticsJson =
ApiJson(
AnalyticsResponse,
"BI Analytics request"
),
// --- VALIDATE DATA ---
DataList =
if Record.HasFields(AnalyticsJson, "data") then
AnalyticsJson[data]
else
error Error.Record(
"BI Analytics response",
"Response does not contain data field",
AnalyticsJson
),
// --- CONVERT TO TABLE ---
Tbl =
if List.IsEmpty(DataList) then
#table(
{
"source_id",
"date",
"veh_driving_dist",
"veh_fuel_cons",
"average_speed",
"veh_cars_counter"
},
{}
)
else
Table.FromList(
DataList,
Splitter.SplitByNothing(),
{"Column1"}
),
Expanded =
if List.IsEmpty(DataList) then
Tbl
else
Table.ExpandRecordColumn(
Tbl,
"Column1",
{
"source_id",
"date",
"veh_driving_dist",
"veh_fuel_cons",
"average_speed",
"veh_cars_counter"
}
),
#"Changed Types" =
Table.TransformColumnTypes(
Expanded,
{
{"source_id", Int64.Type},
{"date", type date},
{"veh_driving_dist", type number},
{"veh_fuel_cons", type number},
{"average_speed", type number},
{"veh_cars_counter", Int64.Type}
}
),
#"Sorted Rows" =
Table.Sort(
#"Changed Types",
{
{"source_id", Order.Ascending},
{"date", Order.Ascending}
}
)
in
#"Sorted Rows"
|