How to handle a column error and avoid loosing all previously calculated data

[stephdurand]

Hello everyone,

I have built an Excel file that gets data from REST API using the web.contents function. The result of the API (Json) is developed in order to have some of the content of the data in dedicated columns

It sometimes happen that the API fails (400 or 500 return code) and in this situation, the develop function fails as the content of the column is not a record (If the return code is not 200, the column is set with the http return code). I managed to filter the rows based on the non expected values (i.e. 400 or 500) but this is not the most efficient solution as if any other http code is returned, I'll loose again the data up to the point of failure.

My Excel file is currently running hence not possible to show any content but I will as soon as it finished (hopefully with sucess this time).
Maybe someone has already a hint on how to manage this situation of non expected column type...

Thanks in advance!

[PhilipTreacy]

Hi @stephdurand 

Hard to give advice without seeing the code and data but you might be able to use try ... otherwise to check what the API returns and take appropriate action.

This might explain how to use try ... otherwise

IFERROR in Power Query Using TRY OTHERWISE • My Online Training Hub

Regards

Phil