cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Multi dimensional JSON file to one flat table

I have a multi-dimensional table from a json source that I'm trying to convert into one flat table. I can expand the columns up to a point, and still have tables within rows as in the below: 

 

Example 1.PNG

 

The problem I'm having is that when I expand these tables e.g. in the parent column, it opens them up as another seperate table. See example below: 

 

Capture 3.PNG

 

Is there a way to automatically expand these tables within the original query instead of having each table expand into a new seperate table view? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Multi dimensional JSON file to one flat table

Hi Lydia

 

The end solution that worked was to have the json data coming through via an API "flattened" by our developer to provide a simpler table for analysis. I ended up not working with the issue in Power BI as there was another solution in code.  

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: Multi dimensional JSON file to one flat table

@LJR,

ImkeF has provided a detailed solution about this issue in the similar thread below, please add a custom column in your query following her guide and then expand the custom column.

http://community.powerbi.com/t5/Desktop/Expand-column-where-not-all-records-are-tables/td-p/79060

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Multi dimensional JSON file to one flat table

Thanks Lydia. I tried this solution but got an error message when trying to apply it:

 

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=Table
Type=Type

 

Image 10.PNG

Highlighted
Microsoft
Microsoft

Re: Multi dimensional JSON file to one flat table

@LJR,

Could you please post the JSON code so that I can  test?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Multi dimensional JSON file to one flat table

Hi Lydia

 

The end solution that worked was to have the json data coming through via an API "flattened" by our developer to provide a simpler table for analysis. I ended up not working with the issue in Power BI as there was another solution in code.  

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors