cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mehaboob557 Resolver IV
Resolver IV

Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Hi All,

 

I am using REST API to get the JSON data. But, i am very new to JSON in powerbi.

 

I have some how used  some internet surf and Navigvated in LIST then Done tranform to Tables then Expand to rows and then LIST is coming e,t,c.

 

I followed this https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/

 

But this is a normal JSON. mine is multidimentional JSON.

 

So i need your Help how can i achieve this.

 

I am shareing some sample JSON data here

 

Please suggest me how can i make into tables.. While making in tables, if it divide into tables how can i do the relationship.

 

Please share screen shot how the flow would be if you can, so that it may be easier to understand.

 

Thanks a ton in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Community Champion
Community Champion

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Below the results from my efforts.

 

Before you start creating queres to parse he JSON file, make sure that your data is a representative example of your real data, otherwise you might be missing some queries:

e,g, the example file includes 2 examples with nested lists that are empty, so for these empty lists, no queries/tables could be created.

 

Parsing a JSON file requires a structured and very disciplined approach (e.g. you need to include steps to remove empty values, even if there are no empty values in your sample data).

The general steps are described in this document.

 

This document was used in creating below 31 (!) minutes video to illustrate the parsing proces and explain the 2 custom functions that are used and included in the Power BI Desktop files:

Start file

Finished file

 

Custom function Table_ConvertColumnTypes was created in close cooperation with Colin Banfield in this topic on Technet.

 

 

I can imagine you have lots of questions about the parsing process: please absorb all information above first, as many of your questions will be answered. Of course: if any questions will be left, don't hesitate to ask them.

 

In any case I will be looking forward to your feedback, 

 

Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
Microsoft
Microsoft

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Hi @mehaboob557,

 

For nested JSON data, please try suggestions in below similar threads:

Nested JSON and never end Records

Multi-Dimensional Json file to Table

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MarcelBeug Community Champion
Community Champion

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Just to inform you that I'm busy with this topic.

It is interesting and rather complex.

 

Basically there are 2 approaches:

1. Create all steps and tables manually, resulting in code that will only work as long as the data structure doesn't change.

2. Use some functions for data typing and expanding embbeded (lists of) records, resulting in a more dynamic solution.

 

I will share the results (pbix file and video) once these are ready, maybe later today, maybe tomorrow, maybe even later this week.

 

The complexity is illustrated by this topic on Technet, in which Colin Banfield and I have been creating a function to dynamically determine data types, which will be used in the second approach mentioned above.

Specializing in Power Query Formula Language (M)
mehaboob557 Resolver IV
Resolver IV

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Thank you.

 

I will be waiting for that 🙂

mehaboob557 Resolver IV
Resolver IV

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Hi @MarcelBeug,

 

I am waiting for your reply.

 

Please help me how can i use Multidimention JSON to table and XML to table

MarcelBeug Community Champion
Community Champion

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Hi @mehaboob557

 

Still working on this.

I will have my results ready later today or tomorrow at the latest, so for you either tomorrow or Friday Start Of Business.

 

Rest assured: your patience will be rewarded.

Specializing in Power Query Formula Language (M)
mehaboob557 Resolver IV
Resolver IV

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Dear @MarcelBeug,

 

Thanks for the response.

 

I will wait for that . 🙂

MarcelBeug Community Champion
Community Champion

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Below the results from my efforts.

 

Before you start creating queres to parse he JSON file, make sure that your data is a representative example of your real data, otherwise you might be missing some queries:

e,g, the example file includes 2 examples with nested lists that are empty, so for these empty lists, no queries/tables could be created.

 

Parsing a JSON file requires a structured and very disciplined approach (e.g. you need to include steps to remove empty values, even if there are no empty values in your sample data).

The general steps are described in this document.

 

This document was used in creating below 31 (!) minutes video to illustrate the parsing proces and explain the 2 custom functions that are used and included in the Power BI Desktop files:

Start file

Finished file

 

Custom function Table_ConvertColumnTypes was created in close cooperation with Colin Banfield in this topic on Technet.

 

 

I can imagine you have lots of questions about the parsing process: please absorb all information above first, as many of your questions will be answered. Of course: if any questions will be left, don't hesitate to ask them.

 

In any case I will be looking forward to your feedback, 

 

Specializing in Power Query Formula Language (M)

View solution in original post

mehaboob557 Resolver IV
Resolver IV

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Thank you so much @MarcelBeug  sir.

 

You are a great Mentor and best person i have ever communicated with.

 

I wish i could have worked under you to gain your more knowledge.

 

Last but not the least "YOU ROCKZ IN POWER BI".

 

 

Guipsot
Regular Visitor

Re: Unable to figure out how can i make Multidimension JSON (Nested JSON) into table

Hi,

 

I find your video quite interesting, thanks for sharing. The links to the document and functions seem to be broken, could you re share them?

 

Cheers,

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors