Skip to main content

Über dieses Buch

Analyze your company’s data quickly and easily using Microsoft’s latest tools. Build scalable and robust data models to work from. Learn to clean and combine different data sources effectively. Create compelling visualizations and share them with your colleagues.

Author Dan Clark takes you through each topic using step-by-step activities and plenty of screen shots to help familiarize you with the tools. This second edition includes new material on advanced uses of Power Query, along with the latest user guidance on the evolving Power BI platform. Beginning Power BI is your hands-on guide to quick, reliable, and valuable data insight.

What You Will Learn

Simplify data discovery, association, and cleansing

Create solid analytical data models

Create robust interactive data presentations

Combine analytical and geographic data in map-based visualizations

Publish and share dashboards and reports

Who This Book Is For

Business analysts, database administrators, developers, and other professionals looking to better understand and communicate with data.



Building Models in Power Pivot


Chapter 1. Introducing Power Pivot

The core of Microsoft’s self-service business intelligence (BI) toolset is Power Pivot. It is integrated into both Excel 2016 and Power BI and forms the foundation on top of which you will build your analytical reports and dashboards. This chapter provides you with some background information on why Power Pivot is such an important tool and what makes it perform so well. The chapter also provides you with an overview of the Power Pivot interface in Excel 2016 and gives you with some experience using the different areas of the interface.

Dan Clark

Chapter 2. Importing Data into Power Pivot

One of the first steps in creating the Power Pivot model is importing data. Traditionally, when creating a BI solution based on an OLAP cube, you need to import the data into the data warehouse and then load it into the cube. It can take quite a while to get the data incorporated into the cube and available for your consumption. This is one of the greatest strengths of the Power Pivot model. You can easily and quickly combine data from a variety of sources into your model. The data sources can be from relational databases, text files, web services, and OLAP cubes, just to name a few. This chapter shows you how to incorporate data from a variety of these sources into a Power Pivot model.

Dan Clark

Chapter 3. Data Munging with Power Query

Although Power Pivot provides many types of connections that you can use to query data, there are times when you need to clean and shape it (commonly called data munging) before loading it into the model. This is where Power Query really shines and is a very useful part of your BI arsenal. Power Query provides an easy-to-use interface for discovering and transforming data. It contains tools to clean and shape data such as removing duplicates, replacing values, and grouping data. In addition, it supports a vast array of data sources, both structured and unstructured, such as relational databases, web pages, and Hadoop, just to name a few. Once the data is extracted and transformed, you can then easily load it into a Power Pivot model.

Dan Clark

Chapter 4. Creating the Data Model

Now that you know how to get data into the Power Pivot model, the next step is to understand what makes a good model. This is very important when dealing with data in Power Pivot. A good model will make Power Pivot perform amazingly fast and allow you to analyze the data in new and interesting ways. A bad model will cause Power Pivot to perform very slowly and at worst give misleading results when performing the data analysis.

Dan Clark

Chapter 5. Creating Calculations with DAX

Now that you know how to create a robust data model to base your analysis on, the next step is to add to the model any calculations required to aid your exploration of the data. For example, you may have to translate code values into meaningful descriptions or parse out a string to obtain key information. This is where Data Analysis Expressions (DAX) comes into play. This chapter introduces you to DAX and shows you how to use DAX to create calculated columns to add to the functionality of your model.

Dan Clark

Chapter 6. Creating Measures with DAX

Creating measures in DAX is the most important skill necessary to create solid data models. This chapter covers the common functions used to create measures in the data model. It also covers the important topic of data context and how to alter or override the context when creating measures.

Dan Clark

Chapter 7. Incorporating Time Intelligence

One of the most common types of data analysis is comparing values over time. This chapter shows you how to correctly implement time-based analysis in Power Pivot. It includes setting up a date table and using the various built-in functions for analyzing values to date, comparing values from different periods, and performing semi-additive aggregations.

Dan Clark

Chapter 8. Data Analysis with Pivot Tables and Charts

Once you have the data model created in Power Pivot, you need to create an interface for users to interact with the data model and perform data analysis using the model. You can use several programs to interface with the model depending on the type of analysis taking place; these include Power BI, Performance Point, and Reporting Services. Although all these tools are viable clients to use, one of the best client tools is Excel itself. Excel is a feature-rich environment for creating dashboards using pivot tables and pivot charts. Furthermore, it is very easy to share Excel files with colleagues or host the Excel workbook on SharePoint for increased performance and security. This chapter covers the basics of building an interface for analyzing the data contained in a Power Pivot model using pivot tables and pivot charts in Excel.

Dan Clark

Building Interactive Reports and Dashboards with Power BI Desktop


Chapter 9. Introducing Power BI Desktop

As you have seen in the previous chapters, Excel is an excellent tool for developing analytic solutions. It works quite well for individual analysis and sharing the results among a small group of consumers. But where it is lacking is when you need to share the results of your analysis with a broader audience. This is where the Power BI portal and Power BI Desktop come in to play. The Power BI portal is where you can host, share, and secure interactive dashboards and reports with others. Power BI Desktop is where you create the model and visuals on which the dashboards in the portal are based. The great thing about Power BI Desktop is that it uses the same tools you’ve been using in Excel. It uses Power Query to get, clean, and shape the data. It then uses a model designer similar to Power Pivot to construct a tabular model on top of which you create interactive visuals.

Dan Clark

Chapter 10. Creating Reports with Power BI Desktop

In the last chapter, you saw how to import, clean, and shape data using Power BI Desktop. In addition, you created the data model and augmented it with calculated columns and measures. In this chapter you will investigate some of the common visualizations used to create reports in Power BI Desktop. You will become familiar with how to control visual interactions, along with report filtering. You will build standard visualizations such as column, bar, and pie charts. In addition, you will look at line and scatter charts. Finally, you will investigate how to use maps to analyze data geographically.

Dan Clark

Chapter 11. Publishing Reports and Creating Dashboards in the Power BI Portal

Now that you know how to create reports in Power BI Desktop, it’s time to publish your reports for others to use. In this chapter, you will see how to publish reports created in Power BI Desktop to the Power BI Service (portal). Once the reports are published, you will create dashboards and share them with colleagues. In addition, you will set up an automated data refresh schedule.

Dan Clark

Chapter 12. Creating a Complete Solution

So far in this book, you’ve gained experience working with each of the pieces of Microsoft’s self-service BI toolset. You’ve used Power Query, Power Pivot, Excel, and the Power BI Desktop. This chapter provides you with several use cases to solidify the concepts of the previous chapters. By working through these use cases, you will gauge which areas you have mastered and which you need to spend more time studying. Because this is sort of like your final exam, I have deliberately not included step-by-step instructions as I did for earlier exercises. Instead, I have given you general directions that should be sufficient to get you started. If you get stuck, refer back to the previous chapters to remind yourself of how to accomplish the task.

Dan Clark

Chapter 13. Advanced Topics in Power Query

When you build queries using the Power Query designer, the designer creates the query using the M query language. Although you can create robust queries using just the visual interface, there is a lot of useful processing that you can only complete by writing M code. This chapter goes beyond the basics and explores some of the advanced functionality in Power Query, including the M query language, parameters, and functions.

Dan Clark

Chapter 14. Advanced Topics in Power BI

This chapter covers some advanced topics in Power BI that I think you may find useful. It includes using custom visuals, advanced mapping, row-based security, templates, and content packs.

Dan Clark


Weitere Informationen

Premium Partner

Neuer Inhalt

BranchenIndex Online

Die B2B-Firmensuche für Industrie und Wirtschaft: Kostenfrei in Firmenprofilen nach Lieferanten, Herstellern, Dienstleistern und Händlern recherchieren.



Best Practices für die Mitarbeiter-Partizipation in der Produktentwicklung

Unternehmen haben das Innovationspotenzial der eigenen Mitarbeiter auch außerhalb der F&E-Abteilung erkannt. Viele Initiativen zur Partizipation scheitern in der Praxis jedoch häufig. Lesen Sie hier  - basierend auf einer qualitativ-explorativen Expertenstudie - mehr über die wesentlichen Problemfelder der mitarbeiterzentrierten Produktentwicklung und profitieren Sie von konkreten Handlungsempfehlungen aus der Praxis.
Jetzt gratis downloaden!