How to define dataset coming from power BI in R Custom Visual

[akj2784]

I am trying to create a custom visual in Power BI based on an R script much like in the example found at ( http://radacad.com/interactive-map-using-r-and-power-bi-create-custom-visual-part-1 ), Installed node.js, pbiviz successfully. Once I get this custom visual in Power BI Desktop, I see all static data coming from sample iris dataset, But I want this to use my dataset coming from Power BI. how can I edit the script (shown below) to allow Power BI to use my own data rather than the data in the library?

source('./r_files/flatten_HTML.r')

############### Library Declarations ###############libraryRequireInstall("ggplot2");libraryRequireInstall("plotly")
####################################################

################### Actual code ####################g = qplot(`Petal.Length`, data = iris, fill = `Species`, main = Sys.time());
####################################################

############# Create and save widget ###############p = ggplotly(g);internalSaveWidget(p, 'out.html');
####################################################

When I attempt to change "iris" to a dataset, it can't find dataset. I've also tried adding dataset <- data.frame(MyColumns) but that did not help.

If I have table name as Dim - Product, Fact - Sales and Columns as Product Name and Total Sales. How do I define this?

Any tutorial/video which has the example would really help.

Regards,

Akash

[Anonymous]

By default, the dataset exported from Power BI to the R script is named Values. 

It is defined in the file capabilities.json: 

You can change it if you want, its name and its display name (the display name is the name appearing in Power Bi). And you can even use multiple datasets. Here is an helper function to change the name(s) and the display name(s):

library(jsonlite)
fill_capabilities <- function(path, dataNames, dataDisplayNames){
  wd <- setwd(path)
  on.exit(setwd(wd))
  caps <- fromJSON("capabilities.json", simplifyVector = FALSE)
  caps[["dataRoles"]] <- mapply(function(name, displayName){
    list(
      "displayName" = displayName, 
      "kind"        = "GroupingOrMeasure", 
      "name"        = name
    )
  }, dataNames, dataDisplayNames, SIMPLIFY = FALSE, USE.NAMES = FALSE)
  caps[["dataViewMappings"]][[
    1L
  ]][["scriptResult"]][["dataInput"]][["table"]][["rows"]][["select"]] <-
    lapply(dataNames, function(dataName){
      list("for" = list("in" = dataName))
    })
  caps <- toJSON(caps, auto_unbox = TRUE, null = "null", pretty = 2)
  writeLines(caps, "capabilities.json")
}

 The path argument is the path to the folder containing capabiities.json.

[mikorym]

You need to specify incoming data in the capabilities.json file, as explained here: https://microsoft.github.io/PowerBI-visuals/tutorials/funnel-plot-from-r-script-to-r-custom-visual/o...

However, if you want a quick hack, the default mapping in capabilities.json is to have a "Values" variable (which would represent "dataset" if it had been a normal R Visual). This gives at least two possibilities:

Within script.r write the first line "dataset <- Values". Alternatively change the occurences of "Values" to "dataset" in capabilities.json.

Your previous R code should then port over to script.r with minimal issues. Section 3.1 of the link given above also mentions this.

[v-yuta-msft]

Hi akj2784,

I have made a sample table like this:

After all steps done, click R visual, drag columns to R visual, then you can modify the sample code like below:

# Check if all packages have been installed

library("ggplot2")

library("plotly")

library("htmlwidgets")

library("XML")

source('C:/Users/Administrator/Desktop/sampleRHTMLVisual/r_files/flatten_HTML.r')# Path of  Source Code

qplot(`Key`, data = dataset, fill = `Value`, main = Sys.time())

Regards,

Jimmy Tao

[akj2784]

This is R Custom Visual. I am talking about the custom visual wherein we can write R code and create our own visual package using pbiviz.