Forum Discussion

roelf's avatar
roelf
Helper II
7 years ago
Solved

Tranform JSON to table

Hi. I'm in need of help to transform data from an object into a table. I've tried Parsing to JSON, splitting to columns, etc. but I cannot get the data to the format i need it in. When parsing to JSO...
Need Help
  • roelf's avatar
    roelf
    7 years ago

    For those interested, here is what i did in the end.

     

    Using 'search and replace' I changed the JSON. With various steps I moved the value i want to split into rows on, into the OBJECT, so that I end up with an array of objects. This allows me to "split to rows", ending up with individual OBJECTS, and then i extract the data i need from the OBJECT.

     

    let
    Source = Sql.Databases("XXXX.database.windows.net"),
    XXXX = Source{[Name="XXXX"]}[Data],
    dbo_table = XXXXX{[Schema="dbo",Item="dbo_table"]}[Data],
    #"Added Prefix" = Table.TransformColumns(dbo_table, {{"zones", each "[" & _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"zones", each _ & "]", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Added Suffix","},","}},{",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","{""#","{""zonerid"":""#",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",":{""s",",""s",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","}}","}",Replacer.ReplaceText,{"zones"}),
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value3",{{"zones", Json.Document}}),
    #"Expanded zones" = Table.ExpandListColumn(#"Parsed JSON", "zones"),
    #"Expanded zones1" = Table.ExpandRecordColumn(#"Expanded zones", "zones", {"zonerid"}, {"zones.zonerid"})
in
    #"Expanded zones1"
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, roelf

After my research , I'm afraid it's hard to achieve in Power BI for now.

It's easy to achieve it that use this code to transform JSON to table like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5dVNT8IwGADgv7LMK5p+bKzjBiHIAb2MxBhGSIUqC/sg29Cg4b9bCo62FFASDupu7fuR9kn2djCwryBuQIgc1xKfXbOtj1DsIgAchEK7wddFlpcpTRhfhXZzRnOa0jZNrRZLrS5NUzpFoGVdW+1WN7RroT3OkiSbROVSFNzmdM6KTWARl9ErzdfNkBRc1yr99rJF8pGcUTQRKeJCPgHEFeFnmif6yauAWuN7dV+EipKWi0JE6Jg3Z2L3Kc7GMzmdQLhJj97X7eGN4/JVLvX8IuTb1RkkqFVNSnMPS3divuHwqk7v7mxe0cSsuh+SYTCpu97lLR3V0kjpqpQViQLpn4D0RFXwZyGJCumZIH0VsiJRIOFhyKCkeRy9TEsEmrw26PfO5qxamUnN4ZF6Iby90EVdgeqKsQkW6j97xSPTwiNjtU/fUra0AsYmMSsKiHiD/v3j2b5aP7PysSR9sPrOxa2BNlgxMVhDbbDupGRqDL5NTf6hNPRPTwsMVGhiciam96vNrG6WzbU7P3i/AXp79oPQHvwRtKe9bxgZpIn2wO2kVit7OPwE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID             " = _t, #" DATA" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID             ", type text}, {" DATA", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document([#" DATA"])),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"#13:200422", "#13:200425", "#13:200429", "#13:200421", "#13:200412", "#13:200430", "#13:200485"}, {"#13:200422", "#13:200425", "#13:200429", "#13:200421", "#13:200412", "#13:200430", "#13:200485"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"ID             ", " DATA"}, "Attribute", "Value"),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"sortname", "commodity", "cultivarname2", "cultivarname", "cultivar_id", "farmname", "farm_id", "status", "block", "size", "rid", "name"}, {"sortname", "commodity", "cultivarname2", "cultivarname", "cultivar_id", "farmname", "farm_id", "status", "block", "size", "rid", "name"})
in
    #"Expanded Value"

For the format you require, it may need to be done in the data source and it's hard to achieve in Power BI.

 

Best Regards,

Lin

 

 

 

 

 

  • roelf's avatar
    roelf
    Helper II
    7 years ago

    Thanks a lot Lin

     

    I have this problem with different columns in the dataset also, so though the code looks great, it's gonna be too much of a hassle to convert etc. I think our solution lies in writing the data differently.

     

    Thanks for your effort, much appreciated.

     

    Roelf

    • roelf's avatar
      roelf
      Helper II
      7 years ago

      For those interested, here is what i did in the end.

       

      Using 'search and replace' I changed the JSON. With various steps I moved the value i want to split into rows on, into the OBJECT, so that I end up with an array of objects. This allows me to "split to rows", ending up with individual OBJECTS, and then i extract the data i need from the OBJECT.

       

      let
    Source = Sql.Databases("XXXX.database.windows.net"),
    XXXX = Source{[Name="XXXX"]}[Data],
    dbo_table = XXXXX{[Schema="dbo",Item="dbo_table"]}[Data],
    #"Added Prefix" = Table.TransformColumns(dbo_table, {{"zones", each "[" & _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"zones", each _ & "]", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Added Suffix","},","}},{",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","{""#","{""zonerid"":""#",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",":{""s",",""s",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","}}","}",Replacer.ReplaceText,{"zones"}),
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value3",{{"zones", Json.Document}}),
    #"Expanded zones" = Table.ExpandListColumn(#"Parsed JSON", "zones"),
    #"Expanded zones1" = Table.ExpandRecordColumn(#"Expanded zones", "zones", {"zonerid"}, {"zones.zonerid"})
in
    #"Expanded zones1"