Forum Discussion
Solved
How to deal with NULL being returned from an API end point?
I have the M code below where I read data from an API endpoint. However there are cases where this endpoint will not return any values so result = null.
How can I deal with this null so that I end up with an empty table that has the right column headers (ideal solution) or just something that doesn't crash?
let
apiUrl = "https://xxx/registration/users",
token = AccessToken,
options = [Headers=[#"Authorization"="Bearer " & token ]],
result = Json.Document(Web.Contents(apiUrl , options)),
#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"userStatus", "role", "count", "date"}, {"Column1.userStatus", "Column1.role", "Column1.count", "Column1.date"}),
RenameColumns = Table.RenameColumns( #"Expanded Column1", List.Zip(
{
Table.ColumnNames( #"Expanded Column1"),
List.Transform(Table.ColumnNames( #"Expanded Column1"), each Text.AfterDelimiter( _, ".") )
} ) )
in
RenameColumns
Ok I think I've managed to solve my issue:
let
apiUrl = "https://xxx/registration/users",
token = AccessToken,
options = [Headers=[#"Authorization"="Bearer " & token ]],
r = Json.Document(Web.Contents(apiUrl , options)),
result = if r= null then #table(
{ "Column1"}, // defines column names
{} // returns no rows
) else Table.FromList(r, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(result, "Column1", {"userStatus", "role", "count", "date"}, {"Column1.userStatus", "Column1.role", "Column1.count", "Column1.date"}),
RenameColumns = Table.RenameColumns( #"Expanded Column1", List.Zip(
{
Table.ColumnNames( #"Expanded Column1"),
List.Transform(Table.ColumnNames( #"Expanded Column1"), each Text.AfterDelimiter( _, ".") )
} ) )
in
RenameColumns
1 Reply
Ok I think I've managed to solve my issue:
let
apiUrl = "https://xxx/registration/users",
token = AccessToken,
options = [Headers=[#"Authorization"="Bearer " & token ]],
r = Json.Document(Web.Contents(apiUrl , options)),
result = if r= null then #table(
{ "Column1"}, // defines column names
{} // returns no rows
) else Table.FromList(r, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(result, "Column1", {"userStatus", "role", "count", "date"}, {"Column1.userStatus", "Column1.role", "Column1.count", "Column1.date"}),
RenameColumns = Table.RenameColumns( #"Expanded Column1", List.Zip(
{
Table.ColumnNames( #"Expanded Column1"),
List.Transform(Table.ColumnNames( #"Expanded Column1"), each Text.AfterDelimiter( _, ".") )
} ) )
in
RenameColumns