Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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\":\"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

 

 

 

 

 

14 REPLIES 14
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

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.

@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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

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"
			}
		]
	}
}

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

@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.  

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

@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
Super User
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

Anonymous
Not applicable

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. 

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

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.