Forum Discussion

shijieyao's avatar
shijieyao
Microsoft Employee
3 years ago

Create bar chart based on key-value pair dictionary counter

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!

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      shijieyao
      Microsoft Employee
      3 years ago

      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.