Skip to main content
Disciplines
Automotive Business IT + Informatics Construction + Real Estate Electrical Engineering + Electronics Energy + Sustainability Insurance + Risk Finance + Banking Management + Leadership Marketing + Sales Mechanical Engineering + Materials
Events
DE
EN
Books
Journals
Topic Page
Marketing
Start single access now
Access for companies
EXTENDED SEARCH
Log in
Top

2017 | Book

Introducing Power Pivot Read chapter Read first chapter

Beginning Power BI

A Practical Guide to Self-Service Data Analytics with Excel 2016 and Power BI Desktop

Author: Dan Clark

Publisher: Apress

Part of: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Login to get access
insite
SEARCH

About this book

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.

Table of Contents

Frontmatter

Building Models in Power Pivot

Frontmatter
Chapter 1. Introducing Power Pivot
Abstract
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
Abstract
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
Abstract
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
Abstract
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
Abstract
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
Abstract
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
Abstract
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
Abstract
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

Frontmatter
Chapter 9. Introducing Power BI Desktop
Abstract
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
Abstract
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
Abstract
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
Abstract
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
Abstract
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
Abstract
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
Backmatter
Metadata
Title
Beginning Power BI
Author
Dan Clark
Copyright Year
2017
Publisher
Apress
Electronic ISBN
978-1-4842-2577-6
Print ISBN
978-1-4842-2576-9
DOI
https://doi.org/10.1007/978-1-4842-2577-6

Premium Partner

    Image Credits