Recently when working with a JSON file in Power BI I was faced with a problem. I had a field in the JSON file that could contain one or many records in it. The built-in parsing identified the rows as being a mixture of records and lists (there is no native way to expand only the lists and then records). Because of this, it wasn’t able to extract the values from the column.

Here is an example of what the data I am trying to parse looks like in Power BI:

This lead to some frantic googling trying to hit a deadline and I stumbled across this StackOverflow post. It uses the M function Value.Is to check the type of record in a column. The code in the StackOverflow answer uses this function to only return records of a given type, letting you create a custom column that only contains lists or records.

Here is an example of the code that was used in the custom column:

If Value.Is([Column], type record) then [Column] else null

Armed with this code, we can create two columns containing only lists or records respectively:

With the two types split out, we can expand the lists into records and make this whole mess much easier to deal with:

Now that all the rows have the same type, we will combine them into a single column to make expanding the records easier and more efficient. I did this by using a conditional column to check for nulls in Records and replace them with Lists:

(Note: Power BI uses the text value of “null” to represent a true null, not a blank in the Value field)

Now that all the values are records in a single column, it’s easy to just expand the values:

KiZAN is a Microsoft National Solutions Provider with numerous gold and silver Microsoft competencies, including gold data analytics. Our primary offices are located in Louisville, KY, and Cincinnati, OH, with additional sales offices located in Tennessee, Indiana, Michigan, Pennsylvania, Florida, North Carolina, South Carolina, and Georgia.