How to Display JSON Column as Expandable Dropdown in a Power BI Table Visual?

[Nazdac2024]

Hi all 
I have the follwoing JSON column in one of tables :
{"EventId":12345,"EventType":"Error","IsCritical":1,"StartTime":"2023-10-01T12:00:00","EndTime":"2023-10-01T13:30:00","DurationMinutes":90}

This column is representd in a table visual in the report side ..
Is their is a way to allow the user to collapse and expalnd it .. so by clicking an arrow or + for example , he can convert the column to be like this :

-- The JSON column contains null values ( it might be imprtant !! ) 

-- I am also open to use other unstandard free visuals 🙂 

Thanks in advance 

[Anonymous]

Hi, @Nazdac2024 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding your issue, you can try the following two solutions:

First, both solutions require you to convert the JSON to a table type and unpivot the table:

let
    Source = Json.Document("{""EventId"":12345,""EventType"":""Error"",""IsCritical"":1,""StartTime"":""2023-10-01T12:00:00"",""EndTime"":""2023-10-01T13:30:00"",""DurationMinutes"":90}"),
    Table = Table.FromRecords({
        [EventId = Source[EventId], EventType = Source[EventType], IsCritical = Source[IsCritical], StartTime = Source[StartTime], EndTime = Source[EndTime], DurationMinutes = Source[DurationMinutes]]
    }),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Table, {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

1.Using a Matrix Visual Object:

Create a blank calculated column:

Column = " "

Here is the final result:

For more details, please refer to:

Create a matrix visual in Power BI - Power BI | Microsoft Learn

2.Using Measures Combined with Bookmarks to Control Visibility:

First, create a card visual object and two buttons using the following measures:

Measure = "{"& CONCATENATEX(
    'Query1',
    'Query1'[Attribute] & ": " & 'Query1'[Value]&",",
    UNICHAR(10)
)&"}"

Next, create two bookmarks, one showing the card visual object and one of the buttons, and the other not showing them:

Here is the final result:

For more details, please refer to:

Create report bookmarks in Power BI - Power BI | Microsoft Learn

Create and configure buttons in Power BI reports - Power BI | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nazdac2024]

Thanks a lot .. 
The JSON column is a part of a table that contains 15 columns .. So I am not sure if this can solve it ..
I have the follwoing structure : could you help me more 🙂 
Thanks in advance 

[Anonymous]

Hi,  @Nazdac2024 

Thank you for your prompt response.

Since I don't have this type of data for testing, if it's convenient for you, you can share the data with us using GitHub. Please ensure that sensitive information is removed.

I think you can first convert the JSON in that column to a table structure using the Table.FromRecords() function in Power Query, and then expand the table structure using the Table.ExpandTableColumn() function. Combining this with the first solution from the previous reply should give you a similar expanded result.

For more details, please refer to:

Table.ExpandTableColumn - PowerQuery M | Microsoft Learn

Table.FromRecords - PowerQuery M | Microsoft Learn

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.