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"
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"