We have a NoSQL Database in Azure Cosmos DB. We also have a Synapse connector to use this database with PowerBi.
This allows us to use a simple SQL script to fetch the data in PowerBi. Something like:
SELECT logs.Field1, logs.Field2 from logs
The data is mostly structured, but we recently added a new field (say Field3) to the new documents in cosmos.
But now, if we run this:
SELECT logs.Field1, logs.Field2, logs.Field3 from logs
We get an error saying that the column name is invalid.
How should we fetch this field using the SQL script in Power Bi? We want a NULL value when the document does not contain this new field.
In cosmos, we have the IS_DEFINED function built-in the SQL language, and we can use it to query unstructured data using SQL. But this does not work in the PowerBi query editor (I believe it is a Synapse problem). The error reads: IS_DEFINED is not a built in function.