Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 months ago
Solved

Dynamic Column Headers in Table Visual Based on JSON Data

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?

Need Help
Show and Tell
Tips and Tricks

11 Replies

  • danextian's avatar
    danextian
    Super User
    6 months ago

    Hi Anonymous 

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 months ago

      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's avatar
        danextian
        Super User
        6 months ago

        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.

  • Jaywant-Thorat's avatar
    Jaywant-Thorat
    Super User
    6 months ago

    Hi Anonymous 

    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

  • v-priyankata's avatar
    v-priyankata
    Community Support
    6 months ago

    Hi Anonymous 

    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.