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

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

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)
8 REPLIES 8
Community Support Team
Community Support Team

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

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 Member
Member

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

Thank you.

 

I will be waiting for that Smiley Happy

mehaboob557 Member
Member

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

Super User
Super User

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 Member
Member

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 . Smiley Happy

Super User
Super User

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)
mehaboob557 Member
Member

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".