Power BI for Business Professionals

Every report requires the same fundamentals. We get data from some external source, we ensure the data is in a useful structure, and we then apply visualizations to study the data.

   •  Learn to read a flat file like an Excel workbook or a text file

   •  Ensure the data is in the right format

   •  Add data visualizations to the report

Effective reports require clear layout and consistent readable formatting.

   •  Arrange visualization for maximum effect

   •  Apply titles and graphic elements to the report

   •  Use a slicer to filter the visible data

Reports should be consistent in layout and formatting for maximum effect.

   •  Use themes to easily manage report appearance

   •  Try built-in themes and custom themes

   •  Create a reusable report template

Reports must draw the readers’ attention to important aspects of data and trends. One way to accomplish this is with conditional formatting.

   •  Use a matrix visualization

   •  Explore conditional formatting using gradients

   •  Explore conditional formatting using rules

Important information must be communicated quickly and effectively. Visual elements like cards and key performance indicators (KPIs) aid this task.

   •  Display key data values using cards

   •  Know the requirements for using KPIs

   •  Implement a basic KPI application

Graphic shapes and colors must be used to enhance communication. This will show how to avoid obscuring meaning with useless graphics.

   •  Avoid the misuse of treemap visualizations

   •  Analyze the problems with pie charts

   •  Understand appropriate use of axis scales

Data from Excel and other sources is often structured in a way unsuitable for a data model in Power BI. We must use Power Query to create a useful data model, which would be suitable for analysis in Power BI.

   •  Read the Excel workbook data into Power BI

   •  Use Power Query to adjust the imported dataset

   •  Apply the “unpivot” operation, to turn an Excel worksheet into a good data model

Only the simplest data can be represented in a one-table data model. An effective data model requires specifying precisely the relationship between data in different tables.

   •  Use the model view in Power BI Desktop

   •  Edit the names and the table structure

   •  Create the relationships

Virtually all analysis include time dimension data. Time dimension data, however, often requires special handling.

   •  Explore the hidden time dimension tables

   •  Learn about the explicit time dimension tables

   •  Explore the active and inactive relationships

Professionals using Power BI must always be aware of datatypes, but this is especially import with datetime. Many DAX functions will not work correctly if errors are made in datatype management.

   •  Convert data into the Power BI datetime datatype

   •  Enhance the performance by removing unnecessary time of day information

   •  Understand how Power BI improves performance by compressing data

Data received from the “real world” is often filled with erroneous data, missing data, or both. These problems must be addressed before reports can be created.

   •  Deal with missing data

   •  Know how to handle data outside of normal ranges

   •  Explore how to handle inappropriate datatypes in a data model column

When we have put a great deal of work into the correct structuring and error handling of imported data, we would like to apply this effort to future reports as well. Parameterized templates provide a means of doing this.

   •  Define parameters to customize the data load process

   •  Use defined parameters to filter an imported data table

   •  Assign values to parameters when creating a new report with a template

Power BI provides a rich set of built-in visualizations, but Microsoft and other vendors have created a host of custom visualizations, that can be downloaded and used in Power BI reports.

   •  Access Office Store to obtain certified custom visualizations

   •  Download custom visualizations and examples of Power BI workbooks

   •  Use custom visualizations in your own reports

Key performance indicators (KPIs) are popular among analysts and executives alike. Several custom KPIs are available that expand the KPI functionality.

   •  Select a custom KPI visualization from the Office Store

   •  Apply the approach of defining KPI measures in the data source

   •  Assign measures to the custom visualization

In most cases, measure values for a useful KPI will not be present in the imported data. We need to use the DAX language (Data Analysis Expressions) to create custom measure values.

   •  Get a first look at creating measures using DAX

   •  Know the details of a specific measure for use in a KPI

   •  Assign the new measure to the custom visualization

Time series analysis is a deep topic in statistics, but a Power BI custom visualization lets us get involved with minimum effort.

   •  Get an overview of visualizations based on the R Statistics language

   •  Install the open-source statistical language R

   •  Use the R-based time series analysis custom visualization

Decision trees figure prominently in the fields of Data mining and Machine learning. Becoming familiar with the basics can be accomplished with a custom visualization.

   •  Get an overview of Decision Trees

   •  Set up the data model for the prediction of consumer behavior

   •  Learn about the Decision Tree Data mining using the custom visualization

Inevitably, we will need to calculate new columns in our data model, based on the existing values in other tables and columns. Similarly, we will want to investigate more sophisticated measures than simple sums and averages.

   •  Get introduced to DAX

   •  Use DAX to create a calculated column

   •  Use DAX to create a new measure

Some calculations are suitably implemented as columns, others require new measures. We shall see as to what determines the difference.

   •  Try the first attempt so as to calculate how profit margin yields an incorrect result

   •  Identify the cause of the problem

   •  Create a measure to calculate the profit margin directly

In contrast with Microsoft Excel, where the focus is on individual cells in a worksheet, in Power BI our calculations are based on sets of values in rows.

   •  Understand the calculation context

   •  Use the CALCULATE function to control context

   •  Use FILTER and CALCULATE together for a powerful calculation pair

As time goes on, many new measures may be added to a data model. How best to organize them is a personal choice, but there is a technique that some find particularly useful.

   •  Know that measures can be located in any table

   •  Create a special table in the data model just for measures

   •  Create a measure in the new measures table

Sometimes a new small table is required to define things like department names or sales regions.

   •  Get to know the scenarios for DAX table creation

   •  Create a DAX table with literal column values

   •  Create a DAX table based on a summary of other data

Monte Carlo analysis plays an important role in many financial modeling techniques. Languages like R and Python provide excellent tools with which one can integrate Monte Carlo analysis into Power BI.

   •  Use R scripts as a data source

   •  Write R scripts directly in Power BI

   •  Use Power BI to invoke R scripts written in an external tool like RStudio

R includes excellent tools for processing data in preparation for analysis, and often provides a superior alternative to Power Query.

   •  Use an R script to read Excel worksheets

   •  Process the source data using R functions

   •  Ensure that the processed data is in a form suitable for analysis in Power BI

Sometimes you need a custom visualization, but nothing suitable is available at the Office Store. Programming your own is a daunting task, but R and Python provide a much easier path to custom visualizations.

   •  Get a general overview of R visualizations

   •  Perform a k-means clustering algorithm

   •  Examine the results using a built-in visualization

The great advantage to custom R visualizations is the availability of many specialized open-source software packages for excellent graphics.

   •  Use the Scatterplot3D software library

   •  Prepare the data so the Power BI environment can interact with the R script

   •  Produce a 3D plot to examine the results of the k-means clustering algorithm

Microsoft’s Power BI Service provides an excellent means for sharing reports and dashboards with team members. In contrast with Power BI Desktop, the service has different tiers available at different costs.

   •  Know the features of the free version versus Power BI Pro

   •  Discover the differences between Power BI Pro and Power BI Premium

   •  Learn how to sign up

Moving data from the workstation to the Power BI cloud is not difficult, but it differs from the techniques used for Power BI Desktop.

   •  Import data into the Power BI service from local files on the workstation

   •  Export data models from Microsoft Excel or power on the workstation to Power BI service

   •  Understand the challenges for importing data from relational databases into Power BI Service

Reports can be imported from Power BI Desktop or created within the Power BI Service. However, there are important limitations on how data in a Power BI service report can be refreshed.

   •  Create a Power BI service report

   •  Prepare report visualizations for use on the dashboard

   •  Explore the options for refreshing data in a Power BI service report

Dashboards provide executives and analysts with an overview of many different aspects of the enterprise.

   •  Create a Power BI dashboard

   •  Add “tiles” and visualizations to a Power BI dashboard

   •  View and interact with a Power BI dashboard