Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Bi nested list in string

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. Alth...
  • lbendlin's avatar
    4 years ago

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