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.
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.
The package satisfies five main functions:
- Handles OAuth 2.0 authentication between R sessions and Power BI applications.
- Pulls metadata about datasets, dashboards and tiles, reports, import files and user groups.
- Creates datasets, imports data frames as tables and streams data to Power BI tables.
- Managing existing datasets with update, truncation and delete functions.
- 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.
- 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.
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.
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.
# 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()
pbiCreateDatasetFromDataFrame( name = "Iris Dataset", df = iris, tableName = "Iris Data Frame", FIFO = "basicFIFO" # Enable basic first-in-first-out for streaming. )
# 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" )
# 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()