Forum Discussion

meeradominic's avatar
meeradominic
Frequent Visitor
2 years ago

Need help in parsing/splitting data into Name values

Hi Community,   I'm building a dataset out of some form data. And data is coming from a database. And it looks like this. A sample of FormContent values looks like below( 2 Records). ["Lo...
Need Help
mussaenda's avatar
mussaenda
Icon for Community Champion rankCommunity Champion
2 years ago

Hi meeradominic 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZBBT8IwFMe/innnzXSbqPMEZOIFkKgJh3VZOvqAJltL2nrg2/tWY9SB3ujt/f7/l7a/soSSw9zsDIcHb98x4uCVb5El3+CgDqJWevuj89Z36hVaZ7Ro6wK9UK0b5DRyWChHnNATaok2sBl2oo+JzpR1vl6K7rM9cfuA5+IX1fIYcPE8DSCnE7M0ZlnAEykt9tdQlKTZ1fJ1HfhqbzR+0ZvR7d19zkLw2NFzQyC3Y9Fsrjem41BBRMU0gyq6vBY7lLL4S0lzPKPkxTRnjSTsxAj9/D8j+cAI7Z0oGUFVfQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FormContent = _t, FormID = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"FormContent", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "FormContent"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "FormContent", Splitter.SplitTextByDelimiter(":", QuoteStyle.None), {"Name", "Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter1","""","",Replacer.ReplaceText,{"Name"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","[","",Replacer.ReplaceText,{"Name"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","""","",Replacer.ReplaceText,{"Value"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","]","",Replacer.ReplaceText,{"Value"})
in
    #"Replaced Value3"