undefined

[jainesh12]

I've got the below column from MongoDB i.e. of embedded approach, and the column is of "Object" type.
How can I simplify the below?

[v-jingzhang]

Hi @jainesh12 

From the screenshot, the values are not in a standard format that many symbols are not in pairs e.g. "", [], {}. I'm not sure what is your expected result. But since the value format is similar to JSON format, I think you may want to transform each row's Orders data into a table which has columns OrderItem_ID, Product_ID, Quantity, Order_Amount and Order_Date.

If my guess is correct, you can try my solution. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSkktijeMUbKKro6B8DxLUnPjPV2AQoY6MUoBRfkppcklCIHA0sS8ksySSiDXWAeqJd4xN780rwQkZGAAF3RJLEkFCsUoGRkYGesamAFRjFJtLFzeCLutRui2GqHaaoppqyluWy10DUxBttbGxirF6kQrGcE8jbAwRilGCdNMqCiKS6BiSI5B0Qu3GmyT8cAEL9ijsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Orders = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Orders", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Orders", Splitter.SplitTextByDelimiter("}],", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Orders"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Orders", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","""","",Replacer.ReplaceText,{"Orders"}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Replaced Value", {{"Orders", each if Text.Contains(_, "[{") then Text.BetweenDelimiters(_, "[{", "}]") else _, type text}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Text Between Delimiters", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.SplitColumn(Table.FromList(List.Sort(Text.Split([Orders], ","))), "Column1", Splitter.SplitTextByDelimiter(":"),2)), [PromoteAllScalars=true])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"OrderItem_ID", "Order_Amount", "Order_Date", "Product_ID", "Quantity"}, {"OrderItem_ID", "Order_Amount", "Order_Date", "Product_ID", "Quantity"})
in
    #"Expanded Custom"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[jainesh12]

Thank you @v-jingzhang , this might help.