Handling empty rows/cells in JSON - Power BI Quering

[MissMintox]

Searched everywhere but couldn't find something similar to this.

I got a table as below (a glimpse of it):
customDimensions is the column that pulls the data in JSON format. However, there are some empty cells (as seen below) in the column

I tried to Parse the whole column into JSON but it is returning me errors in the blank columns as below:

Therefore I tried an alternative way of first replacing those blank cells in the customDimensions to a generic JSON format input as {"Type":"Unhandled"} and then Parsed the column into JSON. This successfully got rid of the errors, but it is returning error while refreshing the data!

So, How do I handle this in a different way? I want to replace those blank cells as either "null" or any keyword, as long as I do not miss the count of those blank cells.

[v-juanli-msft]

Hi @MissMintox

Since you could Parse the rows in column without empty into JSON, but it returns error for the empty row, you can first parsing a json column, then handle the error by "Replace errors".

Reference how to handle error in power bi

error handling in power query

Best Regards

maggie

[ImkeF]

You can use the error-handler and "wrap" the parsing of the JSON into: 

try <ParseJson> otherwise []

This returns an empty record for the empty fields.