split character string in cell with power query

[dungluu5050]

I have the following input:

order_id	item_order_detail
17889	{"itemID":"1123","itemName":"banh baom1"},{"itemID":"1124","itemName":"khan tam"},{"itemID":"1125","itemName":"nuoc giat"},{"itemID":"1127","itemName":"sua tam da"},{"itemID":"1110","itemName":"phao khong9"},{"itemID":"1121","itemName":"het lam sao"},{"itemID":"1134","itemName":"trai ban"},{"itemID":"1141","itemName":"tim ban"},{"itemID":"1177","itemName":khai hang"},{"itemID":"1161","itemName":"chinh phat"}
17892	{"itemID":"1177","itemName":"hang tang "},{"itemID":"1167","itemName":"ban tam"},{"itemID":"1188","itemName":"nam hoa"},{"itemID":"11667","itemName":"mua dong"},{"itemID":"1198","itemName":"57mua xuan yi"},{"itemID":"1156","itemName":"hai hoang"},{"itemID":"1173","itemName":"tim ve hoa"},{"itemID":"1165","itemName":"nam hoa tim hoa"},{"itemID":"1187","itemName":"tram cai cao"},{"itemID":"116653","itemName":"Set TTT44"}

I'm looking for a way to split the characters in the item_order_detail column into 2 columns itemID and itemName. As below output table uses power query

order_id	itemID	itemName
17889	1123	banh baom1
17889	1124	khan tam
17889	1125	nuoc giat
17889	1127	sua tam da
17889	1110	phao khong9
17889	1121	het lam sao
17889	1134	trai ban
17889	1141	tim ban
17889	1177	khai hang
17889	1161	chinh phat
17892	1177	hang tang
17892	1167	ban tam
17892	1188	nam hoa
17892	11667	mua dong
17892	1198	57mua xuan yi
17892	1156	hai hoang
17892	1173	tim ve hoa
17892	1165	nam hoa tim hoa
17892	1187	tram cai cao
17892	116653	Set TTT44

Can someone suggest a solution for me?

Thanks all

[BA_Pete · Accepted solution]

Hi @dungluu5050 ,

Try this out:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZMxb4MwEIX/iuWZoU4AQ+cuXbqULc5wEIStxHbVmKpV1f/eMxn9WBis++7du3ecTlLprutlJX+NdGn2ry9GPhup1OFoZPV4eyM/b68jBStGil4Z+VcVRF0SV0tBJPKwvinrwxonsThKENAlcF8p9xcXQoR6KokPS1FcbQxLD0VUidg5iRuL3Cki5AiMp09yvKqA6msgkZzfK9fANu/VCd7tgoAW9J+s4+zYe96sPFdb8P0BBI/kshKvmT9QDxDjfu5dB3Ln7doIM2xRe8+xXyK234P+jc7E98pT/TgENS1y7fJQWEWD/yOH+DXvGkH3/vAtMrmDdcA+X5cXE4834Yts2waM985nPAxDXW8ncP4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_id = _t, item_order_detail = _t]),
    splitByDelim = Table.ExpandListColumn(Table.TransformColumns(Source, {{"item_order_detail", Splitter.SplitTextByDelimiter("},{", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "item_order_detail"),
    extractIdBetweenDelim = Table.AddColumn(splitByDelim, "itemID", each Text.BetweenDelimiters([item_order_detail], "ID"":""", """"), type text),
    extractNameBetweenDelim = Table.AddColumn(extractIdBetweenDelim, "itemName", each Text.BetweenDelimiters([item_order_detail], "Name"":""", """"), type text),
    remOthCols = Table.SelectColumns(extractNameBetweenDelim,{"order_id", "itemID", "itemName"})
in
    remOthCols

Example query output:

Pete