TEXT & JSON

[michal_boro]

Hello!

developer shared data with me in the following format:

ID	Information in STRING but ther are in JSON Format
1	{"barcode":"xyz","colli_status":"ON_THE_ROAD","is_added_by_driver":false,"scan_info":null},  {"barcode":"abc","colli_status":"ON_THE_ROAD","is_added_by_driver":false,"scan_info":null},  {"barcode":"dfg","colli_status":"ON_THE_ROAD","is_added_by_driver":false,"scan_info":null},  {"barcode":"hgf","colli_status":"ON_THE_ROAD","is_added_by_driver":false,"scan_info":null}
2	{"barcode":"%00190","colli_status":"DELIVERED","is_added_by_driver":false,"scan_info":[{"event_datetime":"2024-11-12T08:50:33.883+00:00","is_scanned_manually":true,"reason":null,"images_count":0}]}, {"barcode":"%0000","colli_status":"DELIVERED","is_added_by_driver":false,"scan_info":[{"event_datetime":"2024-11-12T08:50:33.883+00:00","is_scanned_manually":true,"reason":null,"images_count":0}]}, {"barcode":"%001094","colli_status":"DELIVERED","is_added_by_driver":false,"scan_info":[{"event_datetime":"2024-11-12T08:50:33.883+00:00","is_scanned_manually":true,"reason":null,"images_count":0}]}, {"barcode":"%08587","colli_status":"DELIVERED","is_added_by_driver":false,"scan_info":[{"event_datetime":"2024-11-12T08:50:33.883+00:00","is_scanned_manually":true,"reason":null,"images_count":0}]}

I don’t know how to transform the data into this format. What’s crucial is that the ID needs to be duplicated for each new row in the JSON

Can you help?

[michal_boro · Accepted solution]

Thank you for advanced solution, it turns out that all i need to do is right click on column header with JSON data. Select TRANFORM -> JSON, and then just expand, expand 🙂

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3dFBS8MwFAfw7/LAk528tBt2vQkrKIiDMbw0Jbw2aS2kKTTpsMq+u+nUi/PiwR16fe+f/PgnWQYMAnjnUFBfdlJxSDi8jm8cAg5lp3UjrCM32NNi+yT296nYbe82p0BjBUmppChGIfvmoHofq0hb5Ze2JCMaU3V+Zgatj8EPhoryEoys6kswL3X1HwzkQQbh+R9dIbI1/i5u0seH53SX/tHLPKEOyjghySnXtJ9SiOFywdiChXuMkxUmUXQTx9E1YoL4LUz3GG+0ZAbSevQnXT9MQq/IduarzhRuqVZWlN1gnJ/iMT97St8N51uN4Xo513LxKr6dRzfI8w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, JSON = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"},{","},#(lf){",Replacer.ReplaceText,{"JSON"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"JSON", Splitter.SplitTextByDelimiter(",#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "JSON"),
    #"Parsed JSON" = Table.TransformColumns(#"Split Column by Delimiter",{{"JSON", Json.Document}}),
    #"Expanded JSON" = Table.ExpandRecordColumn(#"Parsed JSON", "JSON", {"barcode", "colli_status", "is_added_by_driver", "scan_info"}, {"barcode", "colli_status", "is_added_by_driver", "scan_info"}),
    #"Expanded scan_info" = Table.ExpandListColumn(#"Expanded JSON", "scan_info"),
    #"Expanded scan_info1" = Table.ExpandRecordColumn(#"Expanded scan_info", "scan_info", {"event_datetime", "is_scanned_manually", "reason", "images_count"}, {"event_datetime", "is_scanned_manually", "reason", "images_count"})
in
    #"Expanded scan_info1"

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". Once you examined the code, replace the entire Source step with your own source.