Learn Power BI: A beginner's guide to developing interactive business intelligence solutions using Microsoft Power BI

By Greg Deckler

Solve business challenges with Microsoft Power BI's advanced visualization and data analysis techniques

Key Features

• Create effective storytelling reports by implementing simple-to-intermediate Power BI features
• Develop powerful analytical models to extract key insights for changing business needs
• Build, publish, and share impressive dashboards for your organization

Book Description

To succeed in today's transforming business world, organizations need business intelligence capabilities to make smarter decisions faster than ever before. This Power BI book is an entry-level guide that will get you up and running with data modeling, visualization, and analytical techniques from scratch.

You'll find this book handy if you want to get well-versed with the extensive Power BI ecosystem. You'll start by covering the basics of business intelligence and installing Power BI. You'll then learn the wide range of Power BI features to unlock business insights. As you progress, the book will take you through how to use Power Query to ingest, cleanse, and shape your data, and use Power BI DAX to create simple to complex calculations. You'll also be able to add a variety of interactive visualizations to your reports to bring your data to life. Finally, you'll gain hands-on experience in creating visually stunning reports that speak to business decision makers, and see how you can securely share these reports and collaborate with others.

By the end of this book, you'll be ready to create simple, yet effective, BI reports and dashboards using the latest features of Power BI.

What you will learn

• Explore the different features of Power BI to create interactive dashboards
• Use the Query Editor to import and transform data
• Perform simple and complex DAX calculations to enhance analysis
• Discover business insights and tell a story with your data using Power BI
• Explore data and learn to manage datasets, dataflows, and data gateways
• Use workspaces to collaborate with others and publish your reports

Who this book is for

If you’re an IT manager, data analyst, or BI user new to using Power BI for solving business intelligence problems, this book is for you. You’ll also find this book useful if you want to migrate from other BI tools to create powerful and interactive dashboards. No experience of working with Power BI is expected.

• Language: English
• Publisher: Packt Publishing
• Release date: Sep 13, 2019
• ISBN: 9781838646653

Book preview

Learn Power BI

Learn Power BI

A beginner's guide to developing interactive business intelligence solutions using Microsoft Power BI

Greg Deckler

BIRMINGHAM - MUMBAI

Learn Power BI

Copyright © 2019 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Commissioning Editor: Sunith Shetty

Acquisition Editor: Reshma Raman

Content Development Editor: Nathanya Dias

Senior Editor: Ayaan Hoda

Technical Editor: Manikandan Kurup

Copy Editor: Safis Editing

Project Coordinator: Kirti Pisat

Proofreader: Safis Editing

Indexer: Manju Arasan

Production Designer: Nilesh Mohite

First published: September 2019

Production reference: 1130919

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-83864-448-2

www.packt.com

Packt.com

Subscribe to our online digital library for full access to over 7,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.

Why subscribe?

Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals

Improve your learning with Skill Plans built especially for you

Get a free eBook or video every month

Fully searchable for easy access to vital information

Copy and paste, print, and bookmark content

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.packt.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [e-mail] for more details.

At www.packt.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks. 

Contributors

About the author

Greg Deckler is Vice President of Cloud Services at Fusion Alliance and has been a technology systems consultant for over 25 years. Internationally recognized as an expert in Power BI, Greg Deckler is a Microsoft MVP for Data Platform and an active member of the Power BI community, with over 100,000 messages read, more than 11,000 replies, over 2,200 answers, and more than 75 entries in the Quick Measures Gallery. Greg founded the Columbus Azure ML and Power BI User Group (CAMLPUG) and presents at numerous conferences and events, including SQL Saturday, DogFood, and Dynamic Communities' User Group/Power Platform Summit.

I would like to thank the dynamic and vibrant Power BI community as a whole, and especially Charles Sterling, for their dedication and support. Also, shout-outs to the following Power BI Community members: @ImkeF, @konstantinos, @parry2k, @Seth_C_Bauer, @Phil_Seamark, @GilbertQ, @Vvelarde, @MattAllington @marcorusso, and @Mike_Carlo.

About the reviewers

Peter Ter Braake started working as a developer in 1996 after studying physics in Utrecht, the Netherlands. Databases and business intelligence piqued his interest the most, leading to him specializing in SQL Server and its business intelligence components. He worked with Power BI from the tool's very beginnings.

Peter started working as an independent contractor in 2008. This enables him to divide his time between teaching data-related classes, consulting with customers, and writing articles and books.

Vishwanath Muzumdar has 6 years' experience in information technology consulting, business analysis, business development, and business process management in the business intelligence space. He is a Microsoft Power BI developer and creates powerful visual reports for his clients while implementing corporate Power BI solutions and user training. He also has expertise in realizing client requirements across multiple domains, and proficiently planning and executing strategies for the same at both an individual and team level. He aims to utilize his strong prioritization skills, analytical ability, and team management skills, coupled with his expertise in relation to Microsoft Power BI reporting tool, to enable a company to achieve its goals.

Packt is searching for authors like you

If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.

Table of Contents

Title Page

Copyright and Credits

Learn Power BI

About Packt

Why subscribe?

Contributors

About the author

About the reviewers

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Download the color images

Conventions used

Get in touch

Reviews

Section 1: The Basics

Introduction to Business Intelligence and Power BI

Key concepts of business intelligence

Domain

Data

Internal and external data

Structured, unstructured, and semi-structured data

Model

Organizing

Transforming and cleansing

Defining and categorizing

Analysis

Visualization

The Power BI ecosystem

Core, Power BI-specific

Core, non-Power BI-specific

Non-core, Power BI-specific

Natively integrated Microsoft technologies

The extended Power BI ecosystem

Power BI licensing

Power BI Free

Power BI Pro

Power BI Premium

Power BI Embedded

Power BI Report Server

Power BI Desktop and Service

Power BI Desktop

Getting data

Creating a data model

Analyzing data

Creating and publishing reports

Power BI Service

Viewing and editing reports

Creating dashboards

Sharing and collaborating with others

Accessing and creating apps

Refreshing data

Summary

Questions

Further reading

Section 2: The Desktop

Up and Running with Power BI Desktop

Technical requirements

Downloading and installing Power BI Desktop

Power BI Desktop (the trusted Microsoft Store app)

Power BI Desktop (MSI)

Power BI Desktop (Report Server edition)

Running Power BI Desktop

Touring the Desktop

Title Bar and Quick Access Toolbar

The Ribbon

The Formula Bar

Understanding DAX

Views Bar

Panes

Canvas

Page tabs

Footer

Generating data

Creating a calculated table

Creating calculated columns

Formatting columns

Creating visualizations

Creating your first visualization

Formatting your visualization

Adding analytics to your visualization

Creating and using a slicer

Creating more visualizations

Editing visual interactions

Summary

Questions

Further reading

Connecting and Shaping Data

Technical requirements

Getting data

Creating your first query

Getting additional data

Transforming data

Touring the Power Query Editor

The Title Bar and Quick Access Toolbar

The Ribbon

Formula Bar

The Queries Pane

The Query Settings Pane

Data Canvas

Footer

Transforming budget and forecast data

Cleaning up extraneous bottom rows

Filtering rows

Unpivoting data

Using Fill

Changing data types

Transforming people, tasks, and January data

Transforming the People query

Transforming the Tasks query

Transforming the January query

Merging, copying, and appending queries

Merging queries

Expanding tables

Disabling the loading of queries

Copying queries

Changing sources

Appending queries

Verifying and loading data

Organizing queries

Checking column quality, distribution, and profiles

Loading the data

Summary

Questions

Further reading

Creating Data Models and Calculations

Technical requirements

Creating a data model

Touring the Model view

Title Bar and Quick Access Toolbar

Ribbon

Views Bar

Panes

Canvas

Layout Tabs

Footer

Modifying the layout

Creating and understanding relationships

Exploring the data model

Creating calculations

Calculated columns

Understanding context for calculated columns

Creating calculated columns for utilization

Measures

Understanding context for measures

Creating measures for utilization

Checking and troubleshooting calculations

Boundary cases

Slicing

Grouping

Summary

Questions

Further reading

Unlocking Insights

Technical requirements

Segmenting data

Creating groups

Creating hierarchies

Exploring hierarchies

Understanding row-level security

Creating roles

Testing roles

Using report navigation features

Drillthrough

Using drillthrough

Buttons

Types of buttons

Button states

Button actions

Question and answer (Q&A)

Best practices for Q&A

Using a Q&A button

Using Q&A in report authoring

Synonyms

Bookmarks

Creating and using bookmarks

Advanced bookmarks

Advanced visualization techniques

Top N filtering

Gauges and KPIs

What-if parameters

Conditional formatting

Quick Measures

Report page Tooltips

Creating Report page Tooltips

Using Report page Tooltips

Key influencers

Summary

Questions

Further reading

Creating the Final Report

Technical requirements

Preparing the final report

Planning the final report

Cleaning up

Using a theme

Creating a page template

Syncing the slicers

Adjusting the calendar

Adding report filters

Creating the final report pages

Creating the Executive Summary page

Creating the Division Management page

Creating the Branch Management page

Creating the Hours Detail page

Creating the Employee Details page

Creating the Introduction page

Finishing up

Testing

Cleaning up

Summary

Questions

Further reading

Section 3: The Service

Publishing and Sharing

Technical requirements

Getting an account

Office 365

Power BI trial

Introducing the Service

Touring the Service

Header

Navigation Pane

Canvas

Publishing and sharing

Creating a workspace

Publishing

What happens when you publish?

Sharing

Summary

Questions

Further reading

Using Reports in the Service

Technical requirements

Viewing reports

Sizes

Colors

Comments

Bookmarks and persistent filters

Report bookmarks

Personal bookmarks

Persistent filters

Subscribing

Managing subscriptions

Other functions

Exporting reports

Copying

Printing

Export to PowerPoint

Export to PDF

Download report

Embedding reports

Secure embed codes

Using URL parameters with embed codes

Using the pageName parameter

Using the filter parameter

SharePoint Online

Publish to web

Managing publish to web embed codes

Editing and creating reports

Editing a report

Creating a Mobile Layout

Creating a report

Summary

Questions

Further reading

Understanding Dashboards, Apps, and Security

Technical requirements

Understanding dashboards

Creating a dashboard

Working with dashboards

Add tile

Set as featured

Phone view

Ellipses menu

Dashboard themes

Q&A

Working with tiles

Sizing and position

Ellipsis menu

Understanding apps

Creating an app

Getting and using apps

Understanding security and permissions

Workspace permissions

App permissions

Object permissions

Dashboard permissions

Report permissions

Dataset permissions

RLS

Summary

Questions

Further reading

Data Gateways and Refreshing Datasets

Technical requirements

Installing and using data gateways

Downloading and installing a data gateway

Running a data gateway

Personal mode

Standard mode

Configuring a data gateway

Service Settings

Diagnostics

Network

Connectors

Managing a data gateway

Gateway cluster settings and administrators

Removing a gateway and adding data sources

Refreshing datasets

Scheduling a refresh

Summary

Questions

Further reading

Other Books You May Enjoy

Leave a review - let other readers know what you think

Preface

To succeed in today's transforming business world, organizations need business intelligence (BI) capabilities to make smarter decisions faster than ever before. This Power BI book is an entry-level guide that will get you up and running with data modeling, visualization, and analytical techniques from scratch.

You'll find this book handy if you want to become well-versed with the extensive ecosystem of Power BI. You'll start by covering the basics of BI and installing Power BI. You'll then learn about the wide range of Power BI features to unlock business insights. As you progress, the book will take you through how to use Power Query to ingest, cleanse, and shape your data, and use Power BI DAX to create simple to complex calculations. You'll also be able to add a variety of interactive visualizations to your reports in order to bring your data to life. Finally, you'll gain hands-on experience in creating visually stunning reports that speak to business decision makers, and see how you can securely share these reports and collaborate with others.

By the end of this book, you'll be ready to create simple, yet effective, BI reports and dashboards using the latest features of Power BI.

Who this book is for

If you're an IT manager, data analyst, or BI user who is new to using Power BI for solving BI problems, then this book is for you. You'll also find this book useful if you want to migrate from other BI tools to create powerful and interactive dashboards. Note that no experience of working with Power BI is required in order to proceed.

What this book covers

Chapter 1, Introduction to Business Intelligence and Power BI, provides an overview of all of the various components that encompass the Power BI ecosystem, including Power BI Desktop, Power BI Service, Power BI Licensing, Power BI Premium, data gateways, Power BI Report Server, integrations with other Microsoft technologies (such as Office 365, Flow, Visio, and PowerApps), third-party products (such as visuals, and connectors), the Power BI Community, and, if there is room, other Microsoft and third-party websites.

Chapter 2, Up and Running with Power BI Desktop, shows how to download and install the Power BI Desktop. In addition to this, an overview of the major components and interfaces of the Desktop is presented. This includes the Report, Data, and Model panes; the menu tabs; and the Filters, Visualizations, and Fields panes. Finally, we are introduced to the creation of data tables and the creation of visualizations.

Chapter 3, Connecting and Shaping Data, serves as an introduction to the Query Editor to import and transform data, including transposing data, creating custom columns, adding index columns, splitting columns, referencing queries, appending and merging queries, and other transformation functions. In addition to this, you will learn how to create data models using the relationship editor.

Chapter 4, Creating Data Models and Calculations, shows us how to add additional data to our model and create calculated measures. You will use the data and measures to create more advanced visuals, as well as explore your data to understand the important information it contains. You will also use the Q&A feature and more advanced features to unlock insights.

Chapter 5, Unlocking Insights, teaches us how to tell a story with our data by using more advanced features, such as Bookmarks, the Selection pane, Buttons, Drillthrough, and report page tooltips. In addition to this, you will learn about What if parameters and much more.

Chapter 6, Creating the Final Report, demonstrates how to use formatting and other features of Power BI to take a mundane looking report and add flash, pop, and sizzle.

Chapter 7, Publishing and Sharing, shows how to publish our report to the service and share the work with a larger audience.

Chapter 8, Using Reports in the Service, focuses on using reports and dashboards within the Service. This includes all of the various report functions, including editing reports, embedding and exporting reports, bookmarks, usage metrics, and subscriptions. You will also learn how to create and use dashboards, pin tiles and pages to dashboards, and all dashboard functions.

Chapter 9, Understanding Dashboards, Apps, and Security, serves as a gateway to more advanced features of the Service, including how to use workspaces to collaborate with others, how to publish their combined work as an app, and how to find and use other apps.

Chapter 10, Data Gateways and Refreshing Datasets, returns to the subject of data by exploring how to use and manage datasets and workbooks in the Service. In addition to this, this chapter introduces the subject of Data Gateways to assist users in keeping their on-premises data sources up to date. Finally, you will be introduced to dataflows.

To get the most out of this book

A keen interest in solving BI problems will be handy. Some prior experience of using other BI tools is also a bonus.

Download the example code files

You can download the example code files for this book from your account at www.packt.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.

You can download the code files by following these steps:

Log in or register at www.packt.com.

Select the Support tab.

Click on Code Downloads.

Enter the name of the book in the Search box and follow the onscreen instructions.

Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:

WinRAR/7-Zip for Windows

Zipeg/iZip/UnRarX for Mac

7-Zip/PeaZip for Linux

The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/Learn-Power-BI. In case there's an update to the code, it will be updated on the existing GitHub repository.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: http://www.packtpub.com/sites/default/files/downloads/9781838644482_ColorImages.pdf.

Conventions used

There are a number of text conventions used throughout this book.

CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: Download LearnPowerBI.pbix and the Budget and Forecast.xlsx, People and Tasks.xlsx, and Hours.xlsx files from GitHub.

A block of code is set as follows:

{

  name: LearnPowerBI,

    dataColors:

        [

            #EC670F,

            #3C3C3B,

            #E5EAEE,

            #5C85E6,

            #29BCC0,

            #7EAE40,

            #20B2E7,

            #D3DCE0,

            #FF6B0F

        ],

Bold: Indicates a new term, an important word, or words that you see on screen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: Right-click on the following pages and delete them by selecting the Delete page and then the Delete button.

Warnings or important notes appear like this.

Tips and tricks appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, mention the book title in the subject of your message and email us at [e-mail].

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [e-mail] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Reviews

Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!

For more information about Packt, please visit packt.com.

Section 1: The Basics

The objective of this section is to introduce you to key concepts, example scenarios, and downloading supporting data of Power BI.

This section comprises of the following chapter:

Chapter 1, Introduction to Business Intelligence and Power BI

Introduction to Business Intelligence and Power BI

Power BI is a powerful ecosystem of business intelligence tools and technologies from Microsoft. But what exactly is business intelligence, anyway? Simply stated, business intelligence is all about leveraging data in order to make better decisions. This can take many forms and is not necessarily restricted to just business. We use data in our personal lives to make better decisions as well. For example, if we are remodeling a bathroom, we get multiple quotes from different firms. The prices and details in these quotes are pieces of data that allow us to make an informed decision in terms of which company to choose. We may also research these firms online. This is more data that ultimately supports our decision.

In this chapter, we will explore the key fundamental concepts of business intelligence, as well as why business intelligence is important to organizations. In addition, we take a high-level tour of the Power BI ecosystem, licensing, and core tools such as the Power BI Desktop and the Power BI Service.

The following topics will be covered in this chapter:

Key concepts of business intelligence

The Power BI ecosystem

Power BI licensing

Power BI Desktop and Power BI Service

Key concepts of business intelligence

Business intelligence, in the context of organizations, revolves around making better decisions about your business. Unlike the example in the introduction, organizations are not generally concerned with bathrooms, but rather with what can make their business more effective, efficient, and profitable. The businesses that provided those quotes on bathroom remodeling need to answer questions such as the following:

How can the business attract new customers?

How can the business retain more customers?

Who are the competitors and how do they compare?

What is driving profitability?

Where can expenses be diminished?

There are endless questions that businesses need to answer every day, and these businesses need data coupled with business intelligence tools and techniques in order to answer these questions and make effective operational and strategic decisions.

While business intelligence is a vast subject in and of itself, the key concepts of business intelligence can be broken down into five areas:

Domain

Data

Model

Analysis

Visualization

Domain

A domain is simply the context within which business intelligence is applied. Most businesses are comprised of relatively standard business functions or departments, such as the following:

Sales

Marketing

Manufacturing/production

Logistics

Research and development

Purchasing

Human resources

Accounting/finance

Each of these business functions or departments represents a domain within which business intelligence can be used to answer questions that can assist us in making better decisions.

The domain helps in narrowing down the focus regarding which questions can be answered and what decisions need to be