Dynamic Column Headers in Table Visual Based on JSON Data

[Nikhil_ZS]

I have a table visual on a report page with multiple columns, one of which is QC ID.

In the data model, QC ID is related to another fact table that contains a column in JSON format. This JSON column holds multiple comma-separated values.

I am parsing this JSON column in Power Query and expanding it into separate rows and columns.

Requirement

1. When I select a particular row in the main table visual (based on QC ID), the corresponding parsed JSON values from the related detailed table should be displayed in another table visual on the same page.

2. Additionally, the structure of the JSON differs for each QC ID. This means:

   • The keys/attributes inside the JSON can vary by row.

   • Therefore, the column headers in the second table visual need to change dynamically based on the selected QC ID and display only the relevant fields for that record.

In short, I need the second table visual to:

• Filter dynamically based on the selected QC ID.

• Dynamically adjust its columns based on the JSON structure for that specific record.

Is this achievable in Power BI, and if so, what would be the recommended approach?

[v-priyankata]

Hi @Nikhil_ZS 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@danextian @Jaywant-Thorat Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[danextian]

Hi @Nikhil_ZS 

This isn’t currently possible. Column headers in a table or matrix visual do not update dynamically. One approach is to use field parameters, but they are entirely manual — fields must be explicitly added when the parameter is set up, and any new fields added later also need to be added manually. Another approach is to unpivot your table, placing JSON keys in one column and their values in another; you’ll need to use a matrix visual to display it like a table, though this can increase refresh overhead.

Please see the attached pbix.

[Nikhil_ZS]

Hi @danextian 
Thanks for your reply. To fulfill my requirement, table or matrix - anyone would be fine, but do you see any issue with Matrix visual?
If you need sample data with pbix file, you can access here 

Just the ask is, if this is doable and achievable, and the main concern is the future values in JSON columns are unknown.
Please take a look at the attached pbix and let me know in what best possible way we can display this.

[danextian]

Thigns to bear in mind when using matrix:

Sorting by row category must respect the hierarchy. Lower level headers are sorted within the context of hte upper level headers.

Column headers resulting from using a dimension cannot be sorted individualy like you can with a table.

And, unpivoting is done in the query editor so pbix alone is not enough as the data needs to be transformed.

[Nikhil_ZS]

@danextian Oops! I missed that I had done transformation. To help me on this, I have shared the sample data here 

Summary - is the table which would be driving table

Details- is the table which has JSON values in a column
Please let me knoe if you need any clarification.

[danextian]

The link is the same as initially posted

[Nikhil_ZS]

This will definitely work.

You dont need all these tables @danextian 

Just refer to the two csv files I shared with you, and then you can just start in a fresh new pbix file.

[danextian · Accepted solution]

In the image below,  you'll  need to add an index column to uniquely identify each row values in matrix are always aggregated.

Please see the attached pbix.

[Nikhil_ZS]

Thanks a lot @danextian 
It seems to be working and the only thing I want to check with you, in my actual data the details table has 1.3 Lakhs rows which further grew up to 39 Lakhs after the transformation.
Row count doesn't seem to be a big problem, the part which I am concerned of is, the transfomation step took almost 1.5 hrs to load the data after transformation.
Is this expected or can we improve on this?

[danextian]

You have too big of a data. Ideally, these transformations should be done at the source.

[Jaywant-Thorat]

Hi @Nikhil_ZS 

Yes. It is achievable, but not 100% dynamically in the way you expect.

Power BI does NOT support truly dynamic column headers in a table visual based on row selection.
Columns must exist in the data model.

However, here is the correct architecture.

What You Want

When selecting QC ID:

• Visual 2 filters by that QC ID (Easy)
• Columns change based on JSON keys (Not directly supported)

Recommended Professional Approach
Step 1 — Normalize JSON (Best Practice)

Instead of expanding JSON into dynamic columns, transform it into key-value structure in Power Query:

Convert JSON to:

QC ID              Attribute              Value
1001                 Length                  50
1001                 Width                   20
1002                 Color                    Red

This is the correct data modeling pattern.

Step 2 — Use Table Visual Like This

Put in Visual 2:

• Rows >>> Attribute
• Values >>> Value

Now when user clicks QC ID in Visual 1:

• Visual 2 filters automatically
• Only relevant attributes appear
• No blank columns
• Fully dynamic per record

Why Dynamic Columns Won’t Work

Power BI:

• Cannot create/remove columns dynamically

• Cannot change headers based on slicer

• Visual schema is fixed at design time

Even calculation groups or DAX cannot dynamically add/remove columns.

=================================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
LinkedIn: https://www.linkedin.com/in/jaywantthorat/
Join #MissionPowerBIBharat: https://tinyurl.com/JoinMissionPowerBIBharat
#MissionPowerBIBharat
LIVE with Jaywant Thorat