| Title: | PowerBI Connectivity |
|---|---|
| Description: | Defines functions needed to connect to PowerBI dataflows/datasets. |
| Authors: | Healthcare Quality Intelligence Unit (Western Australia Health) [aut, cre] |
| Maintainer: | Healthcare Quality Intelligence Unit (Western Australia Health) <[email protected]> |
| License: | GPL (>= 3) + file LICENSE |
| Version: | 0.0.19 |
| Built: | 2026-10-13 05:58:08 UTC |
| Source: | https://github.com/AUS-DOH-Safety-and-Quality/qiverse |
A collection of R functions for working with PowerBI Dataflows and Datasets
Maintainer: Healthcare Quality Intelligence Unit (Western Australia Health) [email protected]
Authors:
Healthcare Quality Intelligence Unit (Western Australia Health) [email protected]
Useful links:
https://github.com/AUS-DOH-Safety-and-Quality/qiverse/qiverse.powerbi
Report bugs at https://github.com/AUS-DOH-Safety-and-Quality/qiverse/issues
For more efficient storage, PowerBI will store strings in a compressed binary format - compressed using the deflate algorithm and then represented as a base64 string.
This function takes the base64 string and decompresses it to return the original string.
decompress_string(compressed_string)decompress_string(compressed_string)
compressed_string |
The base64 string of the compressed data. |
Original string after decompression.
table_str <- "i45W8lXSUfJNzElVitWJVnIDctxSc2HcCCDXMS+/JCO1SKEktSgXLBgKFAzNy87LL88D8/2AfL/8EoXigtTkzLTM1BSl2FgA" decompress_string(table_str) |> cat() # [["M","Male"],["F","Female"],["X","Another term"],["U","Unknown"],["N","Not specified"]]table_str <- "i45W8lXSUfJNzElVitWJVnIDctxSc2HcCCDXMS+/JCO1SKEktSgXLBgKFAzNy87LL88D8/2AfL/8EoXigtTkzLTM1BSl2FgA" decompress_string(table_str) |> cat() # [["M","Male"],["F","Female"],["X","Another term"],["U","Unknown"],["N","Not specified"]]
This function provides the ability to download tables from within PowerBI dataflow themselves, rather than requiring that they first be exposed via a PowerBI dataset.
download_dataflow_table( workspace_name, dataflow_name, table_name, access_token = NULL, destfile = NULL, verbose = TRUE )download_dataflow_table( workspace_name, dataflow_name, table_name, access_token = NULL, destfile = NULL, verbose = TRUE )
workspace_name |
The PowerBI workspace name. |
dataflow_name |
The name of the PowerBI Dataflow within the workspace. |
table_name |
The name of the table within the PowerBI Dataflow to be accessed. |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
destfile |
Optional output filepath to save raw dataflow CSV file to.
If |
verbose |
Whether to print status messages while the function is running. Default is TRUE. |
A data.frame of the PowerBI table from the PowerBI dataflow if
destfile = NULL, otherwise none
## Not run: # Create PowerBI Dataflow azure token tk <- get_az_tk('pbi_df') # Load AD dummy file from PowerBI dataflow ad_dummy <- download_dataflow_table( workspace_name = "My Workspace Name", dataflow_name = "My Dataflow Name", table_name = "My Table Name", access_token = tk$credentials$access_token ) # Download AD dummy file from PowerBI dataflow to local .csv file download_dataflow_table( workspace_name = "My Workspace Name", dataflow_name = "My Dataflow Name", table_name = "My Table Name", access_token = tk$credentials$access_token, destfile = "my_table_name.csv" ) ## End(Not run)## Not run: # Create PowerBI Dataflow azure token tk <- get_az_tk('pbi_df') # Load AD dummy file from PowerBI dataflow ad_dummy <- download_dataflow_table( workspace_name = "My Workspace Name", dataflow_name = "My Dataflow Name", table_name = "My Table Name", access_token = tk$credentials$access_token ) # Download AD dummy file from PowerBI dataflow to local .csv file download_dataflow_table( workspace_name = "My Workspace Name", dataflow_name = "My Dataflow Name", table_name = "My Table Name", access_token = tk$credentials$access_token, destfile = "my_table_name.csv" ) ## End(Not run)
execute_xmla_query()
and execute_rest_query() functions.Download a specified dataset table into R using either the XMLA or REST API
endpoints. This is a convenience wrapper around the execute_xmla_query()
and execute_rest_query() functions.
download_dataset_table( workspace, dataset, table, method = "arrow", access_token = NULL )download_dataset_table( workspace, dataset, table, method = "arrow", access_token = NULL )
workspace |
Name of the workspace containing dataflow |
dataset |
Name of the dataset containing table |
table |
Name of the table to download |
method |
The API to use for downloading the table. Valid values are "arrow" (the default), XMLA" and "REST" |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
DataFrame containing downloaded table
This endpoint has no restrictions on the size of the result and will transfer data in a compressed format (Apache Arrow), and should be preferred.
execute_arrow_query(workspace, dataset, query, access_token = NULL)execute_arrow_query(workspace, dataset, query, access_token = NULL)
workspace |
Name of the workspace containing dataflow |
dataset |
Name of the dataset to execute query against |
query |
DAX query to execute |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
DataFrame containing results of query
Execute a DAX query against a specified PowerBI Dataset using the REST API endpoint.
execute_rest_query(workspace, dataset, query, access_token = NULL)execute_rest_query(workspace, dataset, query, access_token = NULL)
workspace |
Name of the workspace containing dataflow |
dataset |
Name of the dataset to execute query against |
query |
DAX query to execute |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
DataFrame containing results of query
Execute a DAX query against a specified PowerBI Dataset using the XMLA API endpoint.
execute_xmla_query(workspace, dataset, query, access_token = NULL)execute_xmla_query(workspace, dataset, query, access_token = NULL)
workspace |
Name of the workspace containing dataflow |
dataset |
Name of the dataset to execute query against |
query |
DAX query to execute |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
DataFrame containing results of query
Request metadata for all dataflows in specified workspace
list_dataflows(workspace, access_token = NULL)list_dataflows(workspace, access_token = NULL)
workspace |
Name of the workspace containing dataflows |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
DataFrame containing the names, GUIDs, and descriptions for all dataflows in workspace
Request metadata for all datasets in specified workspace
list_datasets(workspace, access_token = NULL)list_datasets(workspace, access_token = NULL)
workspace |
Name of the workspace containing datasets |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
DataFrame containing the names, GUIDs, and descriptions for all datasets in workspace
Request metadata for all Workspaces in Tenant
list_workspaces(access_token = NULL)list_workspaces(access_token = NULL)
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
DataFrame containing the names, GUIDs, and capacity GUIDs for all workspaces in tenant.
This function provides the ability to refresh PowerBI dataflows, via an R wrapper using the PowerBI REST API.
refresh_dataflow( workspace_name, dataflow_name, access_token = NULL, verbose = TRUE )refresh_dataflow( workspace_name, dataflow_name, access_token = NULL, verbose = TRUE )
workspace_name |
The PowerBI workspace name. |
dataflow_name |
The name of the PowerBI Dataflow within the workspace. |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
verbose |
Whether to print status messages while the function is running. Default is TRUE. |
A response object from the POST request.
## Not run: # Create PowerBI Dataflow azure token tk <- get_az_tk('pbi_df') # Load AD dummy file from PowerBI dataflow refresh_result <- refresh_dataflow( workspace_name = "My Workspace Name", dataflow_name = "My Dataflow Name", access_token = tk$credentials$access_token ) ## End(Not run)## Not run: # Create PowerBI Dataflow azure token tk <- get_az_tk('pbi_df') # Load AD dummy file from PowerBI dataflow refresh_result <- refresh_dataflow( workspace_name = "My Workspace Name", dataflow_name = "My Dataflow Name", access_token = tk$credentials$access_token ) ## End(Not run)
This function provides the ability to change the compute engine behaviour for a selected PowerBI dataflow, via an R wrapper using the PowerBI REST API.
update_dataflow_compute_engine( workspace_name, dataflow_name, compute_engine, access_token = NULL )update_dataflow_compute_engine( workspace_name, dataflow_name, compute_engine, access_token = NULL )
workspace_name |
The PowerBI workspace name. |
dataflow_name |
The name of the PowerBI Dataflow within the workspace. |
compute_engine |
The compute engine behaviour setting to be applied. Must be one of "computeOptimized", "computeOn" or "computeDisabled". |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
You have to be the owner of the PowerBI dataflow to use this function, otherwise there will be an 401 unauthorised error.
A response object from the PATCH request.
## Not run: # Create PowerBI Dataflow azure token tk <- get_az_tk('pbi_df') # Load AD dummy file from PowerBI dataflow response <- update_dataflow_compute_engine( workspace_name = "My Workspace Name", dataflow_name = "My Dataflow Name", compute_engine = "computeOptimized", access_token = tk$credentials$access_token ) ## End(Not run)## Not run: # Create PowerBI Dataflow azure token tk <- get_az_tk('pbi_df') # Load AD dummy file from PowerBI dataflow response <- update_dataflow_compute_engine( workspace_name = "My Workspace Name", dataflow_name = "My Dataflow Name", compute_engine = "computeOptimized", access_token = tk$credentials$access_token ) ## End(Not run)
This function provides the ability to change the storage mode for a selected PowerBI dataset, via an R wrapper using the PowerBI REST API.
update_dataset_storage_mode( workspace_name, dataset_name, storage_mode, access_token = NULL )update_dataset_storage_mode( workspace_name, dataset_name, storage_mode, access_token = NULL )
workspace_name |
The PowerBI workspace name. |
dataset_name |
The name of the PowerBI Dataflow within the workspace. |
storage_mode |
The compute engine behaviour setting to be applied. Must be one of "Abf" (small semantic model format) or "PremiumFiles" (large semantic model format). |
access_token |
Token for authorising the connection to PowerBI. Valid options are:
|
You do not have to be the owner of the semantic model to use this function.
A response object from the PATCH request.
## Not run: # Create PowerBI Dataflow azure token tk <- get_az_tk('pbi_df') response <- update_dataset_storage_mode( workspace_name = "My Workspace Name", dataset_name = "My Dataset Name", storage_mode = "PremiumFiles", access_token = tk$credentials$access_token ) ## End(Not run)## Not run: # Create PowerBI Dataflow azure token tk <- get_az_tk('pbi_df') response <- update_dataset_storage_mode( workspace_name = "My Workspace Name", dataset_name = "My Dataset Name", storage_mode = "PremiumFiles", access_token = tk$credentials$access_token ) ## End(Not run)