Power BI Screenshot Automation

Filters, Clicks & Saves!

Consider the following situation…

Your Power BI dashboard is perfect! Thoughtfully structured and beautifully implemented.

But when it comes to monitoring it, you find yourself traversing through the numerous state-filter values, “Alabama, Arizona, Arkansas, California, Colorado…” saving the dashboard state for each value, and then manually arranging them in a PowerPoint Presentation.

This is nothing short of a crime, in today’s fast-paced automated world!

Microsoft’s Power BI is a continuously evolving and immensely powerful data visualization and business intelligence tool, with its simple, streamlined, and powerful structure making it a popular choice for thousands of individuals and organizations worldwide to aggregate, analyze, visualize, and share their data.

However, to tackle situations like the one mentioned above, and to automate the post-dashboard development management system, we created a system that accomplishes the following tasks:

Overview

Dynamically changing the specified filters

In this step, the program generates a list of URLs, each corresponding to a dashboard filtered by a particular filter value. This is accomplished by adding the “query string parameters” in the URL of the published dashboard.

For changing the filters using query string parameters, consider the following syntax,

DASHBOARD_URL?filter= <table >/ <field > <operator > ‘value’

where,
DASHBOARD_URL - The URL of the published dashboard
<table> - The name of the filter with the filtering column
<field> - The name of the column used for filtering ‘Value’ - The filtering value
<operator> - The operator that needs to be used to filter the dashboard

Multiple filters can be added to different fields

Suppose a dashboard is to be filtered on the basis of ‘State’ (equal to Alabama) and ‘Product Category’ ( equal to ‘Decorations’) DASHBOARD_URL?filter= State_Regions/State eq 'Alabama' and Products/Product_x0020_Category eq 'Decoration’

Special Characters in String Queries

Very often, our tables and columns are named such that they contain special characters (spaces, dashes, or other non-ASCII characters). E.g.. ‘Product Category’, ‘State Names’, etc
Such names need to be modified in order to be used in dynamic filtering using query parameters. They must be replaced by their _UNICODE_

Points to Remember

• All special characters must be replaced from the table and field name by the UNICODE (with ‘_’ at the start and end)
• Table and field names must not start with capital letters ‘INF’, eg. ‘INFORMATION’, such names must be saved in lowercase in the dashboard itself to be used in such a system.
• Table and field names are case-sensitive
• The ‘Value’ parameter is NOT case-sensitive

Implementation

Taking User input and Generating the URLs

REQUIRED INPUT:

• The base URL of the dashboard which is published on the workspace
• Table name
• Field name
• Filename and location consisting of the distinct filter elements
• User ID and password of the Microsoft Workspace (It can be hardcoded in the program)

INPUT:

URL GENERATED:

• ‘chromeless=“True”’ is added in order to open the dashboard in full-screen mode
• Each URL opens the dashboard filtered by a specific Campaign value
• The space between the words in the file name “Sheet1 Main” is substituted by its UNICODE

Clicking Screenshots of Filtered Dashboards

For accessing the different dashboard instances with different filters applied, the main automation tool used is ‘Selenium’. Python and Selenium are widely used in automation testing and prove to be highly efficient while interacting with web browsers, they are open source, fast, and support multiple devices and operating systems.
Selenium web driver is a framework used to accept and send various commands to the browser and communicate directly with it. Other than Python, this framework can also be used with the following,

Languages - Java, C#, PHP, Perl, and Ruby
Browsers - Google Chrome 12.0.712.0 and above, Mozilla Firefox, Safari, Opera 11.5 and above, iOS, HtmlUnit 2.9 and above, Android, and Internet Explorer
Operating Systems - Linux, Windows, Mac OS, Solaris

Extra features are added to the web driver's ‘options’ such as the ‘Headless Chrome version’ which allows the program to access the websites without the Graphical User Interface (GUI), which is also referred to as the ‘Head’. The Python package ‘pillow’ or PIL is used to view the screenshots taken by the program. Additional Python packages include ‘time’, and ‘By’, used to instruct the program to halt while the website loads, and to sign in to any accounts (using the find_element and send_keys commands), if required.

Points to Remember

• Ensure that all the dependencies have the correct path set
• The ‘Web driver’ path should be the same as the latest compatible browser’s path
• Ensure that the program handles the website load time delay
• Screenshots can be cropped in order to get the required elements only

Placing the images into different slides of a PPT

For this, the ‘pptx’ library in Python is utilized to create and update PowerPoint files. In this step, a PowerPoint file is created and for every screenshot taken, an image ‘placeholder’ is recursively added to a new slide, and its size and length-width ratio are adjusted according to the image.

Points to Remember

• The images should only be arranged in an image placeholder
• Additional text placeholders and texts can be added along with the images on each slide