Forum Discussion
Helper IDynamic table based on JSON column
Super UserYes.
Let's say you are importing something like the below table:
SourceTable:
| 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"} |
The following code will parse the JSON column, acertain all the JSON fields across all rows, then expand all of them:
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
Output:
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.
Montechristos01Thanks 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.
Any further thoughts?
- MarkLaf
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
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:
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 ExpandJsonColOutput:
- Montechristos01
Thanks again, very valid points. We tried this as well, and display it in a matrix so it 'looks' like a table. It is as close to a solution as possible.
I will try to attach some data here for exploration.
The complete use-case is the following (I should have explained better in the original post):
We have records from different entities that fail some validation criteria.
(let's say 100 entities in total, each entity with 20 columns, each entity containing 500 failing records)We will never show a single table for different entities together, we will first filter on each entity separately.
Once the filtering is done, we want to display all the records of this entity in an expanded table.
We could build one report per entity, expanding all its JSON columns, but this is too cumbersome.
Hence the need to filter 1st, and then display the expanded table.
Any thoughts?