Preface

Microsoft Power BI Complete Reference gets you started with business intelligence by showing you how to install the Power BI toolset, design effective data models, and build basic dashboards and visualizations that make your data come to life.

In this Learning Path, you will learn to create powerful interactive reports by visualizing your data and learn visualization styles, tips and tricks to bring your data to life. You will be able to administer your organization's Power BI environment to create and share dashboards. You will also be able to streamline deployment by implementing security and regular data refreshes.

Next, you will delve deeper into the nuances of Power BI and handling projects. You will get you acquainted with planning a Power BI project, development, and distribution of content, and deployment. You will learn to connect and extract data from various sources to create robust datasets, reports, and dashboards. Additionally, you will learn how to format reports and apply custom visuals, animation and analytics to further refine your data.

By the end of this Learning Path, you will learn to implement the various Power BI tools such as on-premises gateway together along with staging and securely distributing content via apps.

This Learning Path is packaged up keeping your journey in mind. The curator of this Learning Path has combined some of the best that Packt has to offer in one complete package. It includes content from the following Packt products:

• Microsoft Power BI Quick Start Guide by Devin Knight et al.
• Mastering Microsoft Power BI by Brett Powell

Who this book is for

This Learning Path is for those who want to learn and use the Power BI features to extract maximum information and make intelligent decisions that boost their business. If you have a basic understanding of BI concepts and want to learn how to apply them using Microsoft Power BI, this Learning Path is for you. It consists of real-world examples on Power BI and goes deep into the technical issues, covers additional protocols, and much more. 

What this book covers

Chapter 1, Getting Started with Importing Data Options, begins by getting the audience oriented with the Power BI Desktop. Next, they will learn how to connect to various common data sources in Power BI. Once a data source is chosen, the options within will be explored, including the choice between data import, direct query, and live connection.

Chapter 2, Data Transformation Strategies, explores the capabilities of the Power Query Editor inside the Power BI Desktop. Using this Power BI Query Editor, the reader will first learn how to do basic transformations, and they will quickly learn more advanced data-cleansing practices. By the end of this chapter, the audience will know how to combine queries, use parameters, and read and write basic M queries.

Chapter 3, Building the Data Model, discusses one of the most critical parts of building a successful Power BI solution—designing an effective data model. In this chapter, readers will learn that while designing a data model, they are really setting themselves up for success when it comes to building reports. Specifically, this chapter will teach the audience how to establish relationships between tables, how to deal with complex relationship designs, and how to implement usability enhancements for the report consumers.

Chapter 4, Leveraging DAX, teaches that the Data Analysis Expression (DAX) language within Power BI is critical to building data models that are valuable to data consumers. While DAX may be intimidating at first, readers will quickly learn that its roots come from the Excel formula engine. This can be helpful at first, but as you find the need to develop more and more complex calculations, readers will learn that having a background in Excel formulas will only take them so far. This chapter will start with an understanding of basic DAX concepts but quickly accelerate into more complex ideas, such as Time Intelligence and Filter Context.

Chapter 5, Visualizing Data, describes how to take a finely tuned data model and build reports that properly deliver a message that clearly and concisely tells a story about the data.

Chapter 6, Using a Cloud Deployment with the Power BI Service, examines deploying your solution to the Power BI Service to share what you've developed with your organization. Once deployed, you can build dashboards, share them with others, and schedule data refreshes. This chapter will cover the essential skills a BI professional would need to know to top off a Power BI solution they have developed.

Chapter 7, Planning Power BI Projects, discusses alternative deployment modes for Power BI, team and project roles, and licensing. Additionally, an example project template and its corresponding planning and dataset design processes are described. 

Chapter 8, Connecting to Sources and Transforming Data with M, depicts the data access layer supporting a Power BI dataset, including data sources and fact and dimension table queries. Concepts of the Power Query M language, such as query folding and parameters, are explained and examples of custom M queries involving conditional and dynamic logic are given.

Chapter 9, Designing Import and DirectQuery Data Models, reviews the components of the data model layer and design techniques in support of usability, performance, and other objectives. 

Chapter 10, Developing DAX Measures and Security Roles, covers the implementation of analysis expressions reflecting business definitions and common analysis requirements. Primary DAX functions, concepts, and use cases such as date intelligence, row-level security roles, and performance testing are examined.

Chapter 11, Creating and Formatting Power BI Reports, describes a report planning process, data visualization practices, and report design fundamentals, including visual selection and filter scopes. Top report development features, such as slicer visuals, tooltips, and conditional formatting are also reviewed.

Chapter 12, Applying Custom Visuals, Animation, and Analytics, examines powerful interactive and analytical features, including drillthrough report pages, bookmarks, the Analytics pane, ArcGIS Maps, and the waterfall charts. Additionally, examples of custom visuals, such as the Power KPI, and the capabilities of animation to support data storytelling are provided.  

Chapter 13, Designing Power BI Dashboards and Architectures, provides guidance on visual selection, layout, and supporting tiles to drive effective dashboards. Alternative multi-dashboard architectures, such as an organizational dashboard architecture, are reviewed, is the configuration of dashboard tiles and mobile-optimized dashboards.

Chapter 14, Managing Application Workspaces and Content, features the role and administration of app workspaces in the context of Power BI solutions and staged deployments. Additionally, the Power BI REST API, content management features, and practices are reviewed, including field descriptions and version history. 

Chapter 15, Managing the On-Premises Data Gateway, covers top gateway planning considerations, including alternative gateway architectures, workloads, and hardware requirements. Gateway administration processes and tools are described, such as the manage gateways portal, gateway log files, and PowerShell Gateway commands. 

Chapter 16, Deploying the Power BI Report Server, contrasts the Power BI Report Server with the Power BI cloud service and provides guidance on deployment topics such as licensing, reference topology, configuration, administration, and upgrade cycles.    

Chapter 17, Creating Power BI Apps and Content Distribution, walks through the process of publishing and updating apps for groups of users. Additionally, other common distribution methods are covered, such as the sharing of reports and dashboards, email subscriptions, data-alert-driven emails, and embedding Power BI content in SharePoint Online.

Chapter 18, Administering Power BI for an Organization, highlights data governance for self-service and corporate BI, Azure Active Directory features such as Conditional Access Policies, and the Power BI admin portal. Details are provided about configuring Power BI service tenant settings, managing Power BI Premium capacities, and the tools available to monitor Power BI activities.   

Chapter 19, Scaling with Premium and Analysis Services, reviews the capabilities of Power BI Premium and alternative methods for allocating premium capacity. Additionally, Power BI datasets are contrasted with Analysis Services models, Azure Analysis Services is contrasted with SQL Server Analysis Services, and the migration of a Power BI dataset to an Analysis Services model is described.  

To get the most out of this book

A Power BI Pro license and access to the Power BI service is necessary to follow many of the topics and examples in this book. The assignment of the Power BI Service Administrator role within the Office 365 admin center, as well as administrative access to an On-premises data gateway, would also be helpful for the second half of this book. It's assumed that readers are familiar with the main user interfaces of Power BI Desktop and have some background in business intelligence or information technology.   

The primary data source for the examples in this book was the AdventureWorks data warehouse sample database for SQL Server 2016 CTP3. A SQL Server 2017 Developer Edition database engine instance was used to host the sample database. For the import mode dataset, an Excel workbook stored the sales plan data. For the DirectQuery dataset, the sales plan data was stored in the sample SQL Server database.

Note

The AdventureWorksDW2016CTP3 sample database can be downloaded from the following URL: https://www.microsoft.com/en-us/download/details.aspx?id=49502.

 

Editions of SQL Server 2017 are available for download from the following URL: https://www.microsoft.com/en-us/sql-server/sql-server-downloads.

The Power BI Desktop files and specific queries and scripts utilized in the book are included in the code bundle. However, the source data and database are not included in the code bundle. Additionally, the database used by the book contains objects not included in the downloadable sample database, such as SQL views for each fact and dimension table. Therefore, even with access to a SQL Server 2017 database engine instance and the sample AdventureWorks data warehouse database, the examples in the book cannot be completely reproduced.

Download the example code files

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

You can download the code files by following these steps:

1. Log in or register at www.packtpub.com.
2. Select the SUPPORT tab.
3. Click on Code Downloads & Errata.
4. Enter the name of the book in the Search box and follow the onscreen instructions.

 

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

• WinRAR/7-Zip for Windows
• Zipeg/iZip/UnRarX for Mac
• 7-Zip/PeaZip for Linux

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

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

Conventions used

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

CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "This function is used by the Web Data Connector (WDC) (Get Data | Web) to return the contents of an HTML web page as a table, as shown in the following M Query:"

A block of code is set as follows:

// Retrieve table of data access M functions and their descriptions
let
  Source = Web.Page(Web.Contents("https://msdn.microsoft.com/en-US/library/mt296615.aspx")),
  PageToTable = Source{0}[Data],
  ChangedType = Table.TransformColumnTypes(PageToTable,
 {{"Function", type text}, {"Description", type text}})
in
    ChangedType

Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "The Data Gateway item from the download menu in the preceding image currently links to a Power BI Gateway page with a large Download Gateway button at the top."

 

Note

Warnings or important notes appear like this.

Note

Tips and tricks appear like this.

Get in touch

Feedback from our readers is always welcome.

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

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

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

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

Reviews

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

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