poweRbi: an R Interface for Power BI Web

#

Announcing my first publicly available R package, poweRbi, which can be used to navigate and stream data via the Power BI REST API for web services.

Visit the GitHub repository

Overview

Using httr and inspired by other R connectors like twitteR, this package aims to provide an interface for R users to navigate the Power BI REST API and develop apps that interact with Power BI web services.

install.packages(“devtools”)
devtools::install_github(“github/olfrost”)

Purpose

The package satisfies five main functions:

  1. Handles OAuth 2.0 authentication between R sessions and Power BI applications.
  2. Pulls metadata about datasets, dashboards and tiles, reports, import files and user groups.
  3. Creates datasets, imports data frames as tables and streams data to Power BI tables.
  4. Managing existing datasets with update, truncation and delete functions.
  5. Provides utilities to infer data types and generate list/JSON objects from data frames.

Some of the more notable use cases for a package like poweRbi are:

  • Streaming data from an external source to a Streaming or PushStreaming Power BI dataset.
  • Creating datasets and tables on the fly and storing the results of R scripts on a Power BI web service.
  • Maintaining datasets, reports and dashboards.
  • Using URLs from report metadata to embed reports as iframes inside your own application.

Important notices:

  • You will need to register a Power BI application before you can use the library, or have application details such as the client ID and client secret of a pre-registered application.
  • At the time of writing, the package is fully usable and produce desired results, however some more rigorous testing, extra functionality and some performance enhancements are on my to-do list.

Background

Necessity is the mother of invention! I built this package off the back of some on-site client work I did recently after discovering that there were no obvious direct connectors out there for R and the Power BI REST API. The use of R inside Power BI for importing, transforming and plotting data is well documented, and publishing to the web from Power BI desktop is something we’ve covered before.

This library removes the need to have Power BI installed to send data to Power BI web services.

Examples

See the demo directory on my GitHub repository for code examples.

Authenticate your session.
pbiAuthenticate(appName, clientId, clientSecret) # This will take you to a sign-in page, or you can cache your authentication details as a .httr-oauth file.

Get metadata about objects in your workspace

# Datasets
pbiListAllDatasets(toDf = FALSE) # Be flexible on whether your data is returned as a list or as a data frame.

# Dashboards
pbiListAllDashboards(toDf = TRUE)

# Imported files
pbiListAllImportedFiles(toDf = TRUE)

# Reports
pbiListAllReports(toDf = TRUE)

# Tables
pbiListAllTables(allDatasets = TRUE)
pbiListAllTables(id = "cfcf6cde-45b7-459d-a8a3-a09644f5cbbe") # A single guid, multiple guids or all.

# Tiles
pbiListAllTiles(allDashboards = TRUE)
pbiListAllTiles(id = "cfbd4b32-3160-437b-8076-94e6cab9aa4a")

# Data sources
pbiListAllDataSources()

# User groups
pbiListAllUserGroups()
Create datasets from data frames
pbiCreateDatasetFromDataFrame(
  name = "Iris Dataset",
  df = iris,
  tableName = "Iris Data Frame",
  FIFO = "basicFIFO" # Enable basic first-in-first-out for streaming.
)
Manage existing datasets
# Use populated (or empty) data frames to update tables in Power BI.
pbiUpdateTableSchema(
  iris3, # Overwrite table schemas.
  guid = "abcdefg",
  tableName = "Iris Data Frame"
)

# Truncate tables.
pbiTruncateDataset(
  guid = "cfcf6cde-45b7-459d-a8a3-a09644f5cbbe",
  tableName = "Triangle"
)

# Delete entire datasets.
pbiDeleteDataset(guid = "cfcf6cde-45b7-459d-a8a3-a09644f5cbbe")

# Create new rows.
pbiAddRowsToTable(
  df = cats, # Use this function to stream data into an appropriate dataset.
  guid = "abcdef",
  tableName = "Iris Data Frame"
)
Utilities
# What data types does Power BI implement?
pbiDataTypes(simple = TRUE, verbose = FALSE)
pbiDatasetTypes()

# Infer data types from a data frame.
sapply(MASS::cats, ._pbiGetDataType)

# Generate JSON objects or lists from data frames to be passed to Power BI.
pbiGenerateTableSchema(
  MASS::cats,
  tableName = "Many Cats"
)

pbiGenerateTableSchema(
  MASS::cats,
  debug = TRUE,
  tableName = "Meowsers in my Trousers"
)

# Automatic token refreshing.
._pbiRefresh() # All handled automatically by pbi functions.
._pbiTokenExpiresIn()

 

Speak to an expert

Data platform consultant

Michael Montgomery

Contact Michael

Latest from this author

Our technologies

The data platform can be thought of as a ‘black box’ of tools for data management, data analytics and decision-making. It is the product of our years of experience in business intelligence and analytics, programming, data science, reporting and big data. Plug in your business problem and the platform will apply the best tools for your application.

OUR TECHNOLOGIES