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

[mehaboob557]

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.

[v-yulgu-msft]

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

[MarcelBeug]

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.

[mehaboob557]

Hi @MarcelBeug,

I am waiting for your reply.

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

[MarcelBeug]

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.

[mehaboob557]

Dear @MarcelBeug,

Thanks for the response.

I will wait for that . 🙂

[MarcelBeug · Accepted solution]

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, 

[Anonymous]

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,

[mehaboob557]

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

[mehaboob557]

Thank you.

I will be waiting for that 🙂