Tag Archives: JSON

Zero Rows Can Bite (part 1): The Mysterious Missing Column

• , , , , ,

Your Power Query expression is happily skipping along, fetching data from a web API. Happily, that is, until one day its refreshes mysteriously die with the complaint that “column 'Column1' of the table wasn't found“. You haven’t changed any M code. You’ve verified that no schema changes occurred on the external web service. How, then, could a column come up missing?

Power Query error message - Expression.Error: The column 'Column1' of the table wasn't found. Details: Column1

Or, maybe it’s not a missing column, but rather your fetch data code starts outputting an unexpected table row with a null value in each column. You manually try the web API using a tool like Postman or Insomnia and don’t find any all-null objects in the API’s response. Where is this all-null table row coming from?

Both of these unexpected occurrences potentially stem from the same underlying cause. As common M code patterns tend to not properly handle this situation, it is possible (even probable!) that M code you use may leave you susceptible to being bitten by one or the other of these bugs.

Continue reading →

Recursive Polymorphic Deserialization with System.Text.Json

• ,

Goal: Deserialize a nested JSON structure, where the objects instantiated are not instances of the given abstract base type but rather are of the appropriate derived types.

In other words, if the specified destination type is AbstractBaseTpe, which should be returned is not an instance of that class but rather an instance of ChildTypeA or ChildTypeB or GrandchildType1, etc., as appropriate. This polymorphic deserialization behavior should hold true at the root level (that is, for the type directly returned by JsonSerializer.Deserialize<AbstractBaseType>(…)), as well as recursively for nested properties (that is, anytime the destination property is of type AbstractBaseType, the object instantiated for the property should be of the appropriate derived type, like ChildTypeA, etc.).

Hopefully, one day support for something along these lines will be built into System.Text.Json. However, for now, when we need it, we have craft our own solution (or borrow one from someone else).

In my case, the other day I needed a simple solution for polymorphic deserialization. Performance wasn’t critical. Just something simple that worked.

Continue reading →