Nested JSON (Complex) to Power Query, Avoid row duplication

Dear Experts,

For a while, I’m trying to figure it out, but getting duplication in the row that case wrong results

I wish someone from DNA Experts could help, I almost watched 10s of videos, and all what I’ve seen on the internet is simple and not in real work.

Here is a sample JSON file, I want to flatten it for further analysis

The main issue comes when expanding the nested objects to new rows (products and options)

API JSON.pbix (14.6 KB)
API JSON.json (178.3 KB)

I’m sure some of our experts will help

Hi @mahmoud.algindy ,

While you are waiting for the answer you can in Power Query go to expand


and pick columns that you want.

You have only nested product options - but you have product_id - so maybe you can put that in new Lookup table if it is suitable and expand with 2 level below.

Good luck.

Thanks, @mspanic

I tried that and got unnecessary duplicate rows, in which nothing in the data can be used as logic to remove duplicates.

The solution will be done thru using some M code in a custom column(s) like
Table.FromColumns and etc…

I also tried but did not succeed, I watched @Melissa youtube and tried my best to follow but in the video the desired to extract a certain field, in my case I need to extract several columns to be able to calculate the Net Sales correctly.

@mahmoud.algindy Is there a possibility that in future there might be new fields that will have nested structures or the current fields that only have text right now will have nested structures i.e. Records or List etc?

1 Like

Thanks, @AntrikshSharma for your response
yes, I might add more columns later, but the current columns are enough to calculate the Sales.

Appreciated your usual support

Here is an updated JSON with more columns
response.json (155.6 KB)

Bumping this post for more visibility from our experts and users.

1 Like

Hi @mahmoud.algindy

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the expert’s and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

I will be closing this thread since it has been open for quite a while now. I suggest you open another thread containing the updated details so our experts can help you much better.

Thank you!

I wonder again, nothing of my question are solved, I was thinking the subscription to DNA Enterprise will help?

My questions are a real-world issues facing everyday, I am really sad!

Hi @mahmoud.algindy

We’re happy to help you with your query.

However, we hope that you’ll be patient as this isn’t an on-demand forum.

Our users and experts will do as much as reasonable to help, but if there is some learning to be done, you may be directed to relevant tutorials to learn this yourself rather than having answers continuously delivered to you.

This is indicated inthe how-to guide on using the forum-** How To Use The Enterprise DNA Support Forum