Forum Discussion

akj2784's avatar
akj2784
Icon for Post Partisan rankPost Partisan
8 years ago

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

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

Need Help

4 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      akj2784
      Icon for Post Partisan rankPost Partisan
      8 years ago

      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.

  • mikorym's avatar
    mikorym
    Frequent Visitor
    8 years ago
    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/overview/

    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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.