Forum Discussion
Convert record to text in power BI
Hi Team,
Source table:
| Rno | Details |
| 1 | { |
| 2 | { "Internal": { "@xmlns:xsd": "http://www.w3.org/2001/XMLSchema", "@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", "SubscriptionId": "b", "ResourceGroupName": "cf", "ServerName": "d", "ApplicationName": "PostgreSQL", "PowerOperationType": "S", "ServerEntityId": "5", "VirtualMachineEntityId": "5" } } |
When i load these table in to power bi , the values are showing like below , It is showing as record in details ciolumn Instead of showing original values
| Rno | Details |
| 1 | Record |
| 2 | Record |
| 3 | Record |
I want to show the same value and pattern given by in source table instead of showing record. Can you please help me out on this ?
Thanks,
v
Hello Anonymous
this data is a json, and probably you get this from the data source. So to convert back a Reccord to a Json.File, you have to use Json.FromValue and Text.FromBinary. Here a practical example
let JsonRecord =Json.Document("{ ""Internal"": { ""@xmlns:xsd"": ""http://www.w3.org/2001/XMLSchema"", ""@xmlns:xsi"": ""http://www.w3.org/2001/XMLSchema-instance"", ""SubscriptionId"": ""b"", ""ResourceGroupName"": ""cf"", ""ServerName"": ""d"", ""ApplicationName"": ""PostgreSQL"", ""PowerOperationType"": ""S"", ""ServerEntityId"": ""5"", ""VirtualMachineEntityId"": ""5"" } } "), JsonOutput = Json.FromValue(JsonRecord), Text = Text.FromBinary(JsonOutput) in Textyou could transform this query into a function that takes Json-Record and apply to a new column and using the record-column as input.
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
3 Replies
Hello Anonymous
this data is a json, and probably you get this from the data source. So to convert back a Reccord to a Json.File, you have to use Json.FromValue and Text.FromBinary. Here a practical example
let JsonRecord =Json.Document("{ ""Internal"": { ""@xmlns:xsd"": ""http://www.w3.org/2001/XMLSchema"", ""@xmlns:xsi"": ""http://www.w3.org/2001/XMLSchema-instance"", ""SubscriptionId"": ""b"", ""ResourceGroupName"": ""cf"", ""ServerName"": ""d"", ""ApplicationName"": ""PostgreSQL"", ""PowerOperationType"": ""S"", ""ServerEntityId"": ""5"", ""VirtualMachineEntityId"": ""5"" } } "), JsonOutput = Json.FromValue(JsonRecord), Text = Text.FromBinary(JsonOutput) in Textyou could transform this query into a function that takes Json-Record and apply to a new column and using the record-column as input.
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyThanks for the solution!
I simplified it by just adding a custom column with the following code:
Text.FromBinary(Json.FromValue([JSON]))Where "JSON" is the name of the column containing the json Records.
Thanks a lot for the solution. This worked like a charm. However, if you only need one specific value from the JSON, all you need to do is to add the specific subvalue.
For instance, let's assume you have a record column named "ColumnName". It contains a subvalue called "Title". Hence, you need to add "Title" right after "ColumnName"
Text.FromBinary(Json.FromValue([ColumnName][Title]))
