Create bar chart based on key-value pair dictionary counter

[shijieyao]

I have a table which contains a column whose value in each cell is just a dictionary counter.

e.g.,

Col

{"a": 1, "b": 2}

What should I do to make a bar chart that uses the keys ("a", "b") as one axis, and the values (1, 2) as the other axis?

Thanks!

[amitchandak]

@shijieyao , Try the power query transformation and then you should able to use data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RSoxRslIw1FGIUUoCsYxqlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document),
    #"Expanded Col" = Table.ExpandRecordColumn(#"Parsed JSON", "Col", {"a", "b"}, {"Col.a", "Col.b"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Col", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Use this code in blank query 

[shijieyao]

Hi @amitchandak , thank you!

But what if the keys in the dictionary are not fixed?
For example, sometimes it could be {"a":1, "b":2}, or {"b": 1, "c": 3} or even {"d": 4}.

I find it hard to expand cols when I don't know in advance what keys there would be.
Or does PBI also supports dynamically expanding columns? Based on my trials, they didn't seem to work.