-
Notifications
You must be signed in to change notification settings - Fork 0
Examples
Through the python-pbi package, you can interact with the Power BI service to download and upload reports, etc. This will require the Power BI Powershell.
Add all the required configuration to create the Pbi object like so:
config = {
'DOWNLOAD_FOLDER': './downloads/',
'UPLOAD_FOLDER': './upload/'
}
You can download a report from the Power BI service (providing you have installed the Power BI Powershell) like so:
from pbi.pbi import PowerBI
from tests.config import config
test_pbi = PowerBI(config)
report = test_pbi.download(
'test_report',
test_pbi.download_folder,
workspace_id=<your_powerbi_workspace_id>
)
NB: Power BI service will ask you to authenticate and download all reports containing the string 'test_report'.
You can upload a report to the Power BI service (providing you have installed the Power BI Powershell) like so:
from pbi.pbi import PowerBI
from tests.config import config
test_pbi = PowerBI(config)
test_pbi.upload(
test_pbi.upload_folder,
workspace_id=<your_workspace_id>
)
NB: Power BI service will ask you to authenticate and upload all reports contained in the given folder to the Power BI workspace.
The python-pbi package enables to create Python PbiReport objects to manipulate them.
Assume you have a Power BI report named test_report.pbix in the folder test_folder. You can bring this file to memory as a Python PbiReport object using the commands below:
from pbi.report import PbiReport
report = PbiReport(
'test_folder',
'test_report'
)
NB: At this stage, the Power BI report is accessible in memory. Please check the PbiReport section for a more detailed description.
Assume you have a PbiReport object in memory named report. After manipulating it, you can easily save it via the command below:
report.save()
NB: The save method also allows to change the dataset connection if needed.
You can easily copy a Power BI report to a new location by using the copy method. Note you will have to save the report to actually create the Power BI file in the desired location.
from pbi.report import PbiReport
report = PbiReport(
'test_folder',
'test_report'
)
new_report = report.copy(
new_name='updated_test_report',
new_folder='updated_report_name'
)
new_report.save()
Some Power BI implementation might make the report structure more complex than needed. Here are a couple of ways to simplify the reports automatically.
As shown in the layout object, the pages of a report are stored as a list, along with some numbering (ordinal). You can reset the numbering using the following method:
report._update_section_id()
Bookmarks can also become a mess to maintain. Imagine you originally create quite a few bookmarks but eventually, as your report evolves, delete the associated visuals. It is not likely that you will remember which bookmarks used which visual, and eventually empty bookmarks will remain. The method below helps tidying empty bookmarks (using no visuals at all):
report.tidy_bookmarks()
Assume your report has several pages, named 'Page 1', 'Page 2', 'Page 3', etc. You can easily remove pages from such a report, or select only some of the pages, by using the method below.
report.select_pages(
[
'Page 1',
'Page 3'
]
)
NB: Selecting pages will tidy bookmarks by default.
Have you ever tried copying complex pages from one Power BI report to another? It is very tiresome, if at all possible, and still requires extensive effort to reproduce bookmarks, etc. Not anymore! With python-pbi, copying pages to another report also brings the bookmarks along, thanks to the merge method. See an example below, where we copy the last page of report0.pbix to report.pbix.
from pbi.report import PbiReport
# Creating objects
report0 = PbiReport(
'temp_folder',
'report0'
)
report = PbiReport(
'temp_folder',
'report'
)
# Filtering the interesting page(s)
report0 = report0.select_pages(['Last Page'])
# Merging both reports (includes bringing bookmarks!)
report.merge(report0)
NB: In the example above the file report0.pbix was not updated, as we did not apply the save method to report0.
NB: Merging reports does not currently support bringing along inserted images or custom visuals.
The overall structure of the report layout is explained in more details in the object section.
To copy visuals (duplicating, or copying from one page to another, even from a different report), you will have to select the required visuals and ad them onto the appropriate page. Assuming you have a report with a 'Home Page' and a 'Page 1', below is an example copying a group of visuals (called header), as well as buttons:
# Selecting the pages
home_page = report.get_page('Home')
page1 = report.get_page('Page 1')
# Selecting the header group of visuals and adding it onto the other page
header = home_page.get_visual_group('Header')
added_header = page1.add_visuals(header)
# Selecting all visuals containing the string 'Button' and adding them onto the other page
buttons = home_page.get_visuals('Button')
added_buttons = page1.add_visuals(buttons)
The objects exposed within the PbiReport allow to manipulate the visuals, filters, etc.
Sometimes, it can be useful to replace some text in the whole report. An example use case is when the data model has been updated, and some columns or some data values used in the Power BI filters have changed as a consequence. You can use the method below to replace all strings matching a certain value:
report.layout.replace(
'old_name',
'new_name'
)
NB: The method above is very brutal and will replace all matchings strings in the Power BI report when exported as a json string. It might break the structure of the Power BI file resulting in errors or corrupted files. Ensure you are using precise names and use with caution.
To hide a visual or a page, you can use the hide method:
page_to_hide = report.get_page(<page_to_hide_name>)
page_to_hide.hide()
visual_to_hide = page.get_visuals(<visual_to_hide_name>)[0]
visual_to_hide.hide()
The font is exposed inside the config object of a visual. You can for example use the command below to set the title font size:
title = home_page.get_visuals('Awesome title')[0]
title['config']['singleVisual']['objects']['general'][0]['properties']['paragraphs'][0]['textRuns'][0]['textStyle']['fontSize'] = '20pt'
Power BI slicers (often shown as dropdowns) have several options that might become inconsistent within a report if developers are not given clear guidelines (whether to use CTRL for multiselect, add a search option, select all, etc.). Try some of the commands below.
report.update_multiselect()
report.add_search()
NB: The commands above will print the number of updates for each page in the report.
The 'keep layer order' attribute can be very annoying, as a report user can bring visuals to the front by clicking by mistake around the report, thus hiding other visuals. We can set this attribute to true automatically to prevent his behaviour by running the command below on the report itself:
report.update_keep_layer_oder()
NB: The command above will print the number of updates for each page in the report.
Headers can also be very annoying for the report end users, especially when they appear for irrelevant visuals (shapes, etc.). We can deactivate headers by running the command below on the report itself:
report.disable_headers(
types_to_filter=['shape', 'textbox']
)
NB: The command above will print the number of updates for each page in the report.
Many Power BI objects can be associated with a filter (the report itself, a page, a slicer, a visual, etc.). These are implemented in python-pbi using the _PbiFilterObject class. For each of these, you can select a filter, update its value, etc.
slicer.update_filter(<slicer_filter_name>, <prefered_default_value>)
page.get_filter(<page_filter_name>)
report.add_filters(<list_of_awesome_filters>)
NB: This is still work in progress and not all filter features are currently covered.
Apart from saving valuable effort, automating download and upload can also allow for seamless deployment from development environment to test or production.
You can build your own CustomPbiReport class and incorporate inside the save method some of the examples above to ensure consistency of visuals and higher quality overall:
You can save a PowerBI report with a different dataset connection. This enables automatic deployment from a development environment to the production environment for example. See an example below:
from pbi.pbi import PowerBI
from pbi.report import PbiReport
from tests.config import config
test_pbi = PowerBI(config)
# Download the report from the development workspace into an arbitrary folder
test_pbi.download(
'test_report',
test_pbi.download_folder,
workspace_id=<development_workspace_id>
)
# Brings the report to memory with python-pbi
dev_report = PbiReport(
'temp_folder',
'test_report'
)
# Copies the report to the upload location with the production name
prod_report = dev_report.copy(
new_name='report',
new_folder=test_pbi.upload_folder
)
# Changes the dataset from development to production dataset
prod_report.save(
dataset_id_from=<development_dataset_id>,
dataset_id_to=<production_dataset_id>,
)
# Uploads the contents of the folder to the production workspace
test_pbi.upload(
test_pbi.upload_folder,
workspace_id=<production_workspace_id>
)
NB: For downloading and uploading, the script above assumes you have installed the Power BI Powershell.
It is sometimes required not to expose sensitive data to lower environments (development, test), like URLs, etc. To update such data on-the-fly while deploying to production via the deployment script, you can add the few lines below:
dct = {
'safe_name_1': 'sensitive_name_1',
'safe_name_2': 'sensitive_name_2'
}
report.update_names(dct)
In case you want to deploy your report to production but some charts are still under development, you can replace them by a placeholder message inside the deployment script. Assuming a placeholder message is available in a Container object named placeholder_message, you can run the script below:
page = report.get_page(<page_name_with_the_WIP_visual>)
wip_visual = page.get_visuals(<visual_name_under_development>)
page.replace_visual_by_placeholder(
wip_visual,
placeholder_message
)
In case your report has been deployed to production as a desktop report, but you are still working on the mobile version, you can incorporate the following command in the deployment script to remove all the visuals from the mobile report (the mobile report will then show the Power BI default).
report.reset_mobile_screen()
In case some pages have their mobile report fully developed, you can also include only a list of pages in the command above:
report.reset_mobile_screen(pages=['WIP Page 1', 'WIP Page 3'])
You can even add a custom message to the mobile reports while they are being developed. The default message will come from a separate report, named custom_mobile_message in the example below.
from pbi.pbi import PbiReport
custom_message_report = PbiReport(
'temp_folder',
'custom_mobile_message'
)
report.reset_mobile_screen(
default_message_report=custom_message_report
)