This refers to the process of customising the appearance of the visuals within a report to make them more appealing, insightful, and easier to interpret.

Some of the reasons why we format visuals include;

Enhanced readability: Makes it easier for users to interpret and draw meaningful insights.
Improved appearance: Enhance general visual appearance of the report/dashboard.
Consistency: Give visuals a consistent look and feel and follow branding guidelines.
Data storytelling: Guide audience’s attention to the narrative we want to tell with the data.
Accessibility: Enhances accessibility for users with disabilities.
User preferences: Adapt the report/dashboard to the audience type.

We can apply formatting in Power BI by:

Modifying various aspects of a visual using the formatting pane.
Communicating additional information.
Themes
Conditional formatting
Customised visuals

Formatting pane in Power BI

This pane allows us modify our visuals in various ways. It offers a wide range of options and settings that can be adjusted for each visual element.

To navigate to the “Format your visual“ pane:

Select the visual. Go to the visualization pane and click on the paintbrush icon.
A formatting pane will be displayed, divided into Visual and General. Each section has a list of formatting options related to the various elements found in a visual.
To modify a specific element, go to the appropriate option on the list, click on the dropdown next to it to expand its available settings.
The formatting settings displayed depend on the type of visual selected.

Communicating additional information

This helps us add more clarity and insights to a visual. In Power BI, we can use the tooltip and reference line features to provide supplementary information.

A Tooltip is an interactive feature where a small pop-up window appears when we hover over data point in a visual. By default, the tooltip will display the data point’s category and value but we can customise it in various ways.

Go to Visualisations, then the Tooltips well.
Drag and drop the desired field to the Tooltips well.
Rename and choose aggregation.
The new field is now displayed in the tooltip.

Reference lines are horizontal or vertical lines added to a visual to mark and communicate certain insights about the data being represented, such as the average, maximum, minimum, and median.

To add a reference line;

Go to the Analytical icon under the Visualizations pane. Note that the list of analytical options shown is dependent on the type of visual selected.
Click and expand the type of line we wish to create.
Click Add to create a new line, name it, and choose the field it should be based upon.

Themes in Power BI

If we have multiple visuals in a report page, we can use Power BI themes to format all at once such that they share the same colors, fonts, and other design elements.

To apply a different theme,

Select the View ribbon at the top. Go to themes and click on the dropdown.
Select a theme from the options provided. The theme is applied and the colors, fonts, and general appearance of our visuals change accordingly.

We can also customize a given theme by changing to our preferred formatting settings.

Select View at the top, and go to Themes.
Select Customise current theme. A dialogue box with customisable theme settings appear, and they’re grouped into categories like Name and color, Text, Visuals, Page and Filter pane.
Click on Apply when we have modified all the desired formatting settings.
The changes are saved and applied to our report.

To be able to use the theme in another file, we need to save it as a JSON file on our local machine.

Go to the themes dropdown and select Save current theme
Pick a name and storage location for the file, then click Save

If we have a saved custom theme file in our local machine, we can import it to current Power BI file as follows;

Go to themes, then select Browse for theme.
Navigate to the saved theme file and upload it. The theme will then be applied to the current file.

Conditional formatting in Power BI

This is a feature that allows us change the formatting of our visuals based on the specific rules or conditions. This makes it easier to interpret and identify patterns and outliers within our data.

Some common Power BI conditional formatting options include:

Background color: Change background color of the data points based on specified conditions.
Font color: Modify the color of text within your visual based on specific rules or conditions.
Data bars: Provide visual representation of data values by adding additional horizontal bars whose lengths correspond to the data values’ magnitude.
Icons: Add built-in or custom icons next to data values, helping to convey a quick visual message about the data’s status.
Web URL: Create hyperlinks within your visual that direct users to external web content.

Custom visuals in Power BI

These are custom-built or third-party data visualizations that can be imported and used within Power BI reports and dashboards. They allow us extend the built-in visualization options with unique charts, graphs, and visuals.

Data models in Power BI

A data model is a conceptual representation of data and its structure within a database. Data models in SQL and Power BI serve different purposes but are closely related when it comes to business intelligence and data analysis.

In SQL, data models such as entity-relationship data models are used for database management and data storage as well as defining how data are structured, stored, and organised within a relational database system.

However, in Power BI, data models help us build analytical models that enhance our analytical capabilities by providing relationships between data tables. Power BI also enables us aggregate and filter across and analyse from multiple sources.

Characteristics of a good data model

A good data model in Power BI is essential for ensuring our reports and dashboards provide valuable insights and are efficient to work with. Some key characteristics include;

Efficient data exploration: Users should be able to quickly navigate through data, filter, and drill down to gain insights without experiencing delays or performance issues.
Simplified aggregation: Aggregations are used to summarise and pre-calculate data for improved performance. A good model should make it simple to create and manage these aggragations.
Data accuracy: A good model should reflect the true state of the underlying data, and transformations and calculations should be carried out accurately. Inaccuracies can lead to incorrect insights and decisions.
Maintainability, reusability: A good data model should be modular and designed for reuse. well-named tables, columns, and measures, along with clear relationships and calculations contribute to maintainability and reusability.
Performance optimization: A well designed data model should optimise queries and calculations to minimize load times and maximize interactivity.
Flexibility: A good model should be flexible enough to adapt to evolving needs.

The star schema in Power BI

A variety of data models can be implemented in Power BI. However, the most commonly used is star schema.

Star schema is a modelling approach used in Power BI and other data warehouse and business intelligence systems. It’s characterized by a central fact table connected to multiple dimension tables, forming a star-like structure when visualized graphically.

Relationships are established between the fact table and dimension tables using primary and foreign keys, enabling us to slice, filter, and drill down into the data effectively.

How to structure tables

A good and simple table structure allows us organize tables in the data model in a way that makes it easy to navigate, work with, and create meaningful reports and visualizations.

Specific and accessible column and table properties. Each table should have clear and specific purpose, representing a distinct entity or dimensions. Meaningful descriptions and annotations on tables and columns help with accessibility.
Merge and/or append tables to simplify. Data from multiple tables will have to be merged to tables in the data model in order to reduce complexity and redundancy. We can merge tables when we want to combine related data from different tables into a single table.
Good quality relationships between tables. Establishing appropriate relationships between tables is crucial for data analysis. They define how tables are connected and how data can be combined and analysed across different dimensions.

Data granularity

This refers to the level of detail or specificity at which data are recorded and stored in a dataset or database. It defines how fine-grained or course-grained data observations are.

Fine-grained data (high granularity)	Course-grained data (low granularity)
Allow for more precise and detailed analysis.	Provides a more summarised view of the data.
The data is more complex which may require more effort to design and maintain the data model.	A more user-friendly data model as the number of tables and relationships to manage is reduced.
More resource intensive to store and process the data which may result in slower query performance.	Requires less storage and may result in faster query performance.

The choice of data granularity depends on the problem we need to solve and the data we have available. There is always a trade-off between the complexity of analysis and performance. Some analyses require highly granular data to detect subtle trends and patterns, while others can be adequately served with summarisation.

Fine-grained data can be summarized where necessary before being used in dashboarding or reporting to optimise performance, while coarse-grained data cannot be reverted into data that can provide the same level of analysis.

Data cleaning and formatting

Clean data ensures the accuracy and reliability of insights derived from visualizations, preventing misinformed conclusions due to errors or inconsistencies.

Always endeavor to use user-friendly naming conventions to enhance the readability of a data model. We can use Transform tab to change column names in the Power query editor as shown below.

Power Query Editor enables us to address various data quality issues such as inconsistencies, unexpected values, and null values by replacing or deleting them.

We replace values by;

Right-click on the column name.
Select Replace Values
Enter the Value To Find and the value to Replace with.

We reduce rows by;

Select column name.
Navigate to the Home tab.
Select Remove Rows under the Reduce Rows section.
Select Remove Errors in the dropdown menu.

The view tab in Power Query Editor also allows us to see the column quality of each column in the dataset and resolve any data quality issues.

To view column quality

Right-click on the column name.
Navigate to the View tab.
Select the Column quality check box in the Data Preview section.
Select the appropriate data quality solution.

We can evaluate and change data types to ensure data consistency. In the Power Query Editor, there are multiple ways to transform data types.

Right-click method:

Right-click on the column name.
Select Change Type
Select the appropriate data type from the dropdown menu.

Transform section method:

Click on the Home tab.
Navigate to the Transform section, which contains general transformations at the column level.
Select the appropriate data type from the dropdown menu.

We can also use the Power Query Editor to identify data types automatically based on the values in the column.

Select the relevant column
Navigate to the Transform tab.
Select the Detect Data Type option in the Any Column section.

Data Profiling

This helps us understand the data characteristics. We can use profiling to identify outliers, missing values, and data distributions.

Power Query Editor provides summary statistics and histograms to help us achieve this.

Data shape transformation

Transforming data shapes involves grouping, pivoting, unpivoting, or transposing data. The Power Query Editor allows us achieve this under the Transform tab.

Group by

To group data by a specific column, we use the group by option which is either available in the Table section under the Transform tab or in the Home tab under the Transform section.

For Basic, under the basic settings, we can select the column to group by as well as the operation to use to group the column and the name of the grouped column.

For advanced, under the advanced settings, we can select multiple columns to group by. Selecting the Add grouping button will allow us to choose a new grouping column. We can also use multiple aggregations for our groupings by selecting the Add aggregations button.

Transposing, pivoting, and unpivoting data

Transpose converts columns to rows and rows to columns for the entire table. Allows us switch from a wide format(many columns, few rows) to a tall format (few columns, many rows) or vice verse.

Select the table to transpose and click on Transpose under the Table section.

Pivot converts rows into columns for a specific column. Used to aggregate data and create summary tables. It converts unique values in a column into new columns, allowing us to summarise data by specific attributes.

Navigate to the Transform tab and select Pivot Column under the Any Column section

Select where the cell values for the new columns will come from.
We can also specify an aggregation function, like sum or average, for the pivoted values.

Formatting Visuals in Power BI

Table of contents