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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Convert record to text in power BI

Hi Team,

 

Source table:

RnoDetails
1

{​​​​
{​​​​
  "Internal": {​​​​
    "@xmlns:xsd": "http://www.w3.org/2001/XMLSchema",
    "@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
    "SubscriptionId": "b",
    "ResourceGroupName": "c",
    "ServerName": "d",
    "ApplicationName": "PostgreSQL",
    "PowerOperationType": "S",
    "ServerEntityId": "5",
    "VirtualMachineEntityId": "5"
  }​​​​
}​​​​

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

 

RnoDetails
1Record
2Record
3Record

 

 

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

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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
    Text

 

you 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

View solution in original post

3 REPLIES 3
Jimmy801
Community Champion
Community Champion

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
    Text

 

you 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

Thanks 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]))

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors