Power Bi nested list in string

Question

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]]&nbsp;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.&nbsp;&nbsp;itemsitemDetailsitemCategoryitemNameitemPrice1[[[Fruit], Fruit, Apple, 0.90]]FruitApple0.92[[[Fruit], Fruit, Apple, 0.90],[[Veggie], Veggie, Salat, 1.50]]Fruit, VeggieApple, Salat0.9, 1.5&nbsp;Any help on how to extract this data would be appreciated.

lbendlin · Accepted Answer

Are you sure that is what you want your result to look like?&nbsp; It may not be usable for visualization.&nbsp; I think this would be better:
&nbsp;
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".

Forum Discussion

Power Bi nested list in string

2 Replies

Related content

Nested IF

Power Query Nested IF AND Statement

Nested IF

Power Query - Nested table - List.PositionOf - Find substring

Power Query Table.SelectRows with multiple/nested criteria

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

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