Parsing Json File Giving Unexpected Results

[sjrrkb123]

I am trying to import a .json file using the Power BI .json connector and am running into some issues. 

Everytime I import the .json file I get this as a result:

I had hoped it would natively expand the lists and records into a usable file. I am dreading having to do this one by one. 

Any thoughts on how to proceed?

[sjrrkb123]

It appears your line of code worked @miguel . I suspect PBI did not know how to handle the fact that some parts of the json are multiple levels deep while others are single level. Thanks!

[Anonymous]

Hi @sjrrkb123 ,

For your scenario, here is a similar thread for your reference:

Transforming json with power query (mix of list and record in a single column)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[miguel · Accepted solution]

Hey!

It really depends on your json and how it's structured.

What's inside of those lists? is it effectively a list of records? do those records have the same or quite a similar schema to the records that are shown in your screenshot?

If the latter is true, then you can try and transform the record values in your column to be a list of records.

Basically create a new columns using the code below:

if Type.Is( Value.Type([ColumnNameGoesHere]), type record) then {[ColumnNameGoesHere]} else [ColumnNameGoesHere]

 That new column would have a single type which should be the list which you can later expand and then later expand the records. 

But in the end it all depends on how your json is structured