Forum Discussion

dungluu5050's avatar
dungluu5050
New Member
3 years ago
Solved

split character string in cell with power query

I have the following input:

 

order_iditem_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_iditemIDitemName
178891123banh baom1
178891124khan tam
178891125nuoc giat
178891127sua tam da
178891110phao khong9
178891121het lam sao
178891134trai ban
178891141tim ban
178891177khai hang
178891161chinh phat
178921177hang tang
178921167ban tam
178921188nam hoa
1789211667mua dong
17892119857mua xuan yi
178921156hai hoang
178921173tim ve hoa
178921165nam hoa tim hoa
178921187tram cai cao
17892116653Set TTT44

 

Can someone suggest a solution for me?

Thanks all

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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