Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
I'm new with the M formula language. Currently I have a function that, when invoked, is meant to return an Employee ID column and an Employee Name column. But if the API returns null, it causes an Expression.Error. I tried fixing this by using try...otherwise:
let
Source = GetPeople("API-XXXXX", null, null),
ErrorHandler = try [Source] otherwise #table({}, {})
in
ErrorHandler
The GetPeople function was already created, and requires the API key for input.
let
Source = (apiToken as text, optional endCursor as text, optional data as list) =>
let
endCursor = if endCursor is null then "" else endCursor,
query = "{
""query"": ""
{
results: PeopleOnSiteHistory(First: 2, After: \""" & endCursor &"\"") {
PageInfo {
HasNextPage
EndCursor
}
Items {
PersonId
PersonName
}
}
}
""
}",
JSON = Web.Contents("https://cloud.3dsafety.com.au/graphql",
[
Headers = [#"X-API-Key"=apiToken, #"Content-Type"="application/json"],
Content = Text.ToBinary(query)
]
),
Source = Json.Document(JSON),
pageInfo = Source[data][results][PageInfo],
items = Source[data][results][Items],
appendedData =
if pageInfo[HasNextPage] = true and data is null then
List.Combine({{}, items})
else List.Combine({data, items}),
output =
if pageInfo[HasNextPage] = true then
@GetPeople(apiToken, pageInfo[EndCursor], appendedData)
else
Table.FromList(appendedData, Record.FieldValues, {"PersonId", "PersonName"})
in
output
in
Source
Unfortunately, even if there is data to return, the try...otherwise construct seems to force it to evaluate to the exception's result, which is a blank table.
if Source is table then Source else #table(0,{})
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!