Power BI - Deneb visual - Read a JSON to create the visuals

[DGPBi]

Hi everyone,
I have a graphic which work correctly. I use at the moment a table in power BI dedicated for this visual. It seems possible for deneb to read JSON data because when I provide the original table with the JSON column, I can see the data but when I try to use JSON.parse(), I have lot of errors
Please find my current code which work perfectly from my Power BI.

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "description": "Final trusted version with a correctly implemented 'No Schedule Data' message.",
  "width": 372,
  "height": 48,
  
  "signals": [
    {
      "name": "pbi_time_offset_seconds",
      "init": "isValid(data('pbi_summarize')[0]) ? data('pbi_summarize')[0].DST_ClockDiffBtwLocationAndLocalOffset : 0"
    },
    {
      "name": "utc_now",
      "init": "now()",
      "on": [
        {
          "events": {"type": "timer", "throttle": 10000},
          "update": "now()"
        }
      ]
    },
    {
      "name": "remote_now",
      "update": "utc_now + (pbi_time_offset_seconds * 1000)"
    },
    {
      "name": "remote_day",
      "update": "day(remote_now)"
    },
    {
      "name": "remote_hour_decimal",
      "update": "hours(remote_now) + (minutes(remote_now) / 60) + (seconds(remote_now) / 3600)"
    },
    {
      "name": "remote_time_label",
      "update": "timeFormat(remote_now, '%H:%M')"
    },
    {
      "name": "has_data_for_today",
      "update": "data('daily_schedule').length > 0"
    }
  ],

  "data": [
    {
      "name": "dataset"
    },
    {
      "name": "pbi_summarize"
    },
    {
      "name": "daily_schedule",
      "source": "dataset",
      "transform": [
        {"type": "filter", "expr": "datum.day_num != null && datum.day_num == remote_day"}
      ]
    },
    {
      "name": "schedule_bands",
      "source": "daily_schedule",
      "transform": [
        {
          "type": "filter",
          "expr": "datum.ScheduleType == 'Production' || datum.ScheduleType == 'ITSupport'"
        },
        {
          "type": "formula",
          "as": "display_type",
          "expr": "datum.type == 'Normal' ? (datum.ScheduleType == 'Production' ? 'Working hours' : 'Support hours') : datum.type == 'Flexible' ? 'Flexible hours' : 'No Working hours'"
        }
      ]
    },
    {
      "name": "site_opening_start",
      "source": "daily_schedule",
      "transform": [
        {"type": "filter", "expr": "datum.ScheduleType == 'SiteOpening'"},
        {"type": "project", "fields": ["startTime"], "as": ["time_value"]}
      ]
    },
    {
      "name": "site_opening_end",
      "source": "daily_schedule",
      "transform": [
        {"type": "filter", "expr": "datum.ScheduleType == 'SiteOpening'"},
        {"type": "project", "fields": ["endTime"], "as": ["time_value"]}
      ]
    }
  ],
  "scales": [
    {
      "name": "xscale",
      "type": "linear",
      "range": "width",
      "domain": [0, 24]
    },
    {
      "name": "yscale",
      "type": "band",
      "range": [1, 50],
      "domain": ["ITSupport", "Production"],
      "padding": 0.1
    },
    {
      "name": "color",
      "type": "ordinal",
      "domain": ["Working hours", "Support hours", "Flexible hours", "No Working hours"],
      "range": ["#a6cee3", "#b2df8a", "#1f78b4", "#d3d3d3"]
    }
  ],
  "axes": [
    {
      "orient": "bottom",
      "scale": "xscale",
      "tickCount": 25,
      "title": {
        "signal": "timeFormat(remote_now, 'Time & Day (%Y-%m-%d)')"
      },
      "labelFontSize": 9,
      "titleFontSize": 9
    }
  ],
  "marks": [
    {
      "type": "group",
      "name": "custom_legend",
      "encode": {"enter": {"x": {"value": 0}, "y": {"value": -40}}},
      "marks": [
        {"type": "rect", "encode": {"enter": {"x": {"value": 0}, "y": {"value": 0}, "width": {"value": 10}, "height": {"value": 10}, "fill": {"value": "#a6cee3"}}}},
        {"type": "text", "encode": {"enter": {"x": {"value": 15}, "y": {"value": 9}, "text": {"value": "Working hours"}, "fontSize": {"value": 10}, "align": {"value": "left"}}}},
        {"type": "rect", "encode": {"enter": {"x": {"value": 120}, "y": {"value": 0}, "width": {"value": 10}, "height": {"value": 10}, "fill": {"value": "#1f78b4"}}}},
        {"type": "text", "encode": {"enter": {"x": {"value": 135}, "y": {"value": 9}, "text": {"value": "Flexible hours"}, "fontSize": {"value": 10}, "align": {"value": "left"}}}},
        {"type": "rect", "encode": {"enter": {"x": {"value": 240}, "y": {"value": 0}, "width": {"value": 10}, "height": {"value": 10}, "fill": {"value": "#d3d3d3"}}}},
        {"type": "text", "encode": {"enter": {"x": {"value": 255}, "y": {"value": 9}, "text": {"value": "No Working hours"}, "fontSize": {"value": 10}, "align": {"value": "left"}}}},
        
        {"type": "rect", "encode": {"enter": {"x": {"value": 0}, "y": {"value": 15}, "width": {"value": 10}, "height": {"value": 10}, "fill": {"value": "#b2df8a"}}}},
        {"type": "text", "encode": {"enter": {"x": {"value": 15}, "y": {"value": 24}, "text": {"value": "Support hours"}, "fontSize": {"value": 10}, "align": {"value": "left"}}}},
        {"type": "rule", "encode": {"enter": {"x": {"value": 122}, "y": {"value": 13}, "x2": {"value": 122}, "y2": {"value": 23}, "stroke": {"value": "black"}}}},
        {"type": "symbol", "encode": {"enter": {"x": {"value": 122}, "y": {"value": 22}, "shape": {"value": "triangle-up"}, "size": {"value": 25}, "fill": {"value": "black"}}}},
        {"type": "rule", "encode": {"enter": {"x": {"value": 128}, "y": {"value": 15}, "x2": {"value": 128}, "y2": {"value": 24}, "stroke": {"value": "black"}}}},
        {"type": "symbol", "encode": {"enter": {"x": {"value": 128}, "y": {"value": 15}, "shape": {"value": "triangle-down"}, "size": {"value": 25}, "fill": {"value": "black"}}}},
        {"type": "text", "encode": {"enter": {"x": {"value": 135}, "y": {"value": 24}, "text": {"value": "Site Opening"}, "fontSize": {"value": 10}, "align": {"value": "left"}}}}
      ]
    },
    {
      "type": "text",
      "interactive": false,
      "encode": {
        "enter": {
          "x": {"field": {"group": "width"}, "mult": 0.5},
          "y": {"field": {"group": "height"}, "mult": 0.5},
          "text": {"value": "No Schedule Data for Today"},
          "fontSize": {"value": 12},
          "fontWeight": {"value": "bold"},
          "fill": {"value": "gray"},
          "align": {"value": "center"}
        },
        "update": {
          "opacity": [{"test": "has_data_for_today", "value": 0}, {"value": 1}]
        }
      }
    },
    {
      "type": "rect",
      "from": {"data": "schedule_bands"},
      "encode": {
        "enter": {
          "x": {"scale": "xscale", "field": "startTime"},
          "x2": {"scale": "xscale", "field": "endTime"},
          "y": {"scale": "yscale", "field": "ScheduleType"},
          "height": {"scale": "yscale", "band": 1},
          "fill": {"scale": "color", "field": "display_type"}
        },
        "update": {
          "opacity": [{"test": "has_data_for_today", "value": 1}, {"value": 0}]
        }
      }
    },
    {
      "type": "rule",
      "from": {"data": "site_opening_start"},
      "encode": {
        "enter": {"x": {"scale": "xscale", "field": "time_value"}, "y": {"value": 0}, "y2": {"value": 50}, "stroke": {"value": "black"}, "strokeWidth": {"value": 2}},
        "update": {"opacity": [{"test": "has_data_for_today", "value": 1}, {"value": 0}]}
      }
    },
    {
      "type": "symbol",
      "from": {"data": "site_opening_start"},
      "encode": {
        "enter": {"x": {"scale": "xscale", "field": "time_value"}, "y": {"value": 50}, "shape": {"value": "triangle-up"}, "size": {"value": 50}, "fill": {"value": "black"}},
        "update": {"opacity": [{"test": "has_data_for_today", "value": 1}, {"value": 0}]}
      }
    },
    {
      "type": "rule",
      "from": {"data": "site_opening_end"},
      "encode": {
        "enter": {"x": {"scale": "xscale", "field": "time_value"}, "y": {"value": 0}, "y2": {"value": 50}, "stroke": {"value": "black"}, "strokeWidth": {"value": 2}},
        "update": {"opacity": [{"test": "has_data_for_today", "value": 1}, {"value": 0}]}
      }
    },
    {
      "type": "symbol",
      "from": {"data": "site_opening_end"},
      "encode": {
        "enter": {"x": {"scale": "xscale", "field": "time_value"}, "y": {"value": 3}, "shape": {"value": "triangle-down"}, "size": {"value": 50}, "fill": {"value": "black"}},
        "update": {"opacity": [{"test": "has_data_for_today", "value": 1}, {"value": 0}]}
      }
    },
    {
      "type": "rule",
      "encode": {
        "update": {
          "x": {"scale": "xscale", "signal": "remote_hour_decimal"},
          "y": {"value": 2}, "y2": {"value": 50},
          "stroke": {"value": "red"},
          "strokeWidth": {"value": 2}
        }
      }
    },
    {
      "type": "text",
      "encode": {
        "update": {
          "x": {"scale": "xscale", "signal": "remote_hour_decimal"},
          "y": {"value": 3},
          "text": {"signal": "remote_time_label"},
          "baseline": {"value": "bottom"},
          "align": {"value": "center"},
          "fill": {"value": "red"}
        }
      }
    }
  ]
}​

Please find an example of my JSON file stored in Sharepoint (Text multi-lines)

{"schedule_rules":[{"day_num":"0","segments":[{"approval_needed":null,"communication_needed":"Mandatory","end_time":"23:59","start_time":"00:00","type":"No Production"}]},{"day_num":"1","segments":[{"approval_needed":"Yes","communication_needed":null,"end_time":"12:30","start_time":"08:30","type":"Normal"},{"approval_needed":"Yes","communication_needed":null,"end_time":"16:30","start_time":"13:30","type":"Normal"}]},{"day_num":"2","segments":[{"approval_needed":"Yes","communication_needed":null,"end_time":"12:30","start_time":"08:30","type":"Normal"},{"approval_needed":"Yes","communication_needed":null,"end_time":"16:30","start_time":"13:30","type":"Normal"}]},{"day_num":"3","segments":[{"approval_needed":"Yes","communication_needed":null,"end_time":"12:30","start_time":"08:30","type":"Normal"},{"approval_needed":"Yes","communication_needed":null,"end_time":"16:30","start_time":"13:30","type":"Normal"}]},{"day_num":"4","segments":[{"approval_needed":"Yes","communication_needed":null,"end_time":"12:30","start_time":"08:30","type":"Normal"},{"approval_needed":"Yes","communication_needed":null,"end_time":"16:30","start_time":"13:30","type":"Normal"}]},{"day_num":"5","segments":[{"approval_needed":"Yes","communication_needed":null,"end_time":"12:30","start_time":"08:30","type":"Normal"},{"approval_needed":"Yes","communication_needed":null,"end_time":"16:30","start_time":"13:30","type":"Normal"}]},{"day_num":"6","segments":[{"approval_needed":null,"communication_needed":"Mandatory","end_time":"23:59","start_time":"00:00","type":"No Production"}]}]}

How to import this JSON in Deneb ? I tried with JSON.parse but fails each time

Thank you for your support

[lbendlin]

The Deneb visual is sandboxed. You will not be able to access any external data from it. Your JSON would have to be stored in a column in your data and handed over to the visual as a datum.

[DGPBi]

Hi Ibendlin.
Forget the problem about Dynamic data source (I updated my first message for not creating more confusion).I found where come from the problem and it is not linked to my deneb project ..was a test table I didn't remove.
But the question is still completely valid.
I have a table in my Power BI with this JSON (provided as example) in a text column.
I was able to provide this column to deneb and in the deneb debugger I can see those data. But when I use JSON.parse I have lot of different errrors. So, in other words I don't know how to use the JSON.parser in deneb. I'm using Vega 6.2.0 included in Power BI
So if you have an idea ??

[lbendlin]

But when I use JSON.parse I have lot of different errrors.

What are these?

I would recommend you start with a much smaller sample.  Single column, single JSON value etc.  Then try to parse that and render in your visual.

If you provide a small proof of concept it will be easier to help.

[DGPBi]

Hi Ibendlin,

Thanks for your answer.
Please find here a small code with an embedded example. I still have : [Error] Illegal callee type: MemberExpression as result and I don't see my 

my_json_column in data

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "description": "A minimal Vega example demonstrating how to parse a JSON string and create a simple chart.",
  "width": 500,
  "height": 200,
  "padding": 5,

  "data": [
    {
      "name": "source",
      "values": [
        {
          "my_json_column": "{\"schedule_rules\":[{\"day_num\":\"0\",\"segments\":[{\"end_time\":\"23:59\",\"start_time\":\"00:00\",\"type\":\"No Production\"}]},{\"day_num\":\"1\",\"segments\":[{\"end_time\":\"12:30\",\"start_time\":\"08:30\",\"type\":\"Normal\"},{\"end_time\":\"16:30\",\"start_time\":\"13:30\",\"type\":\"Normal\"}]},{\"day_num\":\"2\",\"segments\":[{\"end_time\":\"12:30\",\"start_time\":\"08:30\",\"type\":\"Normal\"},{\"end_time\":\"16:30\",\"start_time\":\"13:30\",\"type\":\"Normal\"}]},{\"day_num\":\"3\",\"segments\":[{\"end_time\":\"12:30\",\"start_time\":\"08:30\",\"type\":\"Normal\"},{\"end_time\":\"16:30\",\"start_time\":\"13:30\",\"type\":\"Normal\"}]},{\"day_num\":\"4\",\"segments\":[{\"end_time\":\"12:30\",\"start_time\":\"08:30\",\"type\":\"Normal\"},{\"end_time\":\"16:30\",\"start_time\":\"13:30\",\"type\":\"Normal\"}]},{\"day_num\":\"5\",\"segments\":[{\"end_time\":\"12:30\",\"start_time\":\"08:30\",\"type\":\"Normal\"},{\"end_time\":\"16:30\",\"start_time\":\"13:30\",\"type\":\"Normal\"}]},{\"day_num\":\"6\",\"segments\":[{\"end_time\":\"23:59\",\"start_time\":\"00:00\",\"type\":\"No Production\"}]}]}"
        }
      ],
      "transform": [
        {
          "type": "formula",
          "as": "parsed_json",
          "expr": "JSON.parse(datum.my_json_column)"
        },
        {
          "type": "flatten",
          "fields": ["parsed_json.schedule_rules"],
          "as": ["rule"]
        },
        {
          "type": "flatten",
          "fields": ["rule.segments"],
          "as": ["segment"]
        },
        {
          "type": "formula",
          "as": "startTime",
          "expr": "toNumber(split(datum.segment.start_time, ':')[0]) + (toNumber(split(datum.segment.start_time, ':')[1]) / 60)"
        },
        {
          "type": "formula",
          "as": "endTime",
          "expr": "toNumber(split(datum.segment.end_time, ':')[0]) + (toNumber(split(datum.segment.end_time, ':')[1]) / 60)"
        },
        {
          "type": "formula",
          "as": "day",
          "expr": "datum.rule.day_num"
        },
        {
          "type": "formula",
          "as": "type",
          "expr": "datum.segment.type"
        }
      ]
    }
  ],

  "scales": [
    {
      "name": "yscale",
      "type": "band",
      "range": "height",
      "domain": {"data": "source", "field": "day"},
      "padding": 0.1
    },
    {
      "name": "xscale",
      "type": "linear",
      "range": "width",
      "domain": [0, 24]
    },
    {
      "name": "color",
      "type": "ordinal",
      "range": "category",
      "domain": {"data": "source", "field": "type"}
    }
  ],

  "axes": [
    {"orient": "bottom", "scale": "xscale", "tickCount": 24, "title": "Hour of Day"},
    {"orient": "left", "scale": "yscale", "title": "Day of Week"}
  ],

  "legends": [
    {"fill": "color", "title": "Segment Type"}
  ],

  "marks": [
    {
      "type": "rect",
      "from": {"data": "source"},
      "encode": {
        "enter": {
          "y": {"scale": "yscale", "field": "day"},
          "height": {"scale": "yscale", "band": 1},
          "x": {"scale": "xscale", "field": "startTime"},
          "x2": {"scale": "xscale", "field": "endTime"},
          "fill": {"scale": "color", "field": "type"}
        }
      }
    }
  ]
}

[dm-p · Accepted solution]

JSON.parse() is not a valid expression in Vega. For security reasons, you don't have access to JavaScript in Vega, and they provide many expressions that solve a lot of common scenarios. Even if Vega did have access to JS through its runtime, we would not be able to execute it in a certified visual, as MS certification rules prohibit it. In addition to it being a powerful language, it's one of the reasons I chose Vega for this visual, as their parser solves many challenges that the certification rules impose without me having to solve them myself.

Right now, I don't think that what you're attempting will be possible. If you can add a column that can potentially work in a comma-separated format, you might be able to do something with that (e.g., split() ) in a transform to turn it into an array of values, but you won't be able to parse a JSON string into an object.

Regards,

Daniel