Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Convert record to text in power BI

Hi Team,   Source table: Rno Details 1 {​​​​ {​​​​   "Internal": {​​​​     "@xmlns:xsd": "http://www.w3.org/2001/XMLSchema",     "@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instan...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

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

skythe83's avatar
skythe83
Regular Visitor
3 years ago

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.

  • HoboHarasser's avatar
    HoboHarasser
    Regular Visitor
    3 years ago

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