Forum Discussion
How to visualise a JSON column in a well-formatted text on a card in power bi
Hi Team,
I have a dataset from Azure search service with a column named Data that is a payload message of an event. We have many different events with different messages in different structures as shown below...
Event 1:"
{\"message\": {\"id\": \"xxxx\",\"person\": {\"name\": \"husnu\",\"age\": 34},\"addresses\": [{\"city\": \"london\"},{\"city\": \"oxford\"}]}}
Event 2:
{\"message\": {\"id\": \"ABCD-1234\",\"subType\": \"NewOrder\",\"personId\": 0,\"summary\": \"summary note\",\"notes\": \"notesss\",
\"orderDate\": \"2021-02-11T15:35:51.1274372Z\",\"phoneNumber\": \"+111111\", \"requestorFirstName\": \"Adam\",
\"requestorLastName\": \"Adam\",\"requestorEmail\":\"[email protected]\"}}
Event 3: .....
Event 4: .....
.....
Event N:.....
and so on...
I did many searches but almost all the results are about how to use/load JSON files as a dataset in power bi. I know how to load a JSON file and play around it but I need some help on how to visualize this column in readable and well-formatted text on a card in power bi as shown below.
Event 1
{
"message": {
"id": "xxxx",
"person": {
"name": "husnu",
"age": 34
},
"addresses": [
{
"city": "london"
},
{
"city": "oxford"
}
]
}
}
Event 2:
{
"message": {
"id": "ABCD-1234",
"subType": "NewOrder",
"personId": 0,
"summary": "summary note",
"notes": "notesss",
"orderDate": "2021-02-11T15:35:51.1274372Z",
"phoneNumber": "+111111",
"requestorFirstName": "Adam",
"requestorLastName": "Adam",
"requestorEmail": "[email protected]"
}
}
Can you please help me with this?
Many thanks
19 Replies
- amitchandak
Super User
Anonymous , Event One after expansion might be broken into two tables. Event two in one table
If ID is the same then you need to create a common table for ID
Not sure this can help. But I created two tables from JSON in this file: https://www.youtube.com/watch?v=MPYfEetvFH4
Thank you for your reply amitchandak .
Obviously, I dont want to break the column into tables. Even if I do that I have to know the structure of JSON for each event to generate a well-formatted text in power query.
- amitchandak
Anonymous , Oh, you want to display a formatted JSON. Split and rebuild would be a lot of overhead. Let us check with the Power Query expert.
ImkeF , Any suggestion for this. How to format JSON in power query or DAX
- PhilipTreacy
Hi Anonymous
If each message is a different structure and you want to save all messages in a single table, the best you can do is to have two columns like
ID | Message
with the message stored as string.
Regards
Phil
Thank you for your reply PhilipTreacy .
Currently, there are 2 columns: id and Data, and Data is stored as a string but I want to visualize it in a well-formatted JSON text on a card.
- PhilipTreacy
Anonymous
Can you please give an example of what you mean by a well formatted JSON text?
Do you mean you actually want to see it like this in a card
{ "message": { "id": "xxxx", "person": { "name": "husnu", "age": 34 }, "addresses": [ { "city": "london" }, { "city": "oxford" } ] } }Regards
Phil
- ImkeF
Community Champion
Hi Anonymous and amitchandak ,
I haven't done anything like this before.
You'd probably have to find an API first that transforms to the desired JSON format. Did you get anything to solve this issue,please help, i am also looking something similar
PowerBI default visuals doesn't allow to achieve this, while creating your own custom visual gives you access to format JSON properly. Example image attached
PowerBI default visuals doesn't allow to format JSON properly, but moving to custom visuals give you access to properly format JSON Data. Example image attached
rjhaleHelper V
Interesting. Which custom visual are you using to display the json like that?
Designed and developed my own custom visual using typescript
