Forum Discussion

Badrul_IO's avatar
Badrul_IO
New Member
3 years ago

How to deal with a null result from an API endpoint?

I have this line in my M-query:

result = Json.Document(Web.Contents(apiUrl , options)),

 

which then gets converted to a table:

#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

 

Sometimes the result returns data in which case everything is good. Sometimes it will be empty in which case result appears to = null (from what I can see).

So how can I modify the first line in order to be more resiliant - can I somehow do an If null then 0 or something?

Need Help

1 Reply

  • MatthRichardsUK's avatar
    MatthRichardsUK
    Resolver I
    3 years ago

    You can use the Table.FromList function with the optional missingField parameter. When the result list is empty or null, you can specify a default value to replace the missing values in the resulting table.

    For example, to replace missing values with 0, you can use the following code:

    #"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error, 0)

    This will convert the result list to a table, and if the list is empty or null, the resulting table will contain a single row with a single column of value 0.


    Alternatively, you can use the Table.FillDown function to fill missing values in the table with the value from the previous row. For example:

    #"Filled Down" = Table.FillDown(#"Converted to Table", {0})
    This will fill any missing values in the first column of the table with the value from the previous row.