Need help in parsing/splitting data into Name values

[meeradominic]

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

["Logo":true,"title01":true,"pipa_info":true,"Title_Personal_Details":true,"Title":"Miss","Gender":"Female","First_Name":"Ash","Last_Name":"Andy","DOB":"9999-02-03","Address":"123 NSW","Phone":"1234567890","Email":"df@abc.com"]
["Logo":true,"title01":true,"pipa_info":true,"Title_Personal_Details":true,"Title":"Mr","Gender":"Male","First_Name":"Aby","Last_Name":"Rob","DOB":"9999-02-10","Address":"345 NSW","Phone":"1234567899","Email":"res@abc.com"]

I'm trying to parse the data so that I can get something like below. Any help is appreciated.

Thanks,

[mussaenda]

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"

[lbendlin]

I would strongly recommend against your expected format.  Also, your source data is JSON, not a record.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZDLigIxEEV/RbJWSftuVzr42PhiFFwYadKm1EAnkSQuRPz3qc4wqD2Oy8kqde4NFc5mQ66MuHOqpHPSaEa6OE/MweDN2zOUGfHSZ0CjOzjJE0+k3j90VnknWYB1RvMsGYDnMnOFHEdGpriIEURj0AJsYCNQPI+RjqR1Pplx9d3uu2PAE/5EtbgEPJh/BBDjqdBahdYD7gthIV+DUVSrl2bLdeCLo9HwQxvNVrsT0xAMFX43BGLf4+muujOKkduNbMv/L8gW9Uz/kpNeXsj5NOlLNxH95QYdvHMTF9zgu2c52y8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FormContent = _t]),
    #"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
    #"Expanded FormContent" = Table.ExpandRecordColumn(#"Parsed JSON", "FormContent", {"submission"}, {"submission"}),
    #"Expanded submission" = Table.ExpandRecordColumn(#"Expanded FormContent", "submission", {"Logo", "title01", "pipa_info", "Title_Personal_Details", "Title", "Gender", "First_Name", "Last_Name", "DOB", "Address", "Phone", "Email"}, {"Logo", "title01", "pipa_info", "Title_Personal_Details", "Title", "Gender", "First_Name", "Last_Name", "DOB", "Address", "Phone", "Email"})
in
    #"Expanded submission"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".