PowerBI Leverage with R

PowerBI
R
functions
Leverage PowerBI apps with R

PowerBI it’s a completed tool for creating dashboard, nevertheless, you can make it so much more completed leveraging with others languages, such as, R or Python.

Ryan E Wade conference

When at my work I was assigned to co-created a dashboard in powerBI, I inmediately remember the confrence from Ryan E Wade about levering powerBI with R.

Comparing sales within months - dashboard

This blog contains a very simple dashboard with just one table, my purpose indeed, it is just to show how with R we can create as many columns with hexcode colors to make change a color of a column automatically, making the dashboard reproducible in the time.

library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.5.0     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.1
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

Table Sales

A sales table was created with just seller_id, month and the sales made.

After that, two columns were created to store sales_avg and delays_avg.

Code 
Sales <- tibble::tribble(
  ~seller_id, ~month, ~sales, ~delays_percentage,
  1, 2, 200,0.2,
  1, 1, 400,0.04,
  1, 3, 140,0.29,
  1, 4, 390,0.11,
  1, 5, 260,0.34,
  1, 6, 130,0.23,
  2, 1, 300,0.1,
  2, 2, 317,0.07,
  2, 3, 263,0.13,
  2, 4, 142,0.21,
  2, 5, 361,0.03,
  2, 6, 134,0.16,
  3, 1, 124,0.25,
  3, 2, 374,0.23,
  3, 3, 762,0.2,
  3, 4, 163,0.27,
  3, 5, 186,0.12,
  3, 6, 177,0.09,
) %>% 
  as.data.frame() %>% 
  mutate(
      sales_avg = round(mean(sales), 1),
      delays_avg = round(mean(delays_percentage), 2)        
  ) 

Sales
   seller_id month sales delays_percentage sales_avg delays_avg
1          1     2   200              0.20     267.9       0.17
2          1     1   400              0.04     267.9       0.17
3          1     3   140              0.29     267.9       0.17
4          1     4   390              0.11     267.9       0.17
5          1     5   260              0.34     267.9       0.17
6          1     6   130              0.23     267.9       0.17
7          2     1   300              0.10     267.9       0.17
8          2     2   317              0.07     267.9       0.17
9          2     3   263              0.13     267.9       0.17
10         2     4   142              0.21     267.9       0.17
11         2     5   361              0.03     267.9       0.17
12         2     6   134              0.16     267.9       0.17
13         3     1   124              0.25     267.9       0.17
14         3     2   374              0.23     267.9       0.17
15         3     3   762              0.20     267.9       0.17
16         3     4   163              0.27     267.9       0.17
17         3     5   186              0.12     267.9       0.17
18         3     6   177              0.09     267.9       0.17

Creation of new columns

The principal idea of the following function called compare it’s to generate the number of columns that the final user wants to compare and generate de hexcode column to apply functional conditions in PowerBI.

The compare function get a table, 2 columns to compare and the function to apply. Returns a dataframe with a hexcode color column.

compare <- function(.base, .column1, .column2, .f) {
  column <- paste0("color_",{{ .column1}})
  funcion <- .Primitive({{ .f }})
  maximo <- .f == "max"

 base2 <- .base %>%
    mutate(
      {{ column }} := ifelse(
            !!rlang::sym(.column1) >= funcion(!!rlang::sym(.column2), na.rm = TRUE),  
            ifelse(maximo, "#FF0000", "#00FF00"),
            ifelse(!maximo, "#00FF00","#FF0000")
      )
    ) %>%
    ungroup() %>%
    select({{ column }})
  return(base2)
}

Store the columns you want to compare in column1 and column2, also, write the function you wants to apply (max or min).

column1 <- list("sales", "delays_percentage")
column2 <- list("sales_avg", "delays_avg")
.f<- list("max", "min")

# Iterate through list of lists with pmap
tabla <- purrr::pmap(
  .l = list(column1, column2, .f),
  .f = function(.x, .y, .z){
    Sales %>%
        compare(.x, .y, .z)
  }
) %>%
  # Convert list into a dataframe
  purrr::flatten_df() %>%
  # column bind base with new columns flatten
  cbind(Sales, .)

tabla
   seller_id month sales delays_percentage sales_avg delays_avg color_sales
1          1     2   200              0.20     267.9       0.17     #FF0000
2          1     1   400              0.04     267.9       0.17     #FF0000
3          1     3   140              0.29     267.9       0.17     #FF0000
4          1     4   390              0.11     267.9       0.17     #FF0000
5          1     5   260              0.34     267.9       0.17     #FF0000
6          1     6   130              0.23     267.9       0.17     #FF0000
7          2     1   300              0.10     267.9       0.17     #FF0000
8          2     2   317              0.07     267.9       0.17     #FF0000
9          2     3   263              0.13     267.9       0.17     #FF0000
10         2     4   142              0.21     267.9       0.17     #FF0000
11         2     5   361              0.03     267.9       0.17     #FF0000
12         2     6   134              0.16     267.9       0.17     #FF0000
13         3     1   124              0.25     267.9       0.17     #FF0000
14         3     2   374              0.23     267.9       0.17     #FF0000
15         3     3   762              0.20     267.9       0.17     #FF0000
16         3     4   163              0.27     267.9       0.17     #FF0000
17         3     5   186              0.12     267.9       0.17     #FF0000
18         3     6   177              0.09     267.9       0.17     #FF0000
   color_delays_percentage
1                  #00FF00
2                  #00FF00
3                  #00FF00
4                  #00FF00
5                  #00FF00
6                  #00FF00
7                  #00FF00
8                  #00FF00
9                  #00FF00
10                 #00FF00
11                 #00FF00
12                 #00FF00
13                 #00FF00
14                 #00FF00
15                 #00FF00
16                 #00FF00
17                 #00FF00
18                 #00FF00

Final result in PowerBI

With the following result I invite you to integrate R scripts with PowerBI so you can create powerfull apps.