Power Bi nested list in string

[Anonymous]

So i have the following problem:

I'm trying to import some data from my database into my report.
The data is provided by some views, which already have predetermined datatypes for my columns.
Although they are not all correct. And here lies the problem.

An example would be:

items (type = int)	itemDetails (type = string)
1	[[[Fruit], Fruit, Apple, 0.90]]
2	[[[Fruit], Fruit, Apple, 0.90],[[Veggie], Veggie, Salat, 1.50]]

Now the problem is, that Power Bi doesn't recognize the data in the second column as a list, or an array.

My goal is to extract the informations in these nested lists: so the desired output would be something like:

With the extracted information also preferable being lists of values, as the Details column can be arbitrarilylong. 

items	itemDetails	itemCategory	itemName	itemPrice
1	[[[Fruit], Fruit, Apple, 0.90]]	Fruit	Apple	0.9
2	[[[Fruit], Fruit, Apple, 0.90],[[Veggie], Veggie, Salat, 1.50]]	Fruit, Veggie	Apple, Salat	0.9, 1.5

Any help on how to extract this data would be appreciated.

[v-henryk-mstf]

Hi @Anonymous ,

Agree with @lbendlin suggestion, it seems to be simpler to implement in a power query.

If the problem is still not resolved, please point it out. Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

Are you sure that is what you want your result to look like?  It may not be usable for visualization.  I think this would be better:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYqOjnYrKs0sidVRANM6Co4FBTmpOgoGepYGsbFKsTrRSkYE1elER4elpqdnpgKlIQwdheDEnESgMkM9U7A5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"items (type = int)" = _t, #"itemDetails (type = string)" = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"itemDetails (type = string)", Splitter.SplitTextByDelimiter("],[", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "itemDetails (type = string)"),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","[","",Replacer.ReplaceText,{"itemDetails (type = string)"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"itemDetails (type = string)"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value1", "itemDetails (type = string)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"itemDetails (type = string).1", "itemDetails (type = string).2", "itemDetails (type = string).3", "itemDetails (type = string).4"})
in
    #"Split Column by Delimiter1"

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".