Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MissMintox
Helper I
Helper I

Handling empty rows/cells in JSON - Power BI Quering

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

Capture.PNG

 

 

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

 

Capture1.PNG

 

 

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.

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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
Super User
Super User

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. 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.