Power Query Editor split object to columns

[Anonymous]

Hi Community,

Have not been able to find a solution myself so I am asking my fellow community members. I have a column with product attributes in an array object. I have attached an example. In the example I have two products that have 12 attributes each. 

[{"brand":"brand1","category":"category1","currency":"EUR","ean":"ean1","edition":3,"isbn":"isbn1","name":"name1","price":"9.95","product_id":"id1","product_type":"producttype1","quantity":1,"variant":"regular"},{"brand":"brand2","category":"category2","currency":"EUR","ean":"ean2","edition":1,"isbn":"isbn2","name":"name2","price":"9.95","product_id":"id2","product_type":"producttype2","quantity":1,"variant":"regular"}]

I would like to have Power Query Editor solution for splitting this one column into 12 seperate columns, where the first value [before the colon] of the attribute will become the name of column and the second value [after the colon] becomes the actual row value. Please note: The value of the attribute might include comma's, colons and brackets as well. Hence, 

"brand":"brand1"

is an example of a clean attribute. But this might as well look something like:

"brand":["This is an example of a not, so clean combination of attribute and value",""something else"]

Hope someone is able to help me out!

[V-pazhen-msft]

@Anonymous 

Cannot access 😥.

Paul

[Anonymous]

@V-pazhen-msft Let's try this again: https://we.tl/t-gY8EmMDBmR.

[V-pazhen-msft]

@Anonymous 

Can you just show a table format of your data, and also show the expected output in a table. 

Best Regards
Paul

[Anonymous]

@V-pazhen-msft,

Thank you for replying. I have just created a Power BI file with a few rows of our Order data file and the expected end result table. You can download the file here: https://we.tl/t-rMnqZIbIek.

It appears that the error is caused by some rows in the products column having more than 500 bytes of data which cuts of part of the string and we don't have the '}]' closing the string identifying these rows as actualy having json formatting.

Hope you can help me out!

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9BCsIwEEXvknUREnFR915AcNUUSZNQBjStYyIU8e4mmbiwRewq89/8wLymYc1Tsg6VM5Lty8QlqyTTytt+wCnzTyirgGidptXhdMzQKpdzfKllDXgYEtzGBPeO9mmgglNXm1EaCI0Imli9qXcFDSZofwa6EAz/wn4a6UMBKVPjFpTz4NOVPMaHQoggd9H24aJQsle11Be/9cUqfTHT53N9sdQXq/XFX32xXr9lbfsG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Json = _t]),
    Parse = Table.TransformColumns(Source, {"Json", each Table.FromRecords(Json.Document(_))}),
    #"Expanded Json" = Table.ExpandTableColumn(Parse, "Json", {"brand", "category", "currency", "ean", "edition", "isbn", "name", "price", "product_id", "product_type", "quantity", "variant"}, {"brand", "category", "currency", "ean", "edition", "isbn", "name", "price", "product_id", "product_type", "quantity", "variant"})
in
    #"Expanded Json"

[Anonymous]

@CNENFRNL Thank you for the reply. As stated in my previous message. A standard JSON parse is not going to work out for me as this array does not follow a standard json format. Any other solution?