Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
Mastering Microsoft Power BI
Mastering Microsoft Power BI

Mastering Microsoft Power BI: Expert techniques for effective data analytics and business intelligence

eBook
€31.99 €35.99
Paperback
€44.99
Subscription
Free Trial
Renews at €18.99p/m

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Mastering Microsoft Power BI

Connecting to Sources and Transforming Data with M

This chapter follows up on the dataset planning process described in the previous chapter by implementing M queries in a new Power BI Desktop file to retrieve the required fact and dimension tables. Parameters and variables are used to access a set of SQL views reflecting the data warehouse tables inside a SQL Server database and the Annual Sales Plan data contained in an Excel workbook. Additional M queries are developed to support relationships between the sales plan and dimension tables and to promote greater usability and manageability of the dataset.

Three examples of implementing data transformations and logic within M queries, such as the creation of a dynamic customer history segment column, are included. Finally, tools for editing and managing M queries, such as extensions for Visual Studio and Visual Studio Code, are...

Query design per dataset mode

Many common M queries can be written for both import and DirectQuery datasets, but with widely different implications for the source system resources utilized and the performance of the analytical queries from Power BI. It's essential that the mode of the dataset (import or DirectQuery) has been determined in advance of the development of the data access queries and that this decision is reflected in the M queries of the dataset.

The M queries supporting a Power BI dataset import mode should exclude, or possibly split, columns with many unique values, such as a Transaction Number column, as these columns consume relatively high levels of memory. A standard design technique for import mode models is to exclude derived fact table columns with relatively more unique values when these values can be computed via simple DAX measure expressions based...

Data sources

Data source connectivity is one of the strengths of Power BI, due to the vast list of standard data source connectors included in Power BI Desktop, in addition, to support for Open Database Connectivity (ODBC) and Object Linking and Embedding, Database (OLE DB) connections. The breadth of data connectivity options is further bolstered by the ability for developers to create custom Power BI data connectors for a specific application, service, or data source. Custom data connectors, the data retrieval processes created for all data sources for Power BI, and other Microsoft applications are developed with the M language.

Power BI's data connectors are consistently extended and improved with each monthly release of Power BI Desktop. New data sources are commonly added as a preview or beta release feature and previous beta connectors are moved from beta to general...

SQL views

As described in the Dataset planning section of Chapter 1, Planning Power BI Projects, a set of SQL views should be created within the data source and these objects, rather than the database tables, should be accessed by the Power BI dataset. Each fact and dimension table required by the Power BI dataset should have its own SQL view and its own M query within the dataset that references this view. The SQL views should preferably be assigned to a dedicated database schema and identify the dimension or fact table represented as shown in the following screenshot:

Views assigned to BI schema in SQL Server
A common practice is to create a database schema specific to the given dataset being created or to the specific set of reports and dashboards required for a project. However, as suggested in the Data Warehouse Bus Matrix section of Chapter 1, Planning Power BI Projects...

M queries

With the SQL views created, the data sources configured, and the Power BI Desktop environment options applied, the dataset designer can finally develop the data retrieval queries and parameters of the dataset.

Within the Power Query Editor of Power BI Desktop, group folders can be used to organize M queries into common categories such as Data Source Parameters, Staging Queries, Fact table Queries, Dimension Table Queries, and Bridge Table Queries as shown in the following screenshot:

Power Query Editor in Power BI Desktop with group folders

The parameters and queries displayed with a gray font are included in the refresh process of the dataset but not loaded to the data modeling layer. For example, the AdWorksSQLServer query displayed in the preceding image merely exposes the objects of the SQL Server database via the Sql.Database() M function for other queries to reference...

Query folding

Query folding is one of the most powerful and important capabilities of the M language as it translates M expressions into SQL statements that can be executed by the source system. With query folding, M serves as an abstraction layer to implement both common and complex data cleansing and transformation operations while still leveraging source system resources. When implementing any remaining logic or data transformations via M functions, a top priority of the dataset designer is to ensure that these operations are folded to the data source.

In the following M query, a Table.RemoveColumns() M function is applied against the SQL view for the Internet Sales fact table to exclude three columns that are not needed for the dataset:

Power Query Editor: View Native Query

The additional step is translated to a SQL query that simply doesn't select the three columns...

M Query examples

The M query language includes hundreds of functions and several books have been written about to its application. The greater purpose of this chapter is to understand M queries in the context of a corporate Power BI solution that primarily leverages an IT-managed data warehouse. As shown in the examples shared in the M Queries section earlier, the combination of a mature data warehouse and a layer of SQL view objects within this source may eliminate any need for further data transformations. However, Power BI Dataset designers should still be familiar with the fundamentals of M queries and their most common use cases, as it's often necessary to further extend and enhance source data.

The following sections demonstrate three common data transformation scenarios that can be implemented in M. Beyond retrieving the correct results, the M queries also generate...

M editing tools

Power BI Desktop stores the M code for queries created via the Power Query Editor graphical interface or the Advanced Editor within M documents for repeatable execution. Similar to other languages and project types, code editing tools are available to support the development, documentation, and version control of M queries. Dataset designers can use Visual Studio or Visual Studio Code to author and manage the M queries for Power BI and other Microsoft projects. These tools include common development features, such as IntelliSense, syntax highlighting, and integrated source control.

Advanced Editor

In Power BI Desktop, the M code for each query can be accessed from the Advanced Editor window within the Power...

Summary

In this chapter, we've covered all components of the data retrieval process used to support the dataset for this project as described in Chapter 1, Planning Power BI Projects. This includes the layer of SQL views within a database source, source connectivity parameters in Power BI Desktop, and the M queries used to define and load the dimension and fact tables of the dataset. In constructing a data access layer and retrieval process for a dataset, we've also discussed the design considerations relative to import and DirectQuery datasets, Power BI Desktop configuration options, and data source privacy levels. Additionally, we've reviewed core concepts of the M language, including query folding, item access, and data types. Moreover, we've reviewed three examples of efficiently implementing impactful data transformation logic via M queries as well as...

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Master all the dashboarding and reporting features of Microsoft Power BI
  • Combine data from multiple sources, create stunning visualizations and publish your reports across multiple platforms
  • A comprehensive guide with real-world use cases and examples demonstrating how you can get the best out of Microsoft Power BI

Description

This book is intended for business intelligence professionals responsible for the design and development of Power BI content as well as managers, architects and administrators who oversee Power BI projects and deployments. The chapters flow from the planning of a Power BI project through the development and distribution of content to the administration of Power BI for an organization. BI developers will learn how to create sustainable and impactful Power BI datasets, reports, and dashboards. This includes connecting to data sources, shaping and enhancing source data, and developing an analytical data model. Additionally, top report and dashboard design practices are described using features such as Bookmarks and the Power KPI visual. BI managers will learn how Power BI’s tools work together such as with the On-premises data gateway and how content can be staged and securely distributed via Apps. Additionally, both the Power BI Report Server and Power BI Premium are reviewed. By the end of this book, you will be confident in creating effective charts, tables, reports or dashboards for any kind of data using the tools and techniques in Microsoft Power BI.

Who is this book for?

Business Intelligence professionals and existing Power BI users looking to master Power BI for all their data visualization and dashboarding needs will find this book to be useful. While understanding of the basic BI concepts is required, some exposure to Microsoft Power BI will be helpful.

What you will learn

  • Build efficient data retrieval and transformation processes with the Power Query M Language
  • Design scalable, user-friendly DirectQuery and Import Data Models
  • Develop visually rich, immersive, and interactive reports and dashboards
  • Maintain version control and stage deployments across development, test, and production environments
  • Manage and monitor the Power BI Service and the On-premises data gateway
  • Develop a fully on-premise solution with the Power BI Report Server
  • Scale up a Power BI solution via Power BI Premium capacity and migration to Azure Analysis Services or SQL Server Analysis Services

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Mar 29, 2018
Length: 638 pages
Edition : 1st
Language : English
ISBN-13 : 9781788297233
Vendor :
Microsoft
Category :
Languages :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Mar 29, 2018
Length: 638 pages
Edition : 1st
Language : English
ISBN-13 : 9781788297233
Vendor :
Microsoft
Category :
Languages :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
€18.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
€189.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts
€264.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total 132.97
Mastering Microsoft Power BI
€44.99
Microsoft Power BI Quick Start Guide
€37.99
Microsoft Power BI Cookbook
€49.99
Total 132.97 Stars icon

Table of Contents

14 Chapters
Planning Power BI Projects Chevron down icon Chevron up icon
Connecting to Sources and Transforming Data with M Chevron down icon Chevron up icon
Designing Import and DirectQuery Data Models Chevron down icon Chevron up icon
Developing DAX Measures and Security Roles Chevron down icon Chevron up icon
Creating and Formatting Power BI Reports Chevron down icon Chevron up icon
Applying Custom Visuals, Animation, and Analytics Chevron down icon Chevron up icon
Designing Power BI Dashboards and Architectures Chevron down icon Chevron up icon
Managing Application Workspaces and Content Chevron down icon Chevron up icon
Managing the On-Premises Data Gateway Chevron down icon Chevron up icon
Deploying the Power BI Report Server Chevron down icon Chevron up icon
Creating Power BI Apps and Content Distribution Chevron down icon Chevron up icon
Administering Power BI for an Organization Chevron down icon Chevron up icon
Scaling with Premium and Analysis Services Chevron down icon Chevron up icon
Other Books You May Enjoy Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Half star icon Empty star icon 3.2
(21 Ratings)
5 star 38.1%
4 star 14.3%
3 star 4.8%
2 star 14.3%
1 star 28.6%
Filter icon Filter
Top Reviews

Filter reviews by




Georgia Jaunt Sep 18, 2018
Full star icon Full star icon Full star icon Full star icon Full star icon 5
This is one of the most information-dense technical reference books I've read in a long time. By this I mean -- it packs a tremendous amount of useful detail on each page. I'm coming from this as a 20+ year user of database tools, MCSA in SQL Server and someone motivated to really learn/apply Power BI as a consultant. This book really delivers in terms of in-depth explanations and content you can use to create PBI solutions. It goes above and beyond in another dimension as well: the authors very useful digressions on best-practises and real-world advice for things like visualizations and frameworks for managing users' competing needs. This is truly a useful text for someone interested in designing / deploying / managing mid-size corporate installations of BI solutions using Microsoft tools.Caution -- This is not an introductory text. If you are interested in casual use of PBI, there are probably more accessible books out there.It is easy to give this 5 stars on its content -- but would also mention a couple of minor things: - the included code samples are not directly useable. The author cautions about this in the text. Of 3 points raised, this is this the most significant. It would have been nice to be able to eyeball the worked examples on my workstation. - Minor: The index in the back is pretty sparse. For example, you won't find "hierarchy" mentioned anywhere there but have to read through the table of contents to find that section. - Minor: The printing / paper / binding quality is fine -- but I wish Packt would put more thought into typography. It's easier to digest this kind of material when the publisher uses more finesse in use of different sized/numbered heads. As-is, the chapter content just seems to run together.FWIW -- I've previously read: - Teo Lachev's "Applied MS PowerBI" (2nd ed) -- which is also good, particularly for someone first experimenting with Power BI. - Russo & Ferrari "Definitive Guide to Dax" (1st ed) -- the go-to reference on DAX.Aside: I have no affiliation with the author or publisher. I purchased this on Amazon, etc.
Amazon Verified review Amazon
maridee oday May 12, 2018
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I just started this book and already have found it incredibly useful. I relied heavily on Brett's first book in my Power BI work. This new one is wonderful and updated to include the latest features. What I love about Brett's books is that he provides a higher level view than the Microsoft free online education, so you can understand how the different features and methods of doing things interact and compare before diving into the details, which are also provided in a clear and concise way. The information is presented from the ground-up in a logical manner that promotes a solid foundation for learning. In addition, I can easily skim the Table of Contents to find exactly what I need, and it covers every essential Power BI topic. Brett is truly the premier author for books in this field.
Amazon Verified review Amazon
JPH Apr 30, 2019
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Très complet, clair et riche : Une mine de renseignements...
Amazon Verified review Amazon
Brian Hanson Oct 03, 2018
Full star icon Full star icon Full star icon Full star icon Full star icon 5
If you're looking for enterprise-level Power BI deployment, this is the bible. I learned a lot about best practices, and what I already knew was explained very clearly.
Amazon Verified review Amazon
Richard Walker Jan 30, 2019
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I have found this to be a thorough and extremely useful tool to plan the development and scaling of internal and external solutions for PowerBI and Azure AD B2B. This is a great add on to other books written by the likes of Ferrari and Russo.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.