Dynamic table based on JSON column

Question

Hi,&nbsp;We have a data source that is a table of fixed columns, as well as an additional column with JSON of the form:{"Column 1": "value", "Column 2": "value", ... }We have hundreds of records, and potentially hundreds of columns, which we do not know in advance.&nbsp;We want to display this data in a 'normal' PBI table, (including sorting, filtering, highlighting, ...) dynamically, and after a set of rows have been filtered.&nbsp;Is this possible in PBI?

marklaf · Answer

I have not had any opportunities to play around with deneb visuals but would also be interested in seeing a deneb solution to this if it's out there. You may have more luck if you post in the custom visual forum and include dummy data and clear depiction of the output you are looking for:&nbsp;https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/bd-p/CustomVisualsDevelopmentDiscussion&nbsp;That said, you can use the matrix visual to dynamically show columns based on an entity slicer (would make sense to enforce single-select on the slicer for your purpose). It sounds like you've already tried this? Here is a quick example walkthrough, regardless.&nbsp;Set up data using field/value output described previously - not much different here although we are also creating an entity row table separate from the fields tables. Not needed but good to start modeling the data a bit.&nbsp;SourceData:Entity NameEntity Row IDDataAA_1{"Entity A_Col 1": 0, "Entity A_Col 2": 1, "Entity A_Col 3": "K" }AA_2{"Entity A_Col 1": 3, "Entity A_Col 2": 4, "Entity A_Col 3": "L" }AA_3{"Entity A_Col 1": 6, "Entity A_Col 2": 7, "Entity A_Col 3": "M" }BB_1{"Entity B_Col 1": "abc123", "Entity B_Col 2": "bcd234"}BB_2{"Entity B_Col 1": "cde345", "Entity B_Col 2": "def456"}CC_1{"Entity C_Col 1": 0, "Entity C_Col 2": 1, "Entity C_Col 3": 2, "Entity C_Col 4": 3, "Entity C_Col 5": 4 }DD_1{"Entity D_Col 1": 1000}DD_2{"Entity D_Col 1": 1001}DD_3{"Entity D_Col 1": 1002}DD_4{"Entity D_Col 1": 1003}&nbsp;EntityRows:&nbsp;&nbsp;let
    Source = SourceData,
    SelectCols = Table.SelectColumns(
        Source,
        {"Entity Name", "Entity Row ID"}
    )
in
    SelectCols&nbsp;&nbsp;&nbsp;&nbsp;FieldsValues:&nbsp;&nbsp;let
    Source = SourceData,
    SelectCols = Table.SelectColumns(Source,{"Entity Row ID", "Data"}),
    Parse = Table.TransformColumns(
        SelectCols,
        {
            "Data", 
            each let json = Json.Document(_) in 
                Table.FromColumns( 
                    { Record.FieldNames(json),Record.FieldValues(json) }, 
                    type table [Field=text,Value=any] 
                ),
            type table [Field=text,Value=any] 
        }
    ),
    ExpandData = Table.ExpandTableColumn(Parse, "Data", {"Field", "Value"}, {"Field", "Value"})
in
    ExpandData&nbsp;&nbsp;&nbsp;&nbsp;Load these into your model, relate the tables EntityRows -1---M-&gt; FieldValues.&nbsp;You can then set up a slicer with EntityRows[Entity Name] and a matrix with EntityRows[Entity Row ID] for rows, FieldValues[Field] for columns, and a SELECTEDVALUE(&nbsp;FieldValues[Value] ) for the values.&nbsp;Here is an example of doing something a little fancier for aggregrations than SELECTEDVALUE, which just defaults to BLANK() when there are multiple values. Here, we will SUM all the values as long as we don't find any text. You can similarly customize the aggregation behavior at the type or even field level - it requires DAX though - no easier automations that I know of.&nbsp;ValuesSum = 
VAR _hasTxt = 
    NOT ISEMPTY( 
        FILTER( 
            VALUES( FieldsValues[Value] ), 
            IFERROR( NOT ISNUMBER( VALUE( FieldsValues[Value] ) ), TRUE ) 
        ) 
    )
VAR _sumTry = 
    SUMX( 
        VALUES( FieldsValues[Value] ), 
        IFERROR( VALUE( FieldsValues[Value] ), BLANK() ) 
    )
VAR _nonAggTxt = 
    SELECTEDVALUE( FieldsValues[Value] )
RETURN
IF( _hasTxt, _nonAggTxt, _sumTry )&nbsp;&nbsp;Edit: fixed a sentence I didn't finish and added DAX for measure I mentioned - I think the editor dropped it when I first tried to post?&nbsp;

marklaf · Answer

Yes.&nbsp;Let's say you are importing something like the below table:&nbsp;SourceTable:Fixed Col AFixed Col BFixed Col CVariable Json Col11121{"Column1":"A","Column2":"B1"}21222{"Column2":"B2","Column3":"C"}31323{"ColumnA":1,"ColumnB":2}41424{"ColumnC":3,"ColumnD":4}51525{"Extra 1":"abc","Extra 2":"def"}&nbsp;The following code will parse the JSON column, acertain all the JSON fields across all rows, then expand all of them:&nbsp;let
    Source = SourceTable,

ParseJSON = Table.TransformColumns(Source,{{"Variable Json Col", Json.Document}}),

AllJsonFields = List.Distinct( List.Combine( 
        List.Transform( ParseJSON[Variable Json Col], Record.FieldNames ) 
    ) ),

ExpandAllJsonFields = Table.ExpandRecordColumn(ParseJSON, "Variable Json Col", AllJsonFields )
in
    ExpandAllJsonFields&nbsp;Output:&nbsp;Note that all the dynamically expanded JSON fields are of type any, so any type-setting will have to be addressed separately. Although, it would be possible to try to do something like automatically setting the type based on the values.

marklaf · Answer

Q: There may be hundreds of columns. Can PBI deal with these?A: Yes, although it's almost certainly not useful for analysis. If you provide more context on what kind of reports you want to build, and more specificity on the shape of your data, it would be easier to recommend something. Pasting test data into a table for easy copying into PBI is the best&nbsp;Q: For every entity, you will see all the columns (not even ordered) of all other entities.A: This is unavoidable if you want to expand them all in the same table. Are some rows of the same structure as others and it would make sense to split them into separate tables? If it's highly variable without patterns in the data to leverage, I would unpivot all the JSON parsed columns into field/value columns. You can just use unpivot through the UI after expanding. Or change up the query steps to convert it before expanding - something like:&nbsp;&nbsp;let
    Source = SourceTable,

// parse json and then convert to field/value table
    ParseJSON = 
    Table.TransformColumns(
        Source,
        {
            "Variable Json Col", 
            each Record.ToTable( Json.Document(_) ), 
            type table [Name=text,Value=any] 
        }
    ),

ExpandJsonCol = 
    Table.ExpandTableColumn(
        ParseJSON, 
        "Variable Json Col", 
        {"Name", "Value"}, 
        {"Name", "Value"}
    )

in
    ExpandJsonCol&nbsp;&nbsp;Output:&nbsp;

montechristos01 · Answer

I appreciate MarkLaf's effort and good insights on the issue.However the original question is not fully answered, so I am hesitating to mark it as an answer.This would lead others to think this issue has been resolved, but it is not fully.Hope MarkLaf you understand

montechristos01 · Answer

Thanks for the answer.We had thought of that, but there are tow issues:- There may be hundreds of columns. Can PBI deal with these?- For every entity, you will see all the columns (not even ordered) of all other entities.&nbsp;Any further thoughts?

Forum Discussion

Dynamic table based on JSON column

8 Replies

Related content

Dynamic Column headers

Dynamic Column Name

table with dynamic values (columns)

Dynamic Column Selection in a Table Visual

Dynamic Table Creation???

Fixed Col A	Fixed Col B	Fixed Col C	Variable Json Col
1	11	21	{"Column1":"A","Column2":"B1"}
2	12	22	{"Column2":"B2","Column3":"C"}
3	13	23	{"ColumnA":1,"ColumnB":2}
4	14	24	{"ColumnC":3,"ColumnD":4}
5	15	25	{"Extra 1":"abc","Extra 2":"def"}