Microsoft Power BI Cookbook E-Book

Greg Deckler

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

Seitenzahl: 766

Veröffentlichungsjahr: 2021

Beschreibung

The complete everyday reference guide to Power BI, written by an internationally recognized Power BI expert duo, is back with a new and updated edition.
Packed with revised practical recipes, Microsoft Power BI Cookbook, Second Edition, helps you navigate Power BI tools and advanced features. It also demonstrates the use of end-to-end solutions that integrate those features to get the most out of Power BI. With the help of the recipes in this book, you’ll gain advanced design and development insight, practical tips, and guidance on enhancing existing Power BI projects.
The updated recipes will equip you with everything you need to know to implement evergreen frameworks that will stay relevant as Power BI updates. You’ll familiarize yourself with Power BI development tools and services by going deep into the data connectivity, transformation, modeling, visualization, and analytical capabilities of Power BI. By the end of this book, you’ll make the most of Power BI’s functional programming languages of DAX and M and deliver powerful solutions to common business intelligence challenges.

Leseprobe

Microsoft Power BI Cookbook

Second Edition

Gain expertise in Power BI with over 90 hands-on recipes, tips, and use cases

Greg Deckler

Brett Powell

BIRMINGHAM—MUMBAI

Microsoft Power BI Cookbook

Second Edition

Copyright © 2021 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.

Producer: Suman Sen

Acquisition Editor – Peer Reviews: Saby Dsilva

Project Editor: Parvathy Nair

Content Development Editor: Alex Patterson

Copy Editor: Safis Editor

Technical Editor: Aditya Sawant

Proofreader: Safis Editor

Indexer: Manju Arasan

Presentation Designer: Ganesh Bhadwalkar

First published: September 2017

Second edition: September 2021

Production reference: 2141022

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80181-304-4

www.packt.com

Contributors

About the authors

Greg Deckler is a Vice President at Fusion Alliance and has been a professional of consulting services for over 27 years. Recognized as an expert in Power BI, Greg is a six-time Microsoft MVP for the Data Platform and an active member of the Power BI Community site with over 5,000 solutions authored and hundreds of Quick Measure Gallery submissions. Greg founded the Columbus Azure ML and Power BI User Group in Columbus, OH in 2016 and continues to hold regularly monthly meetings.

I would like to thank the dynamic and vibrant Power BI community as a whole and especially all of the Super Users and my User Group members. Finally, I'd like to thank my son, Rocket, for keeping me supplied with excellent graphics and videos!

Brett Powell owns Frontline Analytics LLC, a data and analytics consultancy specializing in the Microsoft data platform. Brett has over ten years of experience in business intelligence and data warehouse projects as both a developer and technical architect. In addition to project engagements, Brett shares technical tips and examples via his popular Insights Quest blog and has authored two commercially successful books – Mastering Microsoft Power BI and Microsoft Power BI Cookbook.

I'd first like to acknowledge and praise Jesus Christ, my Lord and Savior, for blessing me with these opportunities and for loving me and guiding me each day. I'd also like to thank my mother, Cathy, and brother Dustin, for their endless support and encouragement. Finally, I'd like to thank Packt for their efforts to produce quality, valuable content.

About the reviewer

David R. Eldersveld is a Technical Specialist at Microsoft currently focused on Power BI. Prior to joining Microsoft, he was a Data Platform MVP and spent nearly ten years as a data and analytics consultant with CapstoneBI and BlueGranite. David has contributed to technical communities as a blogger, speaker, and contributor to Workout Wednesday's Power BI challenges.

Contents

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Configuring Power BI Tools

Technical Requirements

Installing and Configuring Power BI Desktop

Creating an On-Premises Data Gateway

Installing Analyze in Excel from the Power BI Service

Installing and Configuring Additional Tools

Conclusion

Accessing and Retrieving Data

Technical Requirements

Viewing and Analyzing M Functions

Managing Queries and Data Sources

Using DirectQuery

Importing Data

Applying Multiple Filters

Selecting and Renaming Columns

Transforming and Cleansing Data

Creating Custom Columns

Combining and Merging Queries

Selecting Column Data Types

Visualizing the M library

Profiling Source Data

Diagnosing Queries

Conclusion

Building a Power BI Data Model

Technical Requirements

Designing a Data Model

Implementing a Data Model

Creating Relationships

Configuring Model Metadata

Hiding Columns and Tables

Enriching a Model with DAX

Supporting Virtual Table Relationships

Creating Hierarchies and Groups

Designing Column Level Security

Leveraging Aggregation Tables

Conclusion

Authoring Power BI Reports

Technical Requirements

Building Rich and Intuitive Reports

Filtering at Different Scopes

Integrating Card visualizations

Using Slicers

Controlling Visual Interactions

Utilizing Graphical Visualizations

Creating Table and Matrix visuals

Enhancing Reports

Formatting Reports for Publication

Designing Mobile Layouts

Creating Paginated Reports

Conclusion

Working in the Service

Technical Requirements

Building a Dashboard

Preparing for Q&A

Adding Alerts and Subscriptions

Deploying Content from Excel and SSRS

Streaming Datasets

Authoring Dataflows

Conclusion

Getting Serious with Date Intelligence

Technical Requirements

Building a Date Dimension Table

Preparing the Date Dimension via the Query Editor

Authoring Date Intelligence Measures

Developing Advanced Date Intelligence Measures

Simplifying Date Intelligence with DAX and Calculated Tables

Leveraging Calculation Groups

Conclusion

Parameterizing Power BI Solutions

Technical requirements

Filtering reports dynamically

Leveraging query parameters

Working with templates

Converting static queries to dynamic functions

Capturing user selections with parameter tables

Forecasting with what-if analysis

Conclusion

Implementing Dynamic User-Based Visibility in Power BI

Technical Requirements

Capturing Current User Context

Defining RLS Roles and Filter Expressions

Designing Dynamic Security Models

Building Dynamic Security for DirectQuery

Displaying the Current Filter Context

Avoiding Manual User Clicks

Conclusion

Applying Advanced Analytics and Custom Visuals

Technical Requirements

Incorporating Advanced Analytics

Enriching Content with Custom Visuals and Quick Insights

Creating Geospatial Mapping with ArcGIS Maps

Democratizing Artificial Intelligence

Building Animation and Storytelling

Embedding Statistical Analyses

Grouping and Binning

Detecting and Analyzing Clusters

Forecasting and Anomaly Detection

Using R and Python Visuals

Conclusion

Administering and Monitoring Power BI

Technical requirements

Creating a centralized IT monitoring solution with Power BI

Constructing a monitoring, visualization, and analysis layer

Importing and visualizing dynamic management view (DMV) data

Increasing DBA productivity

Providing documentation

Analyzing SSAS tabular databases and gateways

Analyzing Extended Events

Visualizing log file data

Leveraging the Power BI PowerShell Module

Conclusion

Enhancing and Optimizing Existing Power BI Solutions

Technical Requirements

Enhancing Data Model Scalability and Usability

Improving DAX Measure Performance

Pushing Query Processing Back to Source Systems

Strengthening Data Import and Integration Processes

Isolating and Documenting DAX Expressions

Improving Data Load Speeds with Incremental Refresh

Conclusion

Deploying and Distributing Power BI Content

Technical Requirements

Preparing for Content Creation and Collaboration

Managing Content between Environments

Sharing Content with Colleagues

Configuring Workspaces

Configuring On-Premises Gateway Data Connections

Publishing Apps

Publishing Reports to the Public Internet

Enabling the Mobile Experience

Distributing Content with Teams

Conclusion

Integrating Power BI with Other Applications

Technical Requirements

Integrating SSRS and Excel

Migrating from Power Pivot for Excel Data to Power BI

Accessing and Analyzing Power BI Datasets in Excel

Building Power BI Reports into PowerPoint Presentations

Connecting to Azure Analysis Services

Integrating with Power Automate and Power Apps

Leveraging Dataverse and Dynamics 365

Connecting Dynamics 365 Finance and Operations and the Power Platform

Conclusion

Other Book You May Enjoy

Index

Landmarks

Cover

Index

Share Your Thoughts

Once you've read Microsoft Power BI Cookbook, Second 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/9781801813044

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

1

Configuring Power BI Tools

Power BI is a suite of business analytics tools and services that work together to access data sources, shape, analyze, and visualize data, and share insights. Although not all tools are required for all projects or deployments of Power BI, synergies are available by utilizing the unique features of multiple tools as part of integrated solutions encompassing diverse data sources and visualization types.

In this chapter, we walk through the installation and configuration of the primary tools and services business intelligence (BI) professionals utilize to design and develop Power BI content. This includes official Microsoft tools such as Power BI Desktop, the on-premises data gateway, and Analyze in Excel, as well as third-party tools such as DAX Studio, the ALM Toolkit, and Tabular Editor. Since Power BI tools and services are regularly updated with new features and enhancements, resources are identified to stay up to date and to best take advantage of these tools for your projects.

The recipes in this chapter are as follows:

Installing and Configuring Power BI DesktopCreating an On-Premises Data GatewayInstalling Analyze in Excel from the Power BI ServiceInstalling and Configuring Additional Tools

Technical Requirements

It is assumed that the reader has access to a Power BI Pro license, rights to download and install the development tools on their machine, and has the necessary access and rights to deploy and manage content in the Power BI service and utilize the Power BI mobile applications.

Power BI licensing options and assigning and managing these rights are outside the scope of this book. In addition, the following are required to complete the recipes in this chapter:

A Windows PC running Windows 10 version 14393 or higher. There is no Power BI Desktop for Apple computers or operating systems.Microsoft Excel.Ensure that you have at least 10 GB of disk space available; the Power BI Desktop download is up to 600 MB in size and the installed application is over 1.5 GB.A Power BI login for the Power BI service.

Additionally, the Microsoft on-premises data gateway requires the following:

.NET Framework 4.7.2 (Gateway release December 2020 and earlier).NET Framework 4.8 (Gateway release February 2021 and later)A 64-bit version of Windows 8 or later or a 64-bit version of Windows Server 2012 R2 or later with current TLS 1.2 and cipher suites4 GB disk space for performance monitoring logs (in the default configuration)

Installing and Configuring Power BI Desktop

Power BI Desktop is the primary tool used to develop the visual and analytical content that is then deployed and collaborated on in the Power BI service, embedded in other applications and portals, or even shared on the public internet. Although Power BI Desktop runs as a single application, it includes three tightly integrated components with their own options and settings:

The Power Query and Power Query Editor experience, with its underlying M language and data mashup engine.The Analysis Services tabular data modeling engine and its DAX analytical language.The interactive reporting and visualization engine.

Configuring and leveraging these capabilities, in addition to advanced analytics and customization features such as R, Python, mobile layout, and natural language queries, makes it possible to build robust and elegant BI and analytics solutions.

Getting ready

Most organizations restrict the ability of employees to install software such as Power BI Desktop onto their corporate devices. These organizations typically define a policy and process for pushing a particular version of Power BI Desktop to all user devices or to certain groups of users thus avoiding compatibility issues and ensuring users have access to recent features.

How to install and configure Power BI Desktop

There are actually three different versions of the Power BI Desktop application, as follows:

Power BI Desktop (Microsoft Store app)Power BI Desktop (Microsoft Installer)Power BI Desktop RS (Report Server Edition)

The preferred method of installation is by using the Microsoft Store app. In order to install Power BI Desktop from the Microsoft Store, execute the following:

Open the Microsoft Store app and search for Power BI Desktop. Alternatively, you can enter https://aka.ms/pbidesktop into any browser bar and the Microsoft Store app will automatically open to the Power BI Desktop app, as shown in Figure 1.1.It is important to make sure that you are getting the Power BI Desktop app and not just the Power BI app. The Power BI app is for viewing reports published to the Power BI service only.

Figure 1.1: The Power BI Desktop app in the Microsoft Store

Click the Get button.Once the download and installation is complete, click the Launch button.

For a variety of reasons, it may not be possible to install the Microsoft Store app version of Power BI Desktop. This may be because of running an older version of Windows, corporate policies, or a desire to use the 32-bit version of Power BI Desktop (the Microsoft Store app is 64-bit only). In this case, you can install the Microsoft Installer (MSI) version. To install the MSI version, follow these steps:

The Microsoft Installer (MSI) version of Power BI Desktop can be downloaded on the Power BI downloads page: https://powerbi.microsoft.com/downloads.Under Microsoft Power BI Desktop, choose Advanced download options.

Figure 1.2: Download the MSI version using Advanced download options

Select your language preference and click the Download button.Choose either the 64-bit version (PBIDesktopSetup_x64.exe) or the 32-bit version (PBIDesktopSetup.exe) and then click the Next button.Once the file downloads, click Open file.The Microsoft Power BI Desktop Setup Wizard will launch. Select a language and click the Next button.Complete the rest of the installation wizard, including accepting the license terms and destination folder, clicking the Next button each time. On the final screen, click the Install button.Once the installation finishes, click the Finish button.

Figure 1.3: Successful installation of Power BI Desktop (MSI)

If you are using Power BI Report Server (on-premises) you need to download the corresponding version of Power BI Desktop optimized for Report Server (RS version). Updates for Power BI Report Server come out quarterly and there is a specific version of Power BI Desktop released simultaneously that is optimized to work with each quarterly version. The installation is the same as the MSI version. However, to find the download for the RS version, follow these steps:

Use a browser to navigate to https://powerbi.microsoft.com/report-server.Use the Advanced download options link directly under the DOWNLOAD FREE TRIAL button.

Figure 1.4: Download the RS version using Advanced download options

How it works

Power BI Desktop supports a rich set of configuration options both for the currently open file and for all instances of Power BI Desktop on the given device. These options control the availability of preview and end user features and define default behaviors resource usage, security, and privacy/isolation of data sources.

Regardless of the version of Power BI Desktop that is installed, these options are available by using the File menu in the ribbon and then selecting Options and settings, and then Options, as shown in Figure 1.5.

Figure 1.5: Power BI Desktop Options and settings

Selecting Options brings up the Options window, displayed in Figure 1.6.

Figure 1.6: Power BI Desktop Options (February 2021)

GLOBAL options are applied to all Power BI Desktop files created or accessed by the user, while CURRENT FILE options must be defined for each Power BI Desktop file. The following steps are recommended for GLOBAL options:

On the Data Load tab, under Data Cache Management Options, confirm that the currently used data cache is below the Maximum allowed (MB) setting. If it is near the limit and local disk space is available, increase the value of Maximum allowed (MB). Do not clear the cache unless local disk space is unavailable as this will require additional, often unnecessary, queries to be executed.

Figure 1.7: Global Data Load options

In addition, under the Time intelligence heading, consider turning off Auto date/time for new files. While its convenient to have out-of-the-box calendar hierarchy functionality for date columns, this can also significantly increase the size of a data model and its strongly recommended to utilize a common date dimension table, preferably from an corporate data warehouse source. Finally, under the Type Detection heading, it is recommended that you choose the option to Never detect column types and headers for unstructured sources. The default detection of column types from unstructured sources such as text or Excel files will create a hardcoded dependency on the column names in the source file. Additionally, this default transformation will be applied prior to any filter expression and thus can require more time and resources to perform the refresh.

On the Power Query Editor tab, under the Layout header, ensure that Display the Query Settings pane and Display the Formula Bar are both checked, as seen in Figure 1.8.

Figure 1.8: Power Query Editor Layout settings

On the Security tab, under the Native Database Queries header, select the option to Require user approval for new native database queries. Native queries are the user-specified SQL statements passed to data sources as opposed to the queries Power BI generates internally.

Figure 1.9: Security option for Native Database Queries

Optionally, set Show security warning when adding a custom visual to a report as well. Custom visuals can be divided into three categories as follows:

Certified for Power BI (only on AppSource)Uncertified but available in AppSourceThird-party, not available in AppSource

Certified custom visuals have been thoroughly tested for safety to ensure that the visuals do not access external services or resources and that they follow secure coding practices. Uncertified visuals available in AppSource have been through a validation process, though there is no guarantee that all code paths have been tested or that no external services or resources are accessed. Third-party visuals not available in AppSource should be used with caution, and it is recommended that organizations establish policies and procedures regarding their use.

On the Privacy tab, under the Privacy Levels heading, configure the privacy levels for all data sources and enable the option to Always combine data according to your Privacy Level settings for each source. Use the Learn more about Privacy Levels link for details on these settings. 

Figure 1.10: Global Privacy Level options

Use the Diagnostics tab to provide version information and diagnostics options if there is a need to troubleshoot a particular problem with Power BI Desktop. The Enable tracing option under the Diagnostic Options header writes out detailed trace event data to the local hard drive and thus should only be activated for complex troubleshooting scenarios.

Figure 1.11: Diagnostics Options in Power BI Desktop

Use the Preview features tab to enable preview features for evaluation purposes. The options under Preview features change with new versions as some previous options become generally available and new preview features are introduced. The monthly Power BI Desktop update video and blog post (see https://powerbi.microsoft.com/en-us/blog/) provide details and examples of these new features.Usually a restart of the Power BI Desktop application is required once a new preview option has been activated.

Figure 1.12: Preview features available with the February 2021 release of Power BI Desktop

On the Data Load tab under CURRENT FILE, more experienced users should disable all of the options under the Type Detection and Relationships headings; these model design decisions should be implemented explicitly by the Power BI developer with knowledge of the source data.

Also note that you can disable Auto date/time here on an individual file basis if you did not disable this option at a GLOBAL level.

Figure 1.13: Current File Data Load Options

There's more…

As a modern cloud and service-oriented analytics platform, Power BI delivers new and improved features across its toolset on a monthly basis. These scheduled releases and updates for Power BI Desktop, the Power BI service, the on-premises data gateway, Power BI mobile applications, and more reflect customer feedback, industry trends, and the Power BI team's internal ideas and product roadmap.

BI professionals responsible for developing and managing Power BI content can both stay informed of these updates as well as review detailed documentation and guidance on implementing the features via the Microsoft Power BI blog (http://bit.ly/20bcQb4), Power BI documentation (http://bit.ly/2o22qi4), and the Power BI Community (http:/bit.ly/2mqiuxP).

Figure 1.14: Blog post and supporting video for February 2021 update to Power BI Desktop

The Power BI Community portal provides a robust, searchable hub of information across common topics as well as an active, moderated forum of user experiences and troubleshooting. The community also maintains its own blog featuring examples and use cases from top community contributors, and links to local Power BI User Groups (PUGs), relevant events such as Microsoft Business Applications Summit (MBAS), along with various galleries such as a Themes Gallery, Data Stories Gallery, R Script Showcase, and Quick Measures Gallery.

The Power BI Ideas forum (https://ideas.powerbi.com) is a valuable source for identifying requested features and enhancements, and their status relative to future releases. Ideas are provided a status, such as "Planned," "Under Review," or "Needs Votes." The search functionality allows for filtering by these status details. Filtering for Planned ideas, particularly those with higher community vote counts, provides a sense of impactful updates to be released over a longer time horizon.

See also

For additional information on topics covered in this recipe, refer to the following links:

Power BI Security white paper: http://bit.ly/22NHzRSData source privacy levels: http://bit.ly/2nC0LmxPower BI Auto date/time: http://bit.ly/3bH59cnChange settings for Power BI reports: http://bit.ly/2OP8m0F

Creating an On-Premises Data Gateway

The Microsoft on-premises data gateway (or simply gateway) is a Windows service that runs in on-premises environments or in infrastructure-as-a-service (IaaS) data sources running in the cloud, such as virtual machines running SQL Server databases. The sole purpose of the gateway is to support secure (encrypted) and efficient data transfer between on-premises and IaaS data sources and Microsoft Azure services such as Power BI, Power Apps, Power Automate, and Azure Logic Apps, via an outbound connection to Azure Service Bus. It is important to note that if all data sources used in Power BI reports are in the cloud (and so not on-premises) and accessible by the Power BI service, then the Microsoft on-premises data gateway is not required, as the Power BI service will use native cloud gateways to access native cloud data sources; this could be something such as Azure SQL Database or Azure SQL Managed Instances.

Once installed, a gateway can be used to schedule data refreshes of imported Power BI datasets, to support Power BI reports and dashboards built with DirectQuery, plus live connections to Analysis Services databases.

A single on-premises data gateway can support the refresh and query activity for multiple data sources, and permission to use the gateway can be shared with multiple users. Currently, the gateway supports all common data sources via scheduled imports, including Open Database Connectivity (ODBC) connections, and many of the most common sources via Live Connection and DirectQuery.

Getting ready

The hardware resources required by the gateway vary based on the type of connection (import versus live connection), the usage of the reports, and dashboards in the Power BI service, and the proportion of data volume handled by the gateway versus the on-premises source systems. It is recommended to start with 8-core CPUs, 8 GB of RAM server, and Windows 2012 R2 or later for the operating system. This machine cannot be a domain controller, and to maintain the availability of Power BI content, the gateway server should always be on and connected to the internet.

Another top consideration for the gateway is the location of the gateway server in relation to the Power BI tenant and the data sources to be supported by the gateway. For example, if a SQL Server database is the primary gateway source and it runs on a server in the Western United States, and the Power BI Tenant for the organization is in the West US Azure region, then the gateway should be installed on a server or potentially an Azure virtual machine (VM) in the West US Azure region or a location in the Western United States.

Based on an analysis of current and projected workloads, the gateway resources can be scaled up or down and, optionally, additional gateways can be installed on separate servers to distribute the overall Power BI refresh and query deployment workload.

For example, one gateway server can be dedicated to scheduled refresh/import workloads, thus isolating this activity from a separate gateway server responsible for DirectQuery and Live Connection queries.

The gateway does not require inbound ports to be opened and defaults to HTTPS but can be forced to use TCP. For the default communication mode, it is recommended to whitelist the IP addresses in your data region in your firewall. This list is updated weekly and is available via the Microsoft Azure Datacenter IP list (http://bit.ly/2oeAQyd).

How to create the Gateway

The gateway has two modes, the Standard mode and the Personal mode. The Personal mode is intended for personal use, and thus cannot be shared among users within an enterprise. We will focus on the Standard mode, as the installation and configuration are essentially the same for the Personal mode:

Download the latest Microsoft on-premises data gateway from https://powerbi.microsoft.com/downloads/

Figure 1.15: Download the Microsoft on-premises data gateway

Select the Download standard mode link.Once downloaded, choose Open file to run GatewayInstall.exe.

Figure 1.16: GatewayInstall.exe

Choose the file directory for the installation, accept the terms of use and privacy agreement, and then click the Install button.Sign in to the Power BI service to register the gateway:

Figure 1.17: Registering the gateway

On the next screen after signing in, choose to Register a new gateway on this computer and then click the Next button.Enter a user-friendly name for the gateway and a recovery key and then click the Configure button.

Figure 1.18: Final configuration information for the gateway

With the gateway installed and registered, data sources, gateway admins, and authorized data source users can be added to the Power BI service. A Manage Gateways option will be available under the gear icon in the Power BI service. See the Configuring On-Premises Gateway Data Connectionsrecipe of Chapter 12, Deploying and Distributing Power BI Content, for details on this process.

Figure 1.19: Successfully installed gateway

How it works

As new versions of the gateway become available, a notification is made available in the Status tab of the on-premises data gateway UI, as per Figure 1.19. The Power BI gateway team recommends that updates should be installed as they become available.

The Standard mode on-premises data gateway, rather than the personal mode gateway, is required for the DirectQuery datasets created in this book and the use of other Azure services in the Microsoft business application platform.

The Power BI service uses read-only connections to on-premises sources, but the other services (for example, Power Apps) can use the gateway to write, update, and delete these sources.

The recovery key is used to generate both a symmetric and an asymmetric key, which encrypts data source credentials and stores them in the cloud. The credentials area is only decrypted by the gateway machine in response to a refresh or query request. The recovery key will be needed in the following scenarios:

Migrating a gateway and its configured data sources to a different machine.Restoring a gateway to run the service under a different domain account or restoring a gateway from a machine that has crashed.Taking over ownership of an existing gateway from an existing gateway administrator.Adding a gateway to an existing cluster.

It is important that the recovery key is stored in a secure location accessible to the BI/IT organization. Additionally, more than one user should be assigned as a gateway administrator in the Power BI service to provide redundancy.

There's more…

The final configuration screen for the Microsoft on-premises data gateway shown in Figure 1.18 provides several advanced options, including the following:

Add to an existing gateway clusterChange regionProvide relay details

Gateway clusters remove single points of failure for on-premises data access. Since only a single standard gateway can be installed on a computer, each additional gateway cluster member must be installed on a different computer. If the primary gateway is not available, data refresh requests are routed to other gateway cluster members. When using gateway clusters, it is important that all gateway cluster members run the same gateway version and that offline gateway members are removed or disabled—offline gateway members will negatively impact performance.

By default, the gateway is installed in the same Azure region as your Power BI tenant in the Power BI service. In almost all cases, this setting should not be changed. However, with multi-geo support in Power BI Premium, it may be necessary to install gateways in different Azure regions. The Change Region option provides this flexibility.

Azure relays are automatically provisioned for installed gateways at the time of installation. However, the Provide relay details option allows you to provide your own relay details if you wish to associate the relay with your Azure subscription and manage the sender and listener keys for the relay. This is a very advanced option and you should fully understand Azure relays and what you are trying to accomplish before attempting this configuration. It is important to note that only WCF relays with NetTcp are supported for this feature.

See also

Details of configuring and managing data sources through the on-premises gateway are covered in Chapter 12, Deploying and Distributing Power BI ContentSet the Azure relay for on-premises data gateway: http://bit.ly/3rMJMvP

Installing Analyze in Excel from the Power BI Service

Excel-based data analysis and reporting artifacts, such as PivotTables, charts, and cell range formula expressions with custom formatting, remain pervasive in organizations. Although a significant portion of this content and its supporting processes can be migrated to Power BI, and despite the many additional features and benefits this migration could provide, Power BI is not intended as a replacement for all Excel-based reporting. Organizations, and in particular those departments that use Excel extensively (such as Finance and Accounting), may prefer to leverage these existing assets and quickly derive value from Power BI by both deploying Excel content to Power BI and analyzing Power BI-hosted data from within Excel.

The Power BI service's Analyze in Excel feature replaces the deprecated Microsoft Power BI Publisher for Excel. The Analyze in Excel feature allows you to use Power BI datasets in Excel and use Excel features like PivotTables, charts, and slicers to interact with the datasets.

Getting ready

To get ready to install and use the Power BI service's Analyze in Excel feature, make sure that Microsoft Excel is installed on your computer and then sign in to the Power BI service.

How to install Analyze

In the upper-right corner of any Power BI service page, click the ellipsis and then choose Download and then Analyze in Excel updates.

Figure 1.20: Downloading Analyze in Excel

On the splash screen that appears, choose the Download button.Once the file downloads, choose the Open file link or run the file from your Downloads folder.

Figure 1.21: Analyze in Excel installer

The installation wizard for Microsoft Analysis Services OLE DB Provider Setup launches. Click the Next button.Accept the licensing terms and click the Next button.Click the Install button to begin the installation.Once the installation finishes, click the Finish button.

Figure 1.22: Successful installation of the Analyze in Excel feature

How it works

Now that the Microsoft Analysis Services OLE DB provider is installed locally on your computer, you can use the Analyze in Excel feature for reports. To use Analyze in Excel, follow these steps:

Click on any workspace to see the dashboards, reports, and datasets available in that workspace.Hover over a report or dataset, click the "more" menu (three vertical dots), and then choose Analyze in Excel.

Figure 1.23: Analyze in Excel

If you receive a splash screen informing you that you need some Excel updates, click the I've already installed these updates link.The Power BI service creates and downloads an Excel file that is designed and structured for use with Analyze in Excel. The name of the Excel file will match the name of the Power BI report or dataset from which the Excel file was created.Open the Excel file using the Open file link, or open the file from your Downloads folder.Make sure to click the Enable Editing button in Excel, as the file will open in PROTECTED VIEW.Finally, click the Enable Content button to activate external data connections.

The Excel file contains a single page/tab with the same name as the report or dataset from which the report/dataset was created. This page contains a PivotTable called PivotTable1 that is tied to the Power BI dataset in the Power BI service. Clicking on the PivotTable presents the dataset tables, columns, and measures in the PivotTable Fields pane.

There's more…

Depending on the Office license, users can also connect to Power BI datasets from the Insert PivotTable option and from Data - Get Data experience in Excel.

Table 1.1 lists blogs that contain many articles and examples on Power BI's tools and features.

Blogger(s)

Blog

URL

Michael Carlo Seth Bauer

PowerBI.Tips

http://powerbi.tips

Chris Webb

Crossjoin

https://blog.crossjoin.co.uk

Rob Collie and others

P3 Adaptive

https://powerpivotpro.com

Alberto Ferrari Marco Russo

SQL BI

http://www.sqlbi.com

Kasper De Jonge

Kasper On BI

https://www.kasperonbi.com

Matt Allington

ExceleratorBI

http://exceleratorbi.com.au/blog

Ruth Martinez

Curbal

https://curbal.com/blog

Reza Rad

RADACAD

http://radacad.com/blog

Imke Feldman

The BIccountant

http://www.thebiccountant.com

Brett Powell

Insight Quest

https://insightsquest.com

Gilbert Quevauvilliers

Fourmoo

https://www.fourmoo.com/blog

Tom Martens

Mincing Data

https://www.minceddata.info/blog

Nicky van Vroenhoven

Power BI, Power Platform, Data Platform

https://www.nickyvv.com/

Debbie Edwards

Debbie's Microsoft Power BI, SQL and Azure Blog

http://bit.ly/3eQsb2G

Zoe Douglas

DataZoe

https://www.datazoepowerbi.com/blog

Ibarrau

LaDataWeb (Spanish)

https://blog.ladataweb.com.ar/

Various

Power BI Community Blog

http://bit.ly/3qIoDl9

Various

Power BI Weekly

https://powerbiweekly.info/

Table 1.1: A list of blogs that detail Power BI features and tools, with examples on how to use them

With the exception of Kasper On BI, all of these blogs are from non-Microsoft employees and thus do not necessarily reflect the views of Microsoft or recommended practices with its products. Additionally, several of these blogs are not exclusive to Power BI; they may also include coverage of other MSBI, Azure, SQL Server, and Office 365 tools and services.

An additional resource for learning and deploying Power BI is Adam Saxton's and Patrick LeBlanc's Guy in a Cube video channel (http://bit.ly/2o2lRqU). These videos feature concise, hands-on reviews and resolutions to common issues and scenarios. They also have high-level summaries of recent Power BI updates and releases. As members of Microsoft, Adam and Patrick can incorporate specific guidance from Microsoft product and technical teams, and they regularly identify recent blog posts from the wider Power BI community. It is highly recommended that you subscribe to this channel so that you are always aware of new posts!

See also

Analyze in Excel: http://bit.ly/3bIMSeMAnalyze in Excel – the Advanced Method: https://bit.ly/3bLbpzG

Installing and Configuring Additional Tools

Power BI professionals responsible for the development of semantic models (datasets) routinely utilize additional tools beyond Power BI Desktop to create and manage their models. For example, they use Tabular Editor to quickly implement changes to measures or add new objects, such as perspectives and calculation groups. Likewise, the ALM Toolkit is routinely used to deploy incremental and metadata-only changes to Power BI.

Another such tool is DAX Studio, a third-party tool used to query data models, edit and format code, browse the structure and metadata of data models, and analyze the performance and execution characteristics of DAX queries. For larger and more complex data models and expressions, as well as projects involving multiple models, DAX Studio becomes an essential supplement to the development and performance-tuning processes.

DAX (Data Analysis Expressions) is the "language of Power BI," as it is used to create the measures and queries visualized in Power BI reports and dashboards. Power BI generates and submits DAX queries to the source data model based on the structure of the visualization, user selections, and filters—just as other tools such as Excel generate MDX queries based on the selections and structure of pivot tables and slicers from workbooks. DAX expressions are also used to define security roles and can optionally be used to create columns and tables in data models based on other tables and columns in the model, which can be refreshed at processing time and used by measures and queries. DAX serves the same function in Azure Analysis Services (AAS) and SQL Server Analysis Services (SSAS) tabular models as well as Power Pivot for Excel models, it's essential that BI professionals have a robust tool for developing and analyzing DAX code and the data models containing these expressions.

How to Install and Configure Additional Tools

Download the latest version from the DAX Studio website: https://daxstudio.org/downloads/. The installer version is a single file that provides a wizard-based installation and setup, while the portable version is a ZIP file that does not provide a wizard-based installation.Use the Open file link or run the file from your Downloads folder.

Figure 1.24: DAX Studio installation file

Once the installation starts, select the option to Install for all users (recommended).Accept the license agreement and click the Next button.Choose a folder path to install the tool and click the Next button.Choose whether the DAX Studio add-in for Excel will also be installed. Click the Next button. Note that:The add-in for Excel is required to connect to Power Pivot for Excel data models.Additionally, when DAX Studio is opened from Excel, query results can be exported directly to Excel tables.Select the Start menu folder (the default is DAX Studio) and then click the Next button.Check the box to Create a desktop shortcut and then click the Next button.Finally, click the Install button.

Figure 1.25: Successful installation of DAX Studio

How it works

Upon full installation, including the add-in for Excel, a DAX Studio icon will appear on the Add-Ins tab in the Excel ribbon, like the one shown in Figure 1.26.

Figure 1.26: DAX Studio in the Add-Ins ribbon in Excel

The full installation with the Excel add-in is recommended, as this enables direct output of DAX query results to Excel workbook tables and is required for connecting to Power Pivot data models. For Power Pivot to be available, you must enable the Microsoft Power Pivot for Excel COM Add-in using the File menu in Excel, and then choose Options. Click on the Add-Ins tab. Select COM Add-ins from the drop-down control at the bottom of the dialog box and then select the Go… button. The DAX Studio add-in can be deactivated using this same interface. To demonstrate how this works in Excel, follow these steps:

Open an Excel workbook.Open a Power BI Desktop file.From the Add-Ins tab of the Excel toolbar, activate DAX Studio.

Figure 1.27: The DAX Studio add-in for the Excel Connect dialog

For now, click the Cancel button and then close the Excel workbook.

DAX Studio can also be used as a standalone application outside of Excel. The standalone application provides the same functionality as the Excel add-in, excluding connectivity to Power Pivot for Excel data models and Excel output options. To demonstrate this, follow these steps:

Launch the DAX Studio standalone Windows application.Connect to a Power BI Desktop file or SSAS tabular instance.

The Advanced Options settings of the Connect dialog box allow you to control exactly how DAX Studio connects to the model, such as the ability to specify a security role, effective user name identity, and locale when defining connections to data models and when analyzing trace events associated with DirectQuery data models (that is, the SQL statements generated and passed to sources).

Figure 1.28: DAX Studio advanced connection options

There's more…

There are two additional tools that are useful for Power BI. Namely:

ALM ToolkitTabular Editor

The ALM Toolkit is a third-party tool from MAQ Software that provides advanced features such as Power BI dataset comparison, code merging, partial deployments and bug fixes, source control integration for dataset metadata, and definition reuse between tabular models. To download and install the ALM Toolkit, follow these steps:

In a browser, navigate to http://alm-toolkit.com.Near the top of the page, click the DOWNLOAD LATEST VERSION button.Use the Open file link to launch AlmToolkitSetup.msi or open the file from your Downloads folder.

Figure 1.29: ALM Toolkit installer

Click the Next button on the initial installation screen.Accept the license agreement and click the Next button.Choose a Folder path and click the Next button.On the final installation screen, click the Next button to start the installation.

Figure 1.30: Successfully installed ALM Toolkit

The Power BI ALM Toolkit can now be launched from the Windows Start menu.

Another powerful tool is Tabular Editor. At the time of this book being published, Tabular Editor comes in two versions, the free, open source version 2 and the commercial version 3. Tabular Editor is an alternative to SQL Server Data Tools (SSDT) for authoring and editing tabular models for Analysis Services. Tabular Editor provides a hierarchical view of the objects in your tabular model metadata, such as columns, measures, and hierarchies. Tabular Editor integrates with Power BI Desktop, allowing batch changes to DAX measures and enabling advanced capabilities such as calculation groups and perspectives. Finally, Tabular Editor also enables offline editing capabilities by allowing you to open the tabular model directly from Model.bim files. To install and use Tabular Editor, follow these steps:

Download the latest Tabular Editor from here: https://bit.ly/3bJFBvl.Scroll to the bottom of the page and click the link for TabularEditor.Installer.msi.Once the file downloads, use the Open file link or open the file from your Downloads folder.

Figure 1.31: Tabular Editor installer

Click the Next button on the initial installation screen.Accept the license agreement and click the Next button.Choose a Folder path and click the Next button.Check the boxes Create Desktop shortcut and Create Program Menu shortcut, and then click the Next button.On the final installation screen, click the Next button to start the installation.

Figure 1.32: Successfully installed Tabular Editor

Tabular Editor can now be launched from the Windows Start menu.

See also

DAX Studio tutorials and documentation: https://daxstudio.orgALM Toolkit documentation: http://alm-toolkit.com/HowToUseTabular Editor documentation: https://docs.tabulareditor.com/

Conclusion

In this chapter, we walked through the installation and configuration of the primary tools that BI professionals utilize to design and develop Power BI content, including official Microsoft tools such as Power BI Desktop, the On-premises data gateway, and Analyze in Excel, as well as third-party tools such as DAX Studio, the ALM Toolkit, and Tabular Editor. These tools, coupled with the Power BI service, are the primary resources needed by BI professionals to be productive with Power BI. Later chapters of this book explore the use of these tools in developing and enhancing Power BI solutions.

2

Accessing and Retrieving Data

Power BI Desktop contains a rich set of connectors and transformation capabilities that support the integration and enhancement of data from many different sources. These features are all driven by a powerful functional language and query engine, M, which leverages source system resources when possible and can greatly extend the scope and robustness of the data retrieval process beyond what's possible via the standard query editor interface alone. As with almost all BI projects, the design and development of the data access and retrieval process has significant implications for the analytical value, scalability, and sustainability of the overall Power BI solution.

In this chapter, we dive into Power BI Desktop's Get Data experience and walk through the process of establishing and managing data source connections and queries. Examples are provided of using the Power Query Editor interface and the M language directly, to construct and refine queries to meet common data transformation and cleansing needs. In practice and as per the examples, a combination of both tools is recommended to aid the query development process.

A full explanation of the M language and its implementation in Power BI is outside the scope of this book, but additional resources and documentation are included in the sections titled There's more... and See also.

The recipes included in this chapter are as follows:

Viewing and Analyzing M FunctionsManaging Queries and Data SourcesUsing DirectQueryImporting DataApplying Multiple FiltersSelecting and Renaming ColumnsTransforming and Cleansing Source DataCreating Custom ColumnsCombining and Merging QueriesSelecting Column Data TypesVisualizing the M LibraryProfile Source DataDiagnosing Queries

Technical Requirements

The following are required to complete the recipes in this chapter:

Power BI DesktopSQL Server 2019 or newer with the AdventureWorksDW2019 database installed. This database and instructions for installing it are available here: http://bit.ly/2OVQfG7

Viewing and Analyzing M Functions

Every time you click on a button to connect to any of Power BI Desktop's supported data sources or apply any transformation to a data source object, such as changing a column's data type, one or multiple M expressions are created reflecting your choices. These M expressions are automatically written to dedicated M documents and, if saved, are stored within the Power BI Desktop file as Queries. M is a functional programming language like F#, and it is important that Power BI developers become familiar with analyzing, understanding, and later, writing and enhancing the M code that supports their queries.

Getting ready

To prepare for this recipe, we will first build a query through the user interface that connects to the AdventureWorksDW2019 SQL Server database, retrieves the DimGeography table, and then filters this table to a single country, such as the United States:

Open Power BI Desktop and click on Get Data from the Home tab of the ribbon. Select SQL Server from the list of database sources. For future reference, if the data source is not listed in Common data sources, more data sources are available by clicking More… at the bottom of the list.A dialog window is displayed asking for connectivity information. Ensure that Data Connectivity mode is set to Import. Enter the name of your SQL server as well as the AdventureWorksDW2019 database. In Figure 2.1, my SQL server is installed locally and running under the instance MSSQLSERVERDEV. Thus, I set the server to be localhost\MSSQLSERVERDEV to specify both the server (localhost) and the instance. If you leave the Database field blank, this will simply result in an extra navigation step to select the desired database.

Figure 2.1: SQL Server Get Data dialog

If this is the first time connecting to this database from Power BI, you may be prompted for some credentials. In addition, you may also be warned that an encrypted connection cannot be made to the server. Simply enter the correct credentials for connecting and click the Connect button. For the encryption warning, simply click the OK button to continue.A navigation window will appear, with the different objects and schemas of the database. Select the DimGeography table from the Navigator window and click the Transform Data button.The Power Query Editor launches in a new window with a query called DimGeography; preview data from that table is displayed in the center of the window. In the Power Query Editor window, use the scroll bar at the bottom of the central display area to find the column called EnglishCountryRegionName. You can also select a column and then click Go to Column in the ribbon of the View menu to search for and navigate to a column quickly. Click the small button in the column header next to this column to display a sorting and filtering drop-down menu.

Uncheck the (Select All) option to deselect all values and then check the box next to a country, such as the United States, before clicking the OK button.

Figure 2.2: Filtering for United States only in the Query Editor

Note that the button for the EnglishCountryRegionName column changes to display a funnel icon. Also notice that, in the Query Settings pane on the right side of the window, a new option under APPLIED STEPS has appeared called Filtered Rows.

Figure 2.3: The Query Settings pane in the Query Editor

How to View and Analyze M Functions

There are two methods for viewing and analyzing the M functions comprising a query; they are as follows:

Formula barAdvanced Editor

The formula bar exposes the M function for the current step only. This formula bar appears just above the column headers for the preview data in the central part of the window. If you do not see this formula bar, click the View tab and check the box next to Formula Bar in the Layout section of the ribbon. All such areas of interest are boxed in red in Figure 2.4.

Figure 2.4: The Power Query Editor formula bar

When the Source step is selected under APPLIED STEPS in the Query Settings pane, as seen in Figure 2.3, we see the connection information specified on the initial dialog after selecting Get Data and then SQL Server. The M function being used is Sql.Database. This function is accepting two parameters: the server name, localhost\MSSQLSERVERDEV, and the database name, AdventureWorksDW2019. Clicking on other steps under APPLIED STEPS exposes the formulas for those steps, which are technically individual M expressions.

The formula bar is useful to quickly understand the M code behind a particular query step. However, it is more convenient and often essential to view and edit all the expressions in a centralized window. This is the purpose of the Advanced Editor.To launch the Advanced Editor, follow these steps:

Click on the Home tab and then select Advanced Editor from the Query section of the ribbon, as shown in Figure 2.5. Alternatively, the Advanced Editor can also be accessed from the View tab, shown in Figure 2.4.

Figure 2.5: Advanced Editor on the Home tab of the Query Editor

The Advanced Editor dialog is displayed, exposing all M functions and comments that comprise the query. The M code can be directly edited from within this dialog.

Figure 2.6: The Advanced Editor view of the DimGeography query

As shown in Figure 2.6, using the Advanced Editor will mean that all of the Power Query code that comprises the query can be viewed in one place.

How it works

The majority of queries created for Power BI follow the let...in structure, as per this recipe. Within the let block, there are multiple steps with dependencies among those steps. For example, the second step, dbo_DimGeography, references the previous step,