How to visualise a JSON column in a well-formatted text on a card in power bi

[Anonymous]

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\":\"xxx.yy@xxx.com\"}}

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": "xxx.yy@xxx.com"
	}
}

Can you please help me with this?

Many thanks

[ImkeF]

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.

[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

[Anonymous]

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

[Anonymous]

Yes @PhilipTreacy 

That is exactly what I want to see on the card.

{
	"message": {
		"id": "xxxx",
		"person": {
			"name": "husnu",
			"age": 34
		},
		"addresses": [
			{
				"city": "london"
			},
			{
				"city": "oxford"
			}
		]
	}
}

[PhilipTreacy]

Hi @Anonymous 

As @ImkeF  suggests I'd be looking for an API that you can use for this formatting but a quick check in Google doesn't return anything that can do it.

Regards

Phil

[Anonymous]

@PhilipTreacy , @ImkeF  apart from the solution you suggested I was expecting power bi to provide a library that has functions for JSON operations such as serialize/deserialize in Dax.  

[PhilipTreacy]

Hi @Anonymous 

But what you want is the JSON displayed in a formatted way.  Sorry I've never actually had to do this.  Usually you need to extract the data from JSON and then deal with it.

regards

Phil

[Anonymous]

@PhilipTreacy I do not want to play with data. what I want is very simple that is to display the data as it is but in a formatted way.

[amitchandak]

@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

[Anonymous]

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. 

[jeremy_holovacs]

Did you ever find a solution? Desperately looking for something similar.

[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

[IcaCoco]

Any news on this? Might be nice to have that option.