Power BI: From Data to Decision-Making

This document serves as a transcript for a video tutorial focused on Microsoft Power BI, a business intelligence tool. The tutorial, led by Kevin, explains how to download and install Power BI, import data from various sources like Excel spreadsheets and the web, and transform that data for analysis. It then guides users through creating various visualizations such as bar charts, line charts, and maps, and demonstrates how to interact with and slice the data within the reports. Finally, the document covers customizing the report’s appearance and the process of saving and publishing the report for sharing and collaboration within the Power BI service.

Power BI: From Data to Insightful Reports

Microsoft Power BI is a tool used to gain insights from data. It was utilized at Microsoft to analyze business performance and make decisions based on that performance. Power BI Desktop is entirely free to download and install, regardless of whether you have an enterprise or commercial account.

The general workflow for using Power BI, as introduced in a tutorial, involves:

• Downloading and installing Power BI.
• Importing sample data.
• Creating visualizations and reports.
• Saving, publishing, and sharing these reports with others.

This overview serves as a “101” or introduction to Power BI.

Installation Methods The easiest and recommended way to install Power BI is by clicking the “download free” button, which opens the Microsoft Store to the Power BI download page. Benefits of installing via the Microsoft Store include automatic updates, quicker downloads of only changed components, and the ability for any user (not just an admin) to install it. Alternatively, you can click “see download or language options” to download an executable (.EXE) file and install it manually, though this method does not use the Microsoft Store.

Getting Started and Interface After installation, you can launch Power BI, which first displays a welcome screen. The most crucial initial step is to “get data,” as visualizations cannot be created without it. The welcome screen also shows recent data sources and previously created reports for quick access. Power BI offers training content, including videos and tutorials, to help users get up to speed.

The main interface of Power BI Desktop includes several views:

• Report View: This is the default view, a blank canvas where visuals like charts, tables, or maps are created. On the right side, there are “fields” (all available data columns) and “visuals” (different types of visuals that can be built) panes.
• Data View: Clicking this option displays a spreadsheet-like view of all imported and transformed data.
• Model View: This view shows the relationships between different data tables. For example, if two tables are joined based on a common field like “country name,” a line will connect them, highlighting the relationship when hovered over.

Data Import and Transformation Power BI can pull data from an extensive list of sources, including Excel spreadsheets, SQL databases, web sources (like Wikipedia articles), and Kusto queries. For example, data can be imported from an Excel spreadsheet containing revenue, cost, and profit data, along with details like country, product, sales, and dates. Additionally, data from the web, such as a Wikipedia article listing countries and their populations, can be pulled in.

Data transformation is a key step, allowing users to modify and select data before it’s brought into Power BI. This process opens the Power Query editor, where data is “shaped” and a data model is built. Examples of transformations include:

• Filtering out specific data, such as removing “Fortune cookies” from product analysis. These filtered steps can also be undone.
• Changing data types, like converting “units sold” from decimals to whole numbers.
• Renaming columns for conciseness, such as changing “month name” to “month”.
• Removing unnecessary columns, like “percent of world population,” “date,” “source,” or “rank” from imported web data.
• Filtering rows to include only relevant data, such as specific countries where a company has locations (e.g., Canada, France, Germany, Mexico, United States).
• Replacing values within columns, like removing an extra “D” from “United StatesD”.

Connecting Data Sources Independent data tables can be connected or joined. This is done using the “merge queries” function, allowing tables to be linked based on common fields, such as “country name” between cookie sales data and country populations data. This enables the association of data from one source (e.g., population) with another (e.g., cookie sales).

Creating and Formatting Visualizations After data is loaded and modeled, visualizations can be created on the report canvas. Users can insert a text box to add a title to the report. To create a visual, users can simply click on a data field (e.g., “profit” and “date”) and Power BI will suggest a default chart type (e.g., a bar chart). This can then be changed to another type, such as a line chart for profit by date. Other common visualizations include:

• Map visualization: Automatically inserted when country data is selected, showing locations and allowing profit data to be displayed on the map, with dot sizes indicating profit levels. Can be switched to a treemap to show profit by country hierarchy.
• Table: Allows presentation of data like country, population, and units sold in a structured format.
• Bar chart: Used to show sales or profit by product, easily illustrating which products generate the most profit.

Visualizations can be formatted by clicking on the “format” option (paint roller icon) in the visualization pane. This allows adjustment of various elements, such as increasing title text size, to match company branding or preference. Reports can also have multiple pages.

Slicing and Sharing Data Power BI reports allow for easy data slicing (filtering). A “slicer” visual can be added to a report, where users can select specific categories (e.g., country name) to filter all other visuals on the page. Clicking directly on elements within other visuals, such as a country on a map or in a table, can also serve as a quick way to slice the data.

Once a report is complete, it can be saved. The “power” of Power BI comes from its ability to share reports with others. Reports are published to the Power BI service (powerbi.com). From there, the report can be opened in the Power BI service, where it can still be filtered. The share dialog allows granting access to specific individuals via email, setting permissions (like allowing sharing or creating new content based on datasets), and sending email notifications.

Power BI: Data Transformation and Modeling with Power Query

Data transformation in Power BI is a crucial step that allows users to modify and select data before it is loaded into the Power BI environment. This process is carried out in the Power Query editor, where data is “shaped” and a data model is built.

Here are the key aspects and examples of data transformation discussed:

• Purpose of Transformation
• It enables users to modify their data and choose exactly what data they want to bring into Power BI.
• It helps in building a structured data model suitable for analysis and visualization.
• Accessing the Power Query Editor
• After selecting data from a source (e.g., an Excel spreadsheet), users can choose “Transform data” instead of “Load” to open the Power Query editor.
• Common Transformation Actions
• Filtering Data: Users can filter out specific rows or values that are not relevant to the analysis. For example, a product line like “Fortune cookies” might be removed from the analysis if it’s not profitable or is distracting from other products. These filtered steps can also be undone later if needed.
• Changing Data Types: Data types can be adjusted to ensure accuracy and usability. For instance, “units sold” might be changed from decimal numbers to whole numbers if fractional sales don’t make sense.
• Renaming Columns: Columns can be renamed for conciseness or clarity, such as changing “month name” to simply “month”.
• Removing Unnecessary Columns: Columns that are not needed for the analysis can be removed, such as “percent of world population,” “date,” “source,” or “rank” from a web-imported dataset.
• Filtering Rows to Specific Subsets: Users can filter down rows to include only relevant data, such as selecting only countries where a company has locations (e.g., Canada, France, Germany, Mexico, United States).
• Replacing Values: Specific values within columns can be replaced to correct inconsistencies, like removing an extra “D” from “United StatesD”.
• Tracking Transformations (Applied Steps)
• As changes are made in the Power Query editor, each transformation is recorded in a section called “applied steps” on the right-hand side of the interface. This allows users to see all the modifications made to the data and also provides the option to remove a step if it was made unintentionally.
• Connecting Independent Data Sources (Merging Queries)
• Power BI allows users to connect or join independent data tables, such as linking cookie sales data with country population data from a Wikipedia article.
• This is done using the “merge queries” function, where tables are joined based on a common field (e.g., “country name”).
• The “Model View” in Power BI Desktop visually represents these relationships between data tables, showing lines connecting tables that are joined.

Once all transformations are complete and the data model is built, users click “close and apply” to load the refined data into Power BI, ready for report creation.

Power BI: Crafting Interactive Reports and Visualizations

After data transformation and modeling, Power BI Desktop provides a Report View, which serves as a blank canvas where users create and arrange various visuals such as charts, tables, or maps. This blank area is referred to as the report editor.

On the right side of the Power BI Desktop interface, there are two key panes that facilitate report visualization:

• Fields Pane: This pane displays all available data columns (called fields) from the imported and transformed data. Users can drag and drop these fields onto the canvas or select them to build visuals.
• Visuals Pane: Located to the left of the fields pane, this section offers various types of visuals that can be built using the data.

Here’s a breakdown of how report visualization works:

Creating Visualizations

• Starting a Visual: To create a visual, users can simply click on relevant data fields in the “fields” pane, such as “profit” and “date”.
• Default Suggestions: Power BI often predicts and inserts a default chart type that it deems most likely suitable for the selected data, like a bar chart for profit by date.
• Changing Visual Types: Users can easily change the chart type from the “visualizations” pane if the default doesn’t align with their needs (e.g., switching a bar chart to a line chart for profit by date).
• Defining Visual Elements: The visualizations pane also allows users to define different elements of the chart, such as what fields serve as the axis, values, or legend.

Examples of Visualizations:

• Text Box: Can be inserted to add a title to the report, providing context (e.g., “Kevin Cookie Company performance report”).
• Line Chart: Useful for showing trends over time, such as profit by date.
• Map Visualization: Automatically inserted when geographical data like “country” is selected. It shows locations with dots, and profit data can be dragged onto the map to represent profit levels by dot size.
• Treemap: An alternative to the map view, it can display hierarchical data like profit by country, illustrating which country had the most or least profit.
• Table: Allows presentation of data in a structured, spreadsheet-like format, such as country, population, and units sold. Users can drag and drop fields into the table.
• Bar Chart: Used to show comparisons, such as sales or profit by product, clearly indicating top-performing products.

Formatting and Appearance

• Themes: The “View” tab in the ribbon provides different themes (e.g., “executive” theme) that can be applied to change the overall look and feel of the report, including color schemes, to make it appear more professional.
• Individual Visual Formatting: Each visual can be formatted individually by clicking on the “format” option (represented by a paint roller icon) within the visualization pane. This allows users to adjust elements like title text size or other visual properties to match company branding or preference.
• Multiple Pages: Reports can span multiple pages, allowing for comprehensive data presentation.

Slicing and Interacting with Data

• Slicer Visual: A “slicer” visual can be added to the report, typically based on a categorical field like “country name”. Selecting a specific category in the slicer will filter all other visuals on the page to reflect only that selection.
• Direct Interaction with Visuals: Users can also slice data by directly clicking on elements within other visuals, such as clicking on a country on a map or in a table. This provides a quick way to filter the entire report based on that selection. Clicking a blank area or re-clicking a selection can undo the filter.

Saving and Sharing Reports Once a report with visualizations is complete, it can be saved locally. The “power” of Power BI is realized when reports are published to the Power BI service (powerbi.com), enabling sharing and collaboration. In the Power BI service, reports remain interactive and can still be filtered. The share dialog allows users to grant access to specific individuals via email, set permissions (e.g., allowing sharing or creating new content based on datasets), and send email notifications.

Power BI: Collaborative Data Sharing Essentials

Data sharing in Power BI is a fundamental aspect that unlocks the full potential of the platform, moving beyond individual analysis to collaborative insights. While reports can be created and saved locally for personal use, the true “power” of Power BI lies in its ability to enable collaboration and allow others to interact with the created visualizations.

Here’s a discussion on data sharing:

• Purpose of Sharing: The primary goal of sharing is to allow other individuals to view and interact with the visualizations and reports you’ve created. This facilitates collective analysis and decision-making based on the data.
• The Sharing Process:

1. Local Saving: After creating a report and its visualizations, it is initially saved locally on your desktop as a .pbix file. At this stage, it can be used for individual analysis.
2. Publishing to Power BI Service: To share the report, it must first be “published”. This is done by navigating to the “file” menu and selecting the “publish” option, then choosing “publish to Power BI”.
3. Power BI Service (powerbi.com): The Power BI service is the online platform where all published reports are housed. Once published successfully, the report becomes accessible on powerbi.com. Reports opened in the Power BI service remain interactive, allowing users to filter data just as they would in the Power BI desktop application.

• Sharing Options and Permissions:
• From the Power BI service, you can click on the “share” button, typically found in the top right-hand corner.
• This opens a “share dialog” that provides various options for granting access.
• You can grant access to specific individuals by entering their email addresses.
• Crucially, you can define permissions for those you share with:
• You can allow recipients to share the report with others.
• You can enable them to create new content based on the underlying datasets.
• An option to send an email notification to the recipients is also available, which can include any changes made to the report.

Power BI Report Customization Guide

Report customization in Power BI allows users to refine the appearance and layout of their reports to enhance clarity, professionalism, and alignment with specific branding or preferences. This process goes beyond merely creating visualizations and focuses on making the report aesthetically pleasing and user-friendly.

Key aspects of report customization include:

• Adding Contextual Elements:
• Titles: Users can insert text boxes to add a main title to the report, providing immediate context (e.g., “Kevin Cookie Company performance report”). These titles can be resized and positioned to span the entire report.
• Formatting Visuals:
• Changing Chart Types: While Power BI often suggests a default chart type (e.g., bar chart) for selected data, users can easily switch to other visual types (e.g., line chart, treemap, map, table, bar chart) from the “visualizations” pane to better represent their data.
• Defining Visual Elements: Within the visualization pane, users can explicitly define what fields should serve as the axis, values, or legend for a chart. They can also add secondary values.
• Individual Visual Formatting: Each visual can be formatted independently. By selecting a visual and clicking on the “format” option (represented by a paint roller icon) in the visualizations pane, users can adjust various elements. For instance, the title text size of a visual can be increased to make it stand out. This allows users to match the visuals to their company’s brand, look, and feel.
• Applying Themes:
• Power BI provides different themes (e.g., “executive” theme) under the “View” tab on the ribbon. Applying a theme changes the overall color scheme and appearance of the report, contributing to a more professional look.
• Organizing Layout:
• Users can drag and drop visuals around the report editor (the blank canvas) to organize them as desired.
• Reports are not limited to a single page; users can add multiple pages to their report to accommodate extensive data and different views. Pages can also be renamed.

By leveraging these customization features, users can transform raw data visualizations into polished, insightful reports that effectively communicate their findings. Once satisfied with the customization, the report can be saved locally and then published to the Power BI service for sharing.

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!