cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
husnus
Frequent Visitor

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

 

 

 

 

 

12 REPLIES 12
ImkeF
Super User II
Super User II

Hi @husnus  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 III
Super User III

Hi @husnus 

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!


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.

@husnus 

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!


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

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!


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

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!


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

@husnus , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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. 

@husnus , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors