PowerBI Custom Visual - Table data binding

Question

Also asked this on the PowerBI forum.

I am trying to change sampleBarChart PowerBI visual to use a "table" data binding instead of current "categorical". First goal is to build a simple table visual, with inputs "X", "Y" and "Value".

Both data bindings are described on the official wiki. This is all I could find:

I cannot find any example visuals which use it and are based on the new API.

---

From the image above, a table object has "rows", "columns", "totals" and "identities". So it looks like rows and columns are my x/y indexes, and totals are my values?

This is what I tried. (Naming is slightly off as most of it came from existing barchart code)

Data roles:

   {   "displayName": "Category1 Data",
        "name": "category1",
        "kind": 0},
   {   "displayName": "Category2 Data",
        "name": "category2",
        "kind": 0},
   {   "displayName": "Measure Data",
        "name": "measure",
        "kind": 1}

Data view mapping:

"table": {
    "rows": {"for": {"in": "category1"}},
    "columns": {"for": {"in": "category2"}},
    "totals": {"select": [{"bind": {"to": "measure"}}]}
}

Data Point class:

interface BarChartDataPoint {
    value: number;
    category1: number;
    category2: number;
    color: string;
};

Relevant parts of my visualTransform():

    ...
let category1 = categorical.rows;
let category2 = categorical.columns;
let dataValue = categorical.totals;
    ...
for (let i = 1, len = category1.length; i <= len; i++) {
for (let j = 1, jlen = category2.length; j <= jlen; j++) {
    {
        barChartDataPoints.push({
            category1: i,
            category2: j,
            value: dataValue[i,j],
            color: "#555555"//for now
    });
}
    ...

Test data looks like this:

__1_2_3_
1|4 4 3
2|4 5 5
3|3 6 7 (total = 41)

The code above fills barChartDataPoints with just six data points:

(1; 1; 41),
(1; 2; undefined),
(2; 1; 41),
(2; 2; undefined),
(3; 1; 41),
(3; 2; undefined).

Accessing zero indeces results in nulls.

Q: Is totals not the right measure to access value at (x;y)? What am I doing wrong?

---

Any help or direction is very appreciated.

Answer 1

User @RichardL shared this link on the PowerBI forum. Which helped quite a lot.

"Totals" is not the right measure to access value at (x;y).

It turns out Columns contain column names, and Rows contain value arrays which correspond to those columns.

From the link above, this is how table structure looks like:

{
 "columns":[
  {"displayName": "Year"},
  {"displayName": "Country"},
  {"displayName": "Cost"}
 ],
 "rows":[
  [2014, "Japan", 25],
  [2015, "Japan", 30],
  [2016, "Japan", 18],
  [2015, "North America", 14],
  [2016, "North America", 30],
  [2016, "China", 100]
 ]
}

You can also view the data as your visual receives it by placing this

window.alert(JSON.stringify(options.dataViews))

In your update() method. Or write it in html contents of your visual.

Answer 2

This was very helpful but it shows up a few fundamental problems with the data management of PowerBI for a custom visual. There is no documentation and the process from Roles to mapping to visualTransform is horrendous because it takes so much effort to rebuild the data into a format that is usable consistently with D3.

Commenting on user5226582's example, for me, columns is presented in a form where I have to look up the Roles property to be able to understand the order of data presented in the rows column array. displayName offers no certainty. For exampe, if a user uses the same field in two different dataRoles then it all gets crazily awry.

I think the safest approach is to build a new array inside visualTransform using the known well-field names (the "name" property in dataRoles), then iterate columns interrogating the Roles property to establish an index to the rows array items. Then use that index to populate the new array reliably. D3 then gobbles that up.

I know that's crazy, but at least it means reasonably consistent data and allows for the user selecting the same data field more than once or choosing count instead of column value.

All in all, I think this area needs a lot of attention before custom Visuals can really take off.