Microsoft Power BI Quick Start Guide E-Book

Devin Knight

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 365

Veröffentlichungsjahr: 2022

Beschreibung

Updated with the latest features and improvements in Power BI, this fast-paced yet comprehensive guide will help you master the core concepts of data visualization quickly. You’ll learn how to install Power BI, design effective data models, and build basic dashboards and visualizations to help you make better business decisions. This new edition will also help you bridge the gap between MS Excel and Power BI.

Throughout this book, you’ll learn how to obtain data from a variety of sources and clean it using the Power Query Editor. You’ll also start designing data models to navigate and explore relationships within your data and building DAX formulas to make data easier to work with. Visualizing data is a key element of this book, so there’s an emphasis on helping you get to grips with data visualization styles and enhanced digital storytelling.

As you progress, you’ll start building your own dataflows, gain an understanding of the Common Data Model, and automate dataflow refreshes to eradicate data cleaning inefficiency. You’ll learn how to administer your organization's Power BI environment so that deployment can be made seamless, data refreshes can run properly, and security can be fully implemented.

By the end of this Power BI book, you’ll know how to get the most out of Power BI for better business intelligence.

Leseprobe

Microsoft Power BI Quick Start Guide

Third Edition

The ultimate beginner’s guide to data modeling, visualization, digital storytelling, and more

Devin Knight

Erin Ostrowsky

Mitchell Pearson

Bradley Schacht

BIRMINGHAM—MUMBAI

Microsoft Power BI Quick Start Guide

Third Edition

Copyright © 2022 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 authors, 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.

Senior Publishing Product Manager: Devika Battike

Acquisition Editor – Peer Reviews: Gaurav Gavas

Project Editor: Parvathy Nair

Content Development Editor: Edward Doxey

Copy Editor: Safis Editing

Technical Editor: Aniket Shetty

Proofreader: Safis Editing

Indexer: Manju Arasan

Presentation Designer: Rajesh Shirsath

First published: July 2018

Second edition: October 2020

Third edition: November 2022

Production reference: 2140423

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80461-349-8

www.packt.com

Contributors

About the authors

Devin Knight is the President of Training at Pragmatic Works. At Pragmatic Works, Devin determines which courses are created, delivered, and updated for customers, including 22+ Power BI courses. This is the eleventh SQL Server and BI book that he has authored. Devin often speaks at conferences such as PASS Summit, PASS Business Analytics Conference, SQL Saturdays, and Code Camps. Making his home in Jacksonville, FL, Devin is a contributor at the local Power BI User Group.

I must give thanks to God; without God in my life, I would not be as blessed as I am daily. Thanks for the amazing team of authors: Mitchell, Brad, and Erin have put in time after hours away from their families to bring this great book together. To my wife, Erin, and three children, Collin, Justin, and Lana, who were all patient during nights that daddy had to spend writing. Finally, I would like to thank Jim Halpert, the best Flonkerton coach a rising star like myself could ask for. This year will surely be our year to win gold.

Erin Angela Ostrowsky recently left her role as the appointed lead for reporting and business intelligence at Nevada Gold Mines, a Barrick Gold Corporation (NYSE: GOLD) and Newmont Corporation (NYSE: NEM) joint venture. She is launching a corporate training company, which focuses on upskilling business and IT teams as they pursue value-driven enterprise data architecture solutions.

Erin’s career started with the American City Business Journals as a research director covering 16 industry verticals and over 6,000 businesses across Northeast Florida. Her proven talent for producing and converting primary and secondary research into visually appealing information packages for executive stakeholders eventually led her to training and consulting on the Microsoft Power Platform.

Erin loves to travel and has invested time working on humanitarian projects in the Dominican Republic, Haiti, and France. She is a passionate listener and storyteller who enjoys bridging diverse teams so they can deliver results on time and in budget.

I would like to thank my sisters and cousins, dear friends, mentors, and teachers for their encouragement and honesty along the way — you are truly the greatest gifts God ever gave me.

Mitchell Pearson has worked as a Data Platform Consultant and Trainer for the last 10 years. Mitchell has authored books on SQL Server, Power BI and the Power Platform. His data platform experience includes designing and implementing enterprise level BI solutions with the Microsoft SQL Server stack (T-SQL, SSIS, SSAS, SSRS), the Power Platform, and Microsoft Azure.

Mitchell is very active in the community, presenting at user groups locally and virtually, and creating YouTube videos for Pragmatic Works.

First, I would like to thank God for his grace, provision, and blessings. I would like to thank my wife and children for their patience and support as I worked on this book. I would also like to thank Brian Knight for the opportunity to learn and grow in the field of business intelligence. Finally, I would like to thank Anthony Martin, Dustin Ryan, Bradley Schacht, Devin Knight and the many others not named here, each of these individuals have provided guidance and mentoring through the years and have had a profound impact on my career.

Bradley Schacht is a Principal Program Manager on the Microsoft Azure Synapse Analytics product team based in Jacksonville, FL. Bradley is a former consultant and trainer, and has authored 4 other SQL Server and Power BI books. As a member of the Azure Synapse product team, Bradley works directly with customers to solve some of their most complex data warehousing problems and helps shape the future of the Azure Synapse cloud service. Bradley gives back to the community through speaking at events such as the PASS Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.

I give thanks to God, who I would be lost without and who has blessed me in so many ways. Thanks to my beautiful, amazing wife, Nichole, and our two boys, Oliver and Levi, for all the support and encouragement. I can’t imagine a life without you. To my co-authors, who all worked hard and sacrificed to make this book possible. Finally, to the Atlanta Braves, for winning the Series last season and proving dreams do come true.

About the reviewers

Back in 2010, Riccardo Perico started working in Information Technology. After a brief experience as an ERP consultant, he moved into the data realm. He spent these years exploring the world of datafrom different points of view, both as a DBA and a Business Intelligence Engineer. He is a Microsoft Certified Professional at different levels in databases, BI, and Azure, as well as a Microsoft MVP in the Data Platform category. He is one of the leaders of the Power BI User Group in Italy, and he has been a speaker during the Power Platform World Tour and Power Platform Bootcamp stops in Italy. You can find him speaking at Azure Saturday, SQL Saturday, and Data Saturday events in Europe.

Being part of the reviewer team of this book is another milestone in my career that wouldn’t have been possible without my company Lucient Italia (trademark by One4), and my colleagues Danilo, Davide, Elena, Lorenzo, Luca, Massimiliano, Mattia, Saverio, Selena, Sergio, Sergio, and Veronica. Last but not least, special thanks to my girlfriend Jessica for her support during this and all the other journeys of our life.

Greg Deckler is a Microsoft MVP for Data Platform and an active member of the Columbus Ohio IT Community, having founded the Columbus Azure ML and Power BI User Group (CAMLPUG) and presented at many conferences and events throughout the country. An active blogger, YouTuber and community member interested in helping new users of Power BI, Greg actively participates in the Power BI Community, having authored over 180 Power BI Quick Measures Gallery submissions and over 5,000 authored solutions to community questions. Greg is Vice President of Cloud Services at Fusion Alliance, a regional consulting firm and assists customers in gaining competitive advantage from the cloud and cloud-first technologies like Power BI. Greg has also authored five books on Power BI, including Learn Power BI, DAX Cookbook, Power BI Cookbook 2nd Edition and Mastering Power BI 2nd Edition. Finally, Greg has also authored numerous external tool for the Power BI Desktop, including MSHGQM, Power Sort, Conductor, and Metadata Mechanic.

I would like to thank my son, my family, and the entire Power BI community for all of their love and support.

Jonathon Silva is a training specialist and tech consultant at Pragmatic Works, a training company located in Jacksonville, FL, focused on driving technology adoption through education. Prior to working with Pragmatic Works, Jonathon spent 9 years teaching high school history and economics before transitioning into the world of data and analytics. As a lifelong learner, he enjoys taking on new fields of study and challenging himself to master the necessary skills and knowledge to meet all the needs of his clients. Jonathon specializes in Microsoft’s Power Platform, specifically with Power BI and Power Automate, and loves to provide a full immersive and tailored experience for all students in his courses.

I would not be here without the love and support of my beautiful wife, Erica. She is the love of my life, mother of our three amazing boys Christian, Justin, and Julian, and the glue that holds our crazy world together. I want to thank her, along with my parents, for all the grace and support through each and every moment of my life.

Join our community on Discord

Join our community’s Discord space for discussions with the authors and other readers:

https://packt.link/ips2H

Contents

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Gaining Data Literacy with Power BI

The challenge facing organizations with low data literacy

Overcoming low data literacy with a data strategy

Why choose Power BI

Migrating your Excel skills to Power BI

Summary

Connecting to Data

Getting started

Importing data

Excel as a source

SQL Server as a source

Dataverse as a source

Web as a source

DirectQuery

Limitations

Composite models

Live connection

Limitations

Choosing a data connection method

Summary

Data Transformation Strategies

The Power Query Editor

Transformation basics

Use First Row as Headers

Remove Columns

Change Type

Add Column From Examples

Advanced data transformation options

Add Conditional Columns

Fill Down

Unpivot

Merge Queries

Append Query

The R programming language

Installation and configuration

The R script transform

AI Insights

Sentiment Analysis with Text Analytics

The M formula language

Exploring the M Query library with #shared

Summary

Building the Data Model

Organizing data with a star schema

Building relationships

Editing relationships

Creating a new relationship

Creating a relationship on the date key

Disabling automatically created date tables

Working with complex relationships

Many-to-many relationships

Filtering behavior

Cross-filtering direction

Enabling filtering from the many side of a relationship

Role-playing tables

Importing the date table

Usability enhancements

Hiding tables and columns

Renaming tables and columns

Default summarization

How to display one column but sort by another

Data categorization

Creating hierarchies

Improving data model performance

Query performance

Data model design methodologies

Importing data

DirectQuery

Aggregations

Processing performance

Query folding

Incremental refresh

Best practices

Summary

Leveraging DAX

Building calculated columns

Creating calculated columns with string functions

Using the FORMAT function in DAX

Implementing conditional logic with IF()

Implementing conditional logic with SWITCH()

Leveraging existing relationships with navigation functions

Creating calculated measures

Creating basic calculated measures

Total Sales

Total Cost

Profit

Profit Margin

Optional parameters

Assignment of calculated measures

Display folders

Understanding filter context

Using CALCULATE() to modify filter context

Calculating the percentage of total

Working with time intelligence functions

Creating year-to-date calculations

Creating prior year calculations with CALCULATE()

Role-playing tables with DAX

Summary

Visualizing Data

Report view basics

Creating new visuals

Filtering visualizations and data

Cross-filtering and cross-highlighting

Edit interactions

Slicer

String/text

Numeric

Date

Report filters

Visualizing tabular data

Table

Matrix

Visualizing categorical data

Bar and column charts

Pie and donut charts

Treemap

Scatter chart

Visualizing trend data

Line and area charts

Combo charts

Ribbon chart

Waterfall chart

Funnel chart

Visualizing KPI data

Gauge

KPI

Visualizing data using cards

Card

Multi-row card

Visualizing geographical data

Map

Filled map

Shape map

ArcGIS Map

Azure maps

Advanced visualizations

Natural language with Q&A

Visuals from analytics

Power BI custom visuals

Data visualization tips and tricks

Changing visuals

Formatting visuals

The Analytics section

The Top N filter

Show value as

Summary

Digital Storytelling with Power BI

Configuring drill through

Capturing report views with bookmarks

Combining object visibility with bookmarks

Bookmarking alternate views of the same data

Using buttons to select bookmarks

Using the Bookmark navigator

Report pages as tooltips

Using the Page navigator

Summary

Using a Cloud Deployment with the Power BI Service

Exploring the Power BI service

Creating workspaces

Deploying reports to the Power BI service

Datasets

Workbooks

Creating and interacting with dashboards

Creating your first dashboard

Asking your dashboard a question

Sharing your reports and dashboards

Subscribing to reports

Power BI apps

Maintaining dashboards

Setting up row-level security

Scheduling data refreshes

Using Metrics to track progress

Summary

Data Cleansing in the Cloud with Dataflows

Getting started with dataflows

Exploring datamarts

Creating a dataflow

Using dataflows as a data source in Power BI Desktop

Summary

On-Premises Solutions with Power BI Report Server

Accessing Power BI Report Server

Deploying to Power BI Report Server

Deploying a Power BI report

Deploying a paginated report

Securing reports

Scheduling data refreshes

Summary

Other Books You May Enjoy

Index

Landmarks

Cover

Index

Share your thoughts

Once you’ve read Microsoft Power BI Quick Start Guide, Third Edition, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.

Download a free PDF copy of this book

Thanks for purchasing this book!

Do you like to read on the go but are unable to carry your print books everywhere?Is your eBook purchase not compatible with the device of your choice?

Don’t worry, now with every Packt book you get a DRM-free PDF version of that book at no cost.

Read anywhere, any place, on any device. Search, copy, and paste code from your favorite technical books directly into your application. 

The perks don’t stop there, you can get exclusive access to discounts, newsletters, and great free content in your inbox daily

Follow these simple steps to get the benefits:

Scan the QR code or visit the link below

https://packt.link/free-ebook/9781804613498

Submit your proof of purchaseThat’s it! We’ll send your free PDF and other benefits to your email directly

1

Gaining Data Literacy with Power BI

The amount of data produced and collected in the world daily is growing dramatically. As of a 2017 study (https://www.forbes.com/sites/bernardmarr/2018/05/21/how-much-data-do-we-create-every-day-the-mind-blowing-stats-everyone-should-read/), the best estimates are that 2.5 quintillion bytes of data are generated each day, but that number is only expected to have grown since that study is a number of years old now and, more importantly, with the popularity of Internet of Things (IoT) devices. With such staggering numbers, it produces major problems for organizations trying to ensure their workforce has a high level of data literacy.

Not unlike learning a foreign language, data literacy is the concept of reading, understanding, and communicating with data. In its simplest form, someone with high data literacy skills would know how to take raw data provided to them and convert it into something they can use to drive business decisions. This is a skill that takes time to learn but once an individual masters it, they can become incredibly valuable to an organization. Without high levels of data literacy, organizations can seemingly make decisions on gut feelings without supporting data influencing business plans.

The challenge facing organizations with low data literacy

With the amount of data collected, one would assume that every organization treats the data they collect as an incredibly prized resource. However, that’s far from true. Many organizations are struggling to understand the meaning behind key business metrics and how those metrics should serve as indicators for driving timely business decisions.

Many organizations lack the skills required to properly show the value behind their data. Other companies take the approach of having only a select few that specialize in understanding and utilizing their data. While this strategy is better than complete data ignorance, it’s still as if every organization has amassed a collection of the world’s most important books for gaining knowledge but only a small percentage of employees actually know how to read.

More forward-thinking companies realize that data in the hands of just a few experts creates a bottleneck, and the optimal strategy is to democratize data to the masses. As organizations grow, it’s easy to become overwhelmed with these problems, but if companies don’t put an emphasis on treating data as an asset, they will quickly fall behind competitors who put a priority on data literacy.

Overcoming low data literacy with a data strategy

So how should organizations facing the challenge of staff with low data literacy respond? The first thing leadership must focus on is developing a data strategy. Data itself has no intrinsic value without a strategy for using it properly. The goal of a data strategy is to provide an organization-wide plan on how data should be collected, stored, protected, and analyzed. Without such a plan, an organization is susceptible to issues like data loss, violating international personal data collection laws, and even data breaches, which you hear about often in the news.

It is important to realize that every organization is different, and each has its own unique set of challenges to working with data. So, you shouldn’t stress out about trying to find the definitive data strategy guide because it doesn’t exist! There is no one-size-fits-all data strategy approach for all organizations. For example, a data strategy for a university would look very different from a data strategy for a Fortune 500 company. The former is focused on the success of their students while the latter is likely focused on overall profitability. During the planning of a data strategy, an evaluation of each segment of data must be completed to determine how the data will be processed, stored, and shared. This process will often uncover that not all organizational data should be treated the same. For example, timecard entry data from two years ago is far less important than financial statements from the same timeframe. The idea is that a data strategy should be more granular and not make large declarations about all data. Some data is more valuable than others and the time and resources spent should not be the same for all data. An organization’s data strategy should be centered around its unique needs, but the point of this chapter is not to give you a step-by-step guide on developing a data strategy. More than anything, this is to stress the importance of simply having one!

The second thing organization leadership should focus on to drive higher data literacy is building a data culture. An organization with a healthy data culture is inclusive, meaning it puts data in the hands of everyone, leaving no one left out. This can be challenging when there is such a skill gap between the typical business user and a professional data analyst or data scientist. So, what do you do to overcome that skill gap?

Anyone can improve their data literacy skills, but the question is: what is your organization doing to foster an environment that encourages engaging with and thinking about data? Many organizations are embracing a positive data culture by promoting data enablement programs, which include ways for individuals to improve their skills with both training and mentoring. An enablement program is far more than sending a group of eager data enthusiasts to a class and hoping they learn enough to be productive. A thoughtful data enablement program is an ongoing exercise over the course of weeks and sometimes months to groom your team into becoming citizen developers.

Gartner defines a citizen developer as:

”An employee who creates applications capabilities for consumption by themselves or others, using tools that are not actively forbidden by IT or business units. A citizen developer is a persona, not a title or targeted role. They report to a business unit or function other than IT.”

Growing a data culture full of citizen developers doesn’t happen all at once. Many organizations often start by building a group of data champions. This group would consist of individuals from multiple departments that are eager to learn and ready to make a commitment to improving their data literacy skills. The primary goal is to grow experts in each department so that way, as new citizen developers emerge, they have a known resource within their department who can help them learn. As you might expect, it’s always easier to bounce ideas off someone who knows the kind of data you work with rather than someone who is unfamiliar with your data

Education in data literacy can vary from broad topics that apply to any data analytics tool or more specific tutorials geared toward a particular technology you want your citizen developers leveraging. An example of tool-agnostic learning would be how to spot trends in your data, how to determine outliers in data, or even how to choose the best visualization for the data you are working with. Any of these topics can apply to every data analytics tool on the market. While there are many great data analytics tools on the market, this book spotlights Power BI.

You might have gravitated toward this book for a number of reasons. Maybe you are completely new to Power BI, and you needed a way to kickstart your learning. Perhaps you have been learning about Power BI for some time now but you’re completely self-taught, so you are hoping to fill in the gaps of things you just haven’t seen yet. This would be the phase of learning where many would say, “I don’t know what I don’t know.” Whatever the case may be for you, the authors of this book hope to give you the essentials necessary for achieving high data literacy within Power BI.

Why choose Power BI

By grabbing this book, there’s a bit of an assumption that you have already made the decision that Power BI is the tool you or your organization has chosen. If for some reason you are still on the fence, or perhaps Power BI is one of many business intelligence tools your organization uses, then it’s helpful to have an understanding of why so many have already made Power BI their data analytics tool of choice.

Let’s start with collaboration. One of Power BI’s central goals is to get data in the hands of decision-makers. So even though Power BI does come with a central cloud-based portal that users can visit, it may not make sense to give users a new web page to bookmark in their browser. Collaboration really means bringing Power BI to where your users are rather than forcing them to go somewhere new. The way Power BI does this is with integration into many of the tools you know and love from Microsoft like SharePoint, Excel, PowerPoint, Dynamics, Teams, and even your mobile phone. Within each of these tools, Power BI allows collaboration and discussion to occur around the data visualized on reports. This idea of bringing data to where your users already are is one of the significant ways Power BI helps grow a data culture.

Another reason many are drawn to Power BI is because of its ease of use. As you work your way through this book, you will find that Power BI has a very intuitive interface. It allows you to quickly connect to data, build data cleansing transformations, create relationships between data sources, and visualize your data in minutes. More complex problems can take longer of course, but Power BI tends to follow this 80/20 rule: 80 percent of the problems you encounter in Power BI can be solved with a 20 percent level of Power BI knowledge. The deeper knowledge is important of course, but the times you will actually need it are far rarer.

One of the major considerations when picking a business intelligence tool is price. Fortunately, when comparing many of the other top tools on the market, Power BI wins on cost hands down. The competitor with the closest feature parity is seven times more expensive than Power BI for basic report development.

It is important to note that licensing costs can vary depending on your specific needs so this chapter won’t go into more detail on it here. However, we would recommend reviewing licensing details here: https://powerbi.microsoft.com/en-us/pricing/.

If these reasons aren’t enough, look at the unbiased annual survey performed by Gartner for analytics and BI platforms. Gartner Inc. is a well-recognized technology research firm that conducts research on technologies, which it shares with the public. Power BI continually rates as the highest tool on the market in the categories of “Completeness of Vision” and “Ability to Execute.”

Migrating your Excel skills to Power BI

Microsoft Excel is the number one most popular computer program in the world. While Excel is an amazing tool, the millions of users using it to analyze their data are thirsty for more. Fortunately, Power BI was designed with the Excel fanatic in mind. Many of the skills collected over time while designing Excel solutions still apply in Power BI. Concepts like modeling data, writing Excel formulas, and building PivotTables, all have comparable features in Power BI.

Excel was the first self-service business intelligence tool provided by Microsoft. Starting in Excel 2010, features known as Power Pivot and later Power Query were added to enable more advanced data analytics problem solving that traditional Excel could not handle. These two features would later become the core building blocks for what Power BI is today. So much so that even today, any Excel solution developed using Power Pivot and Power Query can be migrated into Power BI via a simple migration wizard.

Having an understanding of these additional Excel features can give someone an incredible head start when learning Power BI. If you are reading this book and feel confident in your Excel skills, pay close attention throughout this book to each tutorial and consider how you would have solved the various use cases in Excel. You will likely find that Power BI is an incredible time saver over how you would have previously solved these problems in Excel.

Summary

While low levels of data literacy continue to be a massive challenge for organizations, there remains hope for improvement! Technologies like Power BI are one, but not the only, ingredient for a successful data literacy recipe. A well-thought-out data strategy cannot be overlooked. Without a proper plan for your organization’s data, you are treating one of your biggest assets too nonchalantly. Commitment to a data strategy doesn’t happen without buy-in on a culture change within your organization. A strong data culture leads to enablement that scales throughout your organization.

In the next chapter, you will get your first look at the capabilities of Power BI as you learn about the data connectivity options that are available.

Join our community on Discord

Join our community’s Discord space for discussions with the authors and other readers:

https://packt.link/ips2H

2

Connecting to Data

Power BI may very well be one of the most aptly named tools ever developed by Microsoft, giving analysts and developers a powerful business intelligence and analytics playground while still packaging it in a surprisingly lightweight application. Using Microsoft Power BI, the processes of data discovery, modeling, visualization, and sharing are made elegantly simple using a single product. These processes are so commonplace when developing Power BI solutions that this book has adopted sections that follow this pattern. However, from your perspective, the really exciting thing may be that development problems that would have previously taken you weeks to solve in a corporate BI solution can now be accomplished in only hours.

Using the Power BI Desktop application enables you to define your data discovery and data preparation steps, organize your data model, and design engaging data visualizations based on your reports. In this chapter, the development environment will be introduced, and the data discovery process will be explored in depth. The topics detailed in this chapter include the following:

Getting startedImporting dataDirectQueryComposite modelsLive connectionChoosing a data connection method

Let’s first start by learning about Power BI at a high level, and understanding what you need on your machine to get started.

Getting started

Power BI is a Software as a Service (SaaS) offering in the Azure cloud and, as such, the Microsoft product team follows a strategy of cloud first as they develop and add new features to the product. Power BI is also one of five members of Microsoft’s Power Platform:

Figure 2.1: Microsoft’s Power Platform suite of tools

Each of the tools within the Power Platform solves distinctly different problems, but what they have in common is who their core user audience is intended to be. The Power Platform and all the tools included within it are designed and built so business users can solve and design solutions on their own rather than relying solely on their IT department. All five tools within the Power Platform leverage Microsoft’s cloud architecture and allow for seamless integration between the different tools. Leveraging the features of each Power Platform tool (Power BI, Power Apps, Power Pages, Power Automate, and Power Virtual Agents) can give your business incredible advantages over the competition.

However, since this book’s focus is on Power BI, if you’re interested in learning more about the other members of the Power Platform, we would recommend reviewing other titles by Packt to explore each.

While Power BI is primarily a cloud application, this does not mean that Power BI is only available in the cloud. Microsoft presents two options for sharing your results with others. The first, most often utilized method is the cloud-hosted Power BI service, which is available to users for a low monthly subscription fee. The second option is the on-premises Power BI Report Server, which can be obtained through either your SQL Server Enterprise licensing with Software Assurance or a subscription level known as Power BI Premium. Both solutions require a development tool called Power BI Desktop, which is available for free, and is generally where you will start to design your solutions.

Power BI Desktop can be found via a direct download link at Power BI (https://powerbi.microsoft.com/en-us/desktop/), or by installing it as an app from the Microsoft Store. There are several benefits of using the Microsoft Store Power BI Desktop app, including automatic updates, no requirement for admin privileges, and making it easier for a planned IT rollout of Power BI.

If you are using the on-premises Power BI Report Server for your deployment strategy, then you must download a different Power BI Desktop, which is available by clicking the advanced download options link at https://powerbi.microsoft.com/en-us/report-server/. A separate installation is required because updates are released less frequently to the non-cloud version of Power BI (Power BI Report Server). This book will be written primarily under the assumption that you are using the cloud-hosted Power BI service as your deployment strategy.

Once you’ve downloaded, installed, and launched Power BI Desktop, you will likely be welcomed by the start-up screen, which is designed to help new users find their way.

Close this start-up screen so that we can review some of the most commonly used features of the application:

Figure 2.2: First view of the Power BI Desktop

Following the preceding screenshot, let’s learn the names and purposes of some of the most important features in Power BI Desktop:

Get data: Used for selecting data connectors and configuring data source details.Transform data: Launches the Power Query Editor, which is used to apply data transformations to incoming data.Report view: Report canvas used for designing data visualizations. This is the default view that’s open when Power BI Desktop is launched.Data view: Provides a view of the data in your model. This looks similar to a typical Excel spreadsheet, but it is read-only.Model view: Primarily used when your data model has multiple tables and relationships that need to be defined between them.

Now that you have a little familiarity with the basic controls within Power BI Desktop, let’s learn about the options you have for connecting to your various data sources. Power BI is best known for the impressive data visualizations and dashboard capabilities it has. However, before you can begin building reports, you first need to connect to the necessary data sources.

Within Power BI Desktop, a developer has many unique data connectors to choose from, including traditional file types, database engines, big data solutions, cloud sources, data stored on a web page, and other SaaS providers. This book will not cover all the connectors that are available, but it will highlight some of the most popular and common ways to connect to data.

When establishing a connection to a data source, you may be presented with one of three different options regarding how your data will be treated: Import, DirectQuery, or LiveConnection. The sections that follow will focus specifically on these options.

Importing data

Choosing to import data, which is the most common option and default behavior, means that Power BI will physically extract rows of data from the selected source and store it in an in-memory storage engine within Power BI. Power BI Desktop uses a special method for storing data, known as xVelocity, which is an in-memory technology that not only increases the performance of your query results but can also highly compress the amount of space taken up by your Power BI solution. In some cases, the compression that takes place can even lower the required disk space by up to one-tenth of the original data source size. This data compression occurs automatically, meaning there is no required configuration step you must do to receive this benefit. The xVelocity engine uses a local unseen instance of SQL Server Analysis Services (SSAS) to provide these in-memory capabilities.

There are consequences to using the Import option within Power BI that you should also consider. These consequences will be discussed later in this chapter, but as you read on, consider the following: