How to display raw json data in Power BI desktop report

[allenfirth]

I have a dataset in Power BI Desktop that is based on json documents coming out of Cosmos DB. I am able to expand the json documents into columns that I can then use in Power BI. This all works great.

What I am unable to find out how to do is actually display the full and original json content of an individual record.

Basically once the user of my Power BI report has filtered and sliced the data to their satisfaction and reduced it to a small subset of the overall data they will then want to see at a record by record level the full and original contents of the json records.

How do I display this to the users of my Power BI report?

[v-shex-msft]

Hi @allenfirth,

You can duplicate your source data, and use text format to store these raw json data as a copy of original data.

Step:

1. Click on 'Gear' icon to open the source setting.

2. Modify the 'Open file as' option to Text file.

3. Click on 'Ok' to apply these changes.

Regards,

Xiaoxin Sheng

[allenfirth]

Could you please elaborate on where I will find the "gear" icon?

Many thanks, 

Allen

[v-shex-msft]

Hi @allenfirth,

You can find it at query setting tab:

Regards,
Xiaoxin Sheng

[allenfirth]

@v-shex-msft thanks for the reply. 

My datasource is a CosmosDB and NOT a json file so when I click the gear icon on the Source step under query settings, I don't ge tthe option to open the file as text, rather I get a dialog with the connection details to my CosmosDB.

Regards, 

Allen

[v-shex-msft]

HI @allenfirth,

Can you please share some query string which you used in power query? I will check it if I can find some method to work through this situation.

Regards,
Xiaoxin Sheng

[allenfirth]

Thanks @v-shex-msft

Here is my underlying data query:

let
Source = DocumentDB.Contents("https://[cosmosdb account].documents.azure.com:443/", "[cosmosdb container]", "[cosmos db collection]"),
#"Expanded Document" = Table.ExpandRecordColumn(Source, "Document", {"ApplicationId", "Application", "Component", "Environment", "TimeStamp", "LoggedOnUser", "Version", "MessageSucceeded", "MessageFailures", "MessageType", "Message", "MessageId", "TimeStampYear", "TimeStampMonth", "TimeStampDay", "TimeStampHour", "TimeStampMinute", "id"}, {"Document.ApplicationId", "Document.Application", "Document.Component", "Document.Environment", "Document.TimeStamp", "Document.LoggedOnUser", "Document.Version", "Document.MessageSucceeded", "Document.MessageFailures", "Document.MessageType", "Document.Message", "Document.MessageId", "Document.TimeStampYear", "Document.TimeStampMonth", "Document.TimeStampDay", "Document.TimeStampHour", "Document.TimeStampMinute", "Document.id"}),
#"Expanded Document.Version" = Table.ExpandRecordColumn(#"Expanded Document", "Document.Version", {"Major", "Minor", "Build", "Revision", "MajorRevision", "MinorRevision"}, {"Document.Version.Major", "Document.Version.Minor", "Document.Version.Build", "Document.Version.Revision", "Document.Version.MajorRevision", "Document.Version.MinorRevision"}),
#"Inserted Parsed Date" = Table.AddColumn(#"Expanded Document.Version", "Parse", each Date.From(DateTimeZone.From([Document.TimeStamp])), type date),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Parsed Date",{{"Parse", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "VersionDescription", each Number.ToText([Document.Version.Major]) & "." & Number.ToText([Document.Version.Minor]) & "." & Number.ToText([Document.Version.Build]) & "." & Number.ToText([Document.Version.Revision])),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Document.ApplicationId", "ApplicationId"}, {"Document.Application", "Application"}, {"Document.Component", "Component"}, {"Document.Environment", "Environment"}, {"Document.TimeStamp", "TimeStamp"}, {"Document.LoggedOnUser", "LoggedOnUser"}, {"Document.Version.Major", "Version.Major"}, {"Document.Version.Minor", "Version.Minor"}, {"Document.Version.Build", "Version.Build"}, {"Document.Version.Revision", "Version.Revision"}, {"Document.Version.MajorRevision", "Version.MajorRevision"}, {"Document.Version.MinorRevision", "Version.MinorRevision"}, {"Document.MessageSucceeded", "MessageSucceeded"}, {"Document.MessageFailures", "MessageFailures"}, {"Document.MessageType", "MessageType"}, {"Document.Message", "Message"}, {"Document.MessageId", "MessageId"}, {"Document.TimeStampYear", "TimeStampYear"}, {"Document.TimeStampMonth", "TimeStampMonth"}, {"Document.TimeStampDay", "TimeStampDay"}, {"Document.TimeStampHour", "TimeStampHour"}, {"Document.TimeStampMinute", "TimeStampMinute"}, {"Document.id", "id"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"TimeStamp", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"TimeStamp", Order.Descending}})
in
#"Sorted Rows"

The column of expanded data containing the original JSON is called "Message"

[v-shex-msft]

HI @allenfirth,

It seems these records has been converted to table by connector. So, I'm afraid that is hard to convert back these data.

Regards,

Xiaoxin Sheng