Dataflow Gen2-To read a JSON data column n load multiple tables named after parent keys

[PrachiShepunde]

I need to read a column having JSON format using Dataflow Gen2. It is around 1000 lines of code multiple attributes. I want to store each KeyValue data in seperate table. I do not see option for the same in Transformation. Need help to complete this activity.

[PrachiShepunde]

Thanks! which would be better option? Doing this transformation in the Notebook or the Dataflow?

[Ilgar_Zarbali · Accepted solution]

In Dataflow Gen2, there’s no direct option to automatically split a JSON column into multiple tables based on parent keys, but you can do it manually using Power Query. First, use Json.Document to parse the JSON column, then create separate queries for each key by drilling into the relevant parts of the JSON. Each of these queries can be expanded into table format and marked as output tables individually. This way, each parent key's data is loaded into its own table.

[v-lgarikapat]

Hi @PrachiShepunde ,

Thanks for reaching out to the Microsoft fabric community forum.

You're working with Dataflow Gen2 in Fabric and want to:
Read a column containing complex JSON (large, with multiple parent-child key structures).
Dynamically split the JSON into multiple tables, one for each parent key, named accordingly.
You are not seeing a transformation option in Dataflow Gen2 that lets you do this directly.

What You Can Do
Unfortunately, Dataflow Gen2 transformations are limited when it comes to dynamic schema generation or creating multiple outputs dynamically (like Power Query or Data Factory pipelines can). But here’s how you can work around this:
Approach (Semi-automated, using Power Query in Dataflow Gen2)
Load Your Source Table
Start a Dataflow Gen2.
Load the table that contains the JSON column.
Parse the JSON Column
Let’s assume your column is called jsonData.
In a new custom column, use:
= Json.Document([jsonData])
This will parse the JSON string into a record (or list, depending on structure).
If jsonData is already parsed, skip this step.
Convert to Table and Expand
If the root is a record:
= Record.ToTable([ParsedJson])
You’ll now get two columns: Name (parent key) and Value.
You can now filter each parent key, and expand their nested values into rows.
Manually Create a Table Per Parent Key
Sadly, you must manually create a query per top-level key (e.g., "Employee", "Department", etc.).
For each key:
Filter Name = "Employee"
Expand the [Value] column
Promote headers, clean data
Rename the query as Employee
Repeat for other keys. Each query becomes a table output in the dataflow.

Create your first Microsoft Fabric dataflow - Microsoft Fabric | Microsoft Learn

If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[PrachiShepunde]

Thanks for the solution, I will give it a try and reply back

[PrachiShepunde]

@nilendraFabric please help on this if possible