If you are reading this book, it is probably because you need to use data. More specifically, you want to take a journey from data through to insight where quantities of facts and figures need to be shaped into comprehensible information and given clear and visual meaning. Once you have correlated and isolated your analyses, you probably want to share them with colleagues or friends.
This book is all about that journey. It covers the many ways that you can transform raw data into high-impact analyses using Power BI Desktop—the self-service business intelligence (BI)
and analytics application that Microsoft is making freely available. This fresh approach to self-service BI presumes minimal central IT intervention or dependency. It is based on giving the user the ability to use simple yet powerful tools to handle industrial-strength quantities of data and to share stunning output in the shortest possible timescales.
The following are keywords in this universe:
- Speed
- Delivery
- Empowerment
- Decentralization
- Disintermediation
Once you have mastered the tools and techniques
described in this book, you will be able to
- Discover, structure, and load your data from a wide range of sources
- Add all the calculations you need to enhance information and extract accurate analysis
- Create stylish interactive presentations
- Share your insights with your colleagues and clients
It follows that this book is written from the perspective of the user. Essentially, it is all about giving users the tools and knowledge to define their own requirements and satisfy their own needs simply and efficiently through developing new and existing skills.
This chapter assumes that you have no prior knowledge of Power BI Desktop or even business intelligence. Consequently, it starts from the very beginning by explaining what exactly self-service business intelligence really is. Then it takes you through the necessary steps to download and install Power BI Desktop. Finally, it takes you on a whirlwind tour of Power BI Desktop, where you see just how quickly and easily you can go from raw data to polished insight using this amazing tool.
If you intend to follow the dashboard example that you find in this chapter (and I hope that you will), then you need to download the source material for this book from the Apress web site at
www.apress.com/9781484232095
. Appendix A describes how to do this so that you can prepare the terrain for your upcoming adventure with Power BI Desktop.
The Microsoft Self-Service Business Intelligence Solution
It is important to understand from the start that Microsoft’s self-service business intelligence solution, Power BI, is a constantly evolving process. Indeed, it is in a continuous state of flux. Fortunately, this perpetual motion is now at a peak of readiness, and while it is still undergoing some enhancements and revisions, it is ready for immediate use.
So what exactly is Power BI? At its heart, it is a cloud-based service
that lets you store and share essential business data in the form of dashboards and reports. However, before you can share dashboards, you need to create them—and this is where Power BI Desktop comes in. This easy-to-use tool
is completely free. It is used to find, cleanse, and mash up data so that you can then develop telling metrics and deliver them in the form of stylish visualizations. Once your tables, charts, and maps are assembled into reports, you can then share them with a selected audience in Azure—the Microsoft Cloud (should you want to, of course). Yet the good news does not stop there. Your public can view your insights on just about any Windows, iOS, or Android device using the free Power BI apps that Microsoft has made available for these platforms.
So all that you have to do is to create dashboards with Power BI Desktop, share the output in Azure, and then view and interact with the results using the Power BI apps. It really is that simple. Moreover, up to a certain limit on file sizes, it is completely free. In a large corporate environment, you may even install a Power BI Report Server to make Power BI reports and dashboards available to employees without using the Web to share them.
There is more—much more—in the Power BI universe, but this short description will suffice to get you started. In any case, Chapter 23 provides more detail on the way that Power BI Desktop fits into the Microsoft’s self-service business intelligence solution. In the meantime, let’s move the focus back to Power BI Desktop. To begin, what exactly will you be using this application for? There are three answers:
- Import data
- Model data
- Create reports and dashboards
Let’s take a quick look at some of the things that these may entail.
Importing Data from Diverse Sources
The first step on the path to delivering concrete business intelligence is to find and import all the data that you need for your analysis. Power BI Desktop lets you
- Import data from a wide variety of sources. This covers corporate databases to desktop files, social media to big data.
- Merge data from multiple sources and shape it into a coherent structure.
- Cleanse your data to make it reliable and easy to use.
- Break down the data into the rows and columns that suit your requirements.
There was a time when these tasks required dedicated teams of IT specialists. In fact, it was considered so complex that it earned its own acronym, ETL, short for Extract, Transform, and
Load
. Well, this process no longer needs specialists. With Power BI Desktop, you can mash up your own data so that it is ready to use as part of your self-service BI solution.
Modeling Your Data
Power BI Desktop is not just a data store for your information. It also lets you extend and develop the cleansed data. More specifically, it allows you to
- Create a data model by joining tables to develop a coherent data structure from multiple separate sources of data. This data model is then used in dashboards.
- Enrich the data model by applying coherent names and data types.
- Create calculations and prepare the core metrics that you want to use in your analyses and presentations.
It is worth noting that you can load data into Power BI Desktop directly without going through the data cleansing and modeling stage. If the source data is already in good shape, then you can start using it straightaway.
Creating Reports and Dashboards
I think of creating reports and dashboards as the “jewel in the crown” of self-service business intelligence. A truly dynamic analysis and presentation approach lets you deliver business intelligence composed of
- Tables
- Matrixes
- Charts
- Maps
- Gauges
- Images
- KPIs
and many other types of visualization with Power BI Desktop.
Not only that, but it is incredibly fast and highly intuitive. It provides advanced interactivity so that you and your users can “slice and dice” the data “on the fly” in real time using
- Slicers
- Filters
Power BI Desktop Files
Power BI Desktop lets you create multiple pages in a single file. Each collection of pages that is based on the same underlying data is called a
report
. A Power BI Desktop file therefore contains all the dashboards and all the data that is needed by each element (called a
visualization
) on each page. So, a Power BI Desktop file is completely self-contained.
Power BI Desktop is built to handle vast quantities of data. Fortunately, however, it compresses the data that you load in an extremely efficient way. This means that Power BI Desktop files often take up only a fraction of the space that they would if they contained only the raw source data. Indeed, when connecting to certain data sources, you do not even have to load and compress the data. You can connect directly to the data and use it immediately.
This compression also applies to the data that Power BI Desktop uses when you are modeling data and creating dashboards. This is because Power BI Desktop loads all the data that you are using into memory, where it is compressed to make the most of the available memory. This means that Power BI Desktop is extremely fast to use and normally shows you the results of any changes that you make or any filters that you apply in fractions of a second. This instantaneous interactivity also applies to dashboards that you display in Windows, iOS, or Android apps.
The Power BI Universe
Power BI Desktop is one part of an integrated collection of products and services that are generally known as “Power BI.” This universe
is constantly changing, but for the moment it is composed of the following:
- PowerBI.Com: An Azure-based service where you can create and share (depending on your subscription level) data and dashboards.
- Power BI Desktop: The core tool that you use to create Power BI reports. This can involve connecting to multiple data sources and modeling and cleansing the source data.
- Power BI Report Server: An on-premises Power BI report server that allows you to distribute and deliver reports inside a corporate firewall.
- Power BI Mobile Apps: Apps that allow you to view and interact intuitively with Power BI reports and dashboards on Windows, iOS, and Android devices.
- Power BI Apps: Apps that provide a method of collecting and deploying purpose-built dashboards and reports for tailored groups of users in the cloud.
- Power BI Embedded: An Azure service that enables application developers to add interactive Power BI reports into their own applications.
- Power BI Gateway: Allows you to connect the Azure-based Power BI service to on-premises data sources and automate data refresh.
- Third-party visuals: A collection of visual elements, often created by third parties. You can add these visuals to your Power BI reports and dashboards.
It would take an entire book to describe all the elements that make up the Power BI ecosystem-and this is not that book. So, while I will provide a brief overview of third-party visuals in Chapter 18, and an introduction to the Power BI Service, the Power BI Gateway, and Power BI Apps in Chapter 23, I can only suggest that you keep a close eye on the Microsoft Power BI web site (
https://powerbi.microsoft.com
) to keep abreast of the latest developments in this fast-changing product universe.
Installing Power BI Desktop
The first thing that you have to do to create dashboards (or reports or pages of visualizations) is download and install Power BI Desktop. Although this process is really easy, you will save time if you ensure that the computer where you want to install Power BI Desktop has the capability to run this application. Currently, the minimum requirements
are as follows:
- Windows 7, Windows Server 2008 R2 or later
- Internet Explorer 9 or greater
- .Net 4.5
- At least 1GB of available RAM
Note
Power BI Desktop Designer works on both 32-bit and 64-bit computers. However, if you intend to analyze large datasets, a 64-bit workstation with several gigabytes of memory could very well prove necessary.
Microsoft specifies 1GB as a minimum memory requirement
, but you need to be aware that although the application itself is not a memory hog, it can let you load huge amounts of data. Given that all of this data will in most cases be loaded into memory, you need to ensure that you have enough available memory if you intend to analyze large amounts of data. The exception to this principle is when you will be using Power BI Desktop as a “front end” to certain databases or data warehouses, and establishing a direct connection to these sources without loading any data.
So, if you are sure that your PC or laptop is ready for Power BI Desktop, you can install it by following these steps:
- 1.Go to the Power BI Desktop download page on the Microsoft web site. This is currently https://www.microsoft.com/en-us/download/details.aspx?id=45331 . You can easily find the right page by entering Power BI Desktop download in your favorite search engine. You should see a web page containing something like the information shown in Figure 1-1.
Figure 1-1.The Power BI Desktop download page - 2.Select the language.
- 3.Click the Download button. You will be taken to the next page, where you should choose the type of download (32-bit or 64-bit), as shown in Figure 1-2.
Figure 1-2.The download selection page - 4.Click Next (this button only appears once you have selected the type of download that you want). The final download page is displayed. A pop-up appears, as shown in Figure 1-3.
Figure 1-3.The save or run download popup - 5.Click Run. The Power BI Desktop installation package will download (probably in under a minute) and the initial setup dialog is displayed, as shown in Figure 1-4. If you do not see this dialog once the download has completed, then click the toolbar icon (this will have appeared in the toolbar to make it show on top of any other open windows).
Figure 1-4.The initial Power BI Desktop setup dialog - 6.Click Next. The setup license agreement dialog will appear, as shown in Figure 1-5.
Figure 1-5.The setup license agreement dialog - 7.Ensure that the check box accepting the license agreement is checked and click Next. The setup destination dialog will appear, as shown in Figure 1-6. If you prefer to install the Power BI Desktop files in a different directory, then you can enter it here (or click the Change button and browse to select it).
Figure 1-6.The setup destination dialog - 8.Click Next. The final confirmation dialog will appear, as shown in Figure 1-7.
Figure 1-7.The setup final confirmation dialog - 9.Click Install. The Power BI Desktop installation package will run and will complete the installation in a few seconds. You may have to confirm that you allow this application to make changes to your system. You will see a progress dialog, as shown in Figure 1-8.
Figure 1-8.The installation progress dialog - 10.Once the installation process has finished successfully, you will see the completion dialog, as shown in Figure 1-9.
Figure 1-9.The final Power BI Desktop installation dialog - 11.If you want to run Power BI Desktop immediately, then leave the Launch Microsoft Power BI Desktop check box ticked; otherwise, uncheck it and click Finish. The dialog will close and Power BI Desktop is now installed on your computer.
Removing Power BI Desktop
Should you ever want to remove Power BI Desktop from a computer where it has been installed, you have a couple of choices:
- Run the web-based installation process as described earlier. At step 4, you see a dialog asking you if you want to repair or remove Power BI Desktop from your computer. Click Remove and follow the process that is indicated to delete the application from your machine.
- Open the Windows Control Panel . In the Programs section, click Uninstall a program. Select Power BI Desktop from the list of currently installed programs to uninstall it.
Running Power BI Desktop
Once you have installed Power BI Desktop successfully, you are ready to start creating dashboards and analyzing your data. You can start your Power BI Desktop experience as follows:
- 1.Click the Power BI Desktop icon that was created on the Desktop as part of the installation process. You will see the Power BI Desktop splash screen, as shown in Figure 1-10.
Figure 1-10.The Power BI Desktop splash screen
For the moment, however, it is time to stop and draw breath. You have successfully installed Power BI Desktop and you are ready to create your first dashboard with this exciting and revolutionary application.
A First Power BI Desktop Dashboard
This book takes you through an immense amount of detail that explains how to import, cleanse, and shape data from a multitude of different sources. You then learn how to carry out a variety of calculations that will help you to tease out the meaning from the data that you are analyzing. Finally, you see how to transform this analysis into telling visuals that make your insights intuitively comprehensible to your audience.
Yet before delving into all of this detail, it is perhaps more important to appreciate the really fundamental qualities of this amazing application. Despite the depth and reach of this piece of software, there are other qualities that make it stand out and that are possibly even more fundamental, including
- Simplicity : Anyone can learn to create stunning visualizations and carry out in-depth analysis of data without having to endure a steep or arduous learning curve.
- Power : Data from virtually any source can be loaded, manipulated, and combined with other data elements, and then modeled and extended without needing advanced knowledge of IT systems or data management.
Consequently, it is important to see just how easy it is to use the Power BI Desktop dashboard. Indeed, the fastest way to get you “hooked” on this particular tool is to let you see for yourself how fast you can go from zero to hero in delivering compelling dashboards. So let’s see just how quick and easy it can be to take a data source (an Excel file in this instance) and transform it into a Power BI Desktop dashboard.
Loading the Source Data
Once you have launched Power BI Desktop, you are faced with the startup screen that you saw in Figure 1-10. Given that you are working with an application that lives and breathes data, it is not really surprising that the first step in a new analytical challenge is to find and load some data. So the following explains what you have to do (assuming that you have downloaded the sample data that accompanies this book from the Apress web site-this is explained in Appendix A).
- 1.Click Get Data in the startup screen. The Get Data dialog will appear, as shown in Figure 1-11.
Figure 1-11.The Get Data dialog - 2.In the list of all the possible data sources on the right of this dialog, click Excel, and then click Connect. The Windows Open File dialog will appear.
- 3.Click the file C:PowerBiDesktopSamplesCH01CarSales.Xlsx. The Windows Open dialog will look like the one in Figure 1-12.
Figure 1-12.The Windows Open File dialog when loading data from a file source - 4.Click the Open button. The Connecting dialog will appear for a second or two and then the Navigator dialog will appear.
- 5.You will see that the CarSales.xlsx file appears on the left of the Navigator dialog and that any workbooks, named ranges, or data tables that it contains are also listed. Click the BaseData worksheet name that is on the left. The contents of this workbook will appear in the data pane on the right of the Navigator dialog.
- 6.Click the check box for the BaseData worksheet on the left. The Load and Edit buttons will be activated. The Navigator dialog should look like Figure 1-13.
Figure 1-13.The Navigator dialog with data selected - 7.Click Load. The data will be loaded from the Excel file into Power BI Desktop. You will see the Power BI Desktop report window, like the one shown in Figure 1-14.
Figure 1-14.The Power BI Desktop report window
I imagine that loading this data took under a minute. Yet you now have a fully operational data model in Power BI Desktop that is ready for action. However, before moving forward and creating a dashboard, I would like to pause for an instant and explain exactly what you have seen so far. Of course, if you are itching to race ahead and actually create a couple of tables and charts, then you can always jump ahead to the “Your First Visualizations” section.
The Data Load Process
What you have seen so far is an extremely rapid dash through a Power BI Desktop data load scenario. In reality, this process can range from the blindingly simple (as you just saw) to the more complex where you join, filter, and modify multiple datasets from different sources (as you will discover in Chapters 2 through 9). However, loading data will always be the first step in any data analysis scenario when you are using Power BI Desktop.
In this short example, you nonetheless saw many of the key elements
of the data load process. These included
- Accessing data that is available in any of the source formats that Power BI Desktop can read
- Taking a first look at the data before loading it into Power BI Desktop
What you did not see here is how Power BI Desktop can add an intermediate step to the data load process and edit the source data in Power BI Desktop Query Editor. This aspect of data manipulation is covered extensively in the following few chapters.
The Navigator Window
One key aspect of the data load process is using the Navigator window
correctly. The Navigator window appears when connecting to many, but not all, data sources. It is there to let you
- Take a quick look at the available data tables in the data source
- Filter multiple data elements that are available in a single data source
- Look at the data in individual tables
- Select one or more data tables to load into Power BI Desktop
Depending on the data source to which you have connected, you might see only a few data tables in the Navigator window, or hundreds of them. In any case, what you can see are the structured datasets that Power BI Desktop can recognize and is confident that it can import. Equally dependent on the data source is the level of complexity of what you will see in the Navigator window. If you are looking at a database server, for instance, then you may start out with a list of databases and you may need to dig deeper into the arborescence of the data by expanding databases to list the available data tables and views.
You will see much, much more of the Navigator window in the following three chapters.
The Navigator Data Preview
The Navigator Data Preview pane
(on the right) is, as its name implies, a preview of the data in a data source. It provides
- A brief overview of the top few records in any of the datasets that you want to look at. Given that the data you are previewing could be hundreds of columns wide and millions of rows long, there could be scroll bars for the data table visible inside the Navigator Data Preview.
- A list of the available columns in the data table. These are shown at the bottom of the Navigator Data Preview.
Power BI Desktop can preview and load data from several different sources. Indeed (as you can see from the list of possible data sources in the Get Data dialog in Figure 1-11), it can read most of the commonly available enterprise data sources as well as many, many others. What is important to appreciate is that Power BI Desktop applies a common interface to the art and science of loading data, whatever the source. So whether you are examining a SQL Server or an Oracle database, an XML file
or a text file, a web page or a big data source, you will always be using a standardized approach to examining and loading the data. This makes the Power BI Desktop data experience infinitely simpler—and extremely reassuring. It means that you spend less time worrying about technical aspects of data sources and you are free to focus on the data itself.
Note
The Navigator Data Preview is a brilliant data discovery tool. Without having to load any data, you can take a quick look at the data source and any data that it contains that can probably be loaded by Power BI Desktop. You can then decide if it is worth loading, so that you do not waste time on a data load that could be superfluous to your needs.
Modifying Data
Once you have one or more queries in Power BI Desktop that can connect to data sources and bring the data into this environment, you can start thinking about the next step—transforming the data so that it is ready for use. Depending on the number of data sources that you are handling and the extent of any modifications
that are required, this could vary from the simple to the complex. To give a process some structure, I advise that you try to break down any steps into the following main threads:
- Shape the dataset: This covers filtering out records to reduce the size of the dataset, as well as removing any extraneous columns. It may also involve adding columns that you create by splitting existing columns, creating calculated columns, or even joining queries.
- Cleanse and modify the data: This is also known as data transformation (the T in ETL). It encompasses the process of converting text data to uppercase and lowercase, as well as (for instance) removing nonprinting characters. Rounding numbers and extracting date parts from date data are also possible (among many other eventual transformations).
The Power BI Desktop Window
Before we go any further, I would like to explain the Power BI Desktop window, since it is something that you will use a lot in this chapter from this point onward. The Power BI Desktop window contains the elements that are outlined in Figure 1-15.
Figure 1-15.
The Power BI Desktop
As you can see, the Power BI Desktop screen is simple and uncluttered. The various elements that it contains are explained in Table 1-1.
Table 1-1.
Power BI Desktop Options
Option | Description |
|---|---|
Power BI Ribbon | This contains the principal options that are available to you when developing dashboards with Power BI Desktop. |
View Type | These three icons let you flip between Dashboard view (where you create dashboards and reports), Data view (where you add calculations), and Relationships view (where you join data from different sources). |
Dashboard Canvas | This is the main area, where you add visualizations and design your dashboards. |
Visualization pane | This area of the application is specific to each type of visualization and lets you set the specific attributes of each element on a dashboard. It also allows you to filter dashboards, pages, and individual visualizations. You can also format visualizations using this pane. |
Fields List | Here you can see all the available fields from the source data that you can use to build your visualizations. |
Visualization Palette | This area contains
all the currently available types of visualization that you can add to a dashboard. |
Page Selector | These are tabs that let you switch from page to page in a report. |
Power BI Desktop—like most Microsoft applications—has several available ribbons. These are explained in the course of this book.
Your First Visualizations
With your data safely in place inside Power BI Desktop, you can now begin to create the tables, charts, maps, and other elements that you want to add to a dashboard, which you can use to present your first insights into Brilliant British Cars. As this is a first “taster” exercise, I am not looking at explaining all that can be done using Power BI Desktop. All I want to do is to show you how easy it is to create dashboards in minutes rather than hours. Indeed, I only hope that this first simple dashboard will leave you hungry to learn more—and so to move on to the rest of this book.
Before creating a few simple visualizations, let me clarify some of the terms that you will meet when working with Power BI Desktop:
- Visualizations: Also known as visuals, these are the individual presentation elements that you create based on the underlying data. A visual can be a table, a chart, a gauge, a map, or many things indeed. I use these terms interchangeably in this book.
- Dashboard: A Power BI Desktop dashboard is a collection of visualizations on a single page. Indeed, I tend to use the terms page and dashboard interchangeably.
- Report: This is a collection of pages (or dashboards) in a single file, all using the same dataset.
Displaying Available Fields
One of the first things to do is make sure that you can see the data that you will be working with in dashboards and reports. If you look at the right of the Power BI Desktop Report view, you see a vertical pane with the label Fields at the top. This is the Fields pane (or Fields List). It is from here that you access all the data that you will use in your visualizations and dashboards.
For the moment, however, all that you can see is probably the name of the dataset that you imported previously—the BaseData dataset from Excel. Do the following to see all the fields that this table contains:
- 1.Click the small triangle to the left of the table name. The table will expand to reveal all the available fields that it contains. Alternatively, if the fields are already visible, they will disappear from view, leaving only the data table name visible. You can see what this looks like in Figure 1-16.
Figure 1-16.The Power BI Desktop Fields list
You can see that some of the fields have a sigma (∑) icon to their left. This indicates that the data in the field is numeric. As you progress through this book, you will see that there are other icons that Power BI Desktop uses to flag different types of fields.
Adding a Matrix of Sales per Country by Year
It is now time to draw on the blank canvas that is your first dashboard. To begin, let’s start with a simple matrix of sales per country for each year that Brilliant British Cars has been trading.
- 1.In the Visualizations pane, click the matrix icon, as highlighted in Figure 1-17.A blank matrix will appear on the dashboard canvas.
Figure 1-17.The matrix icon in the Visualizations pane - 2.Leaving the freshly created matrix selected, click the check box to the left of the CountryName field in the Fields list. The list of countries where cars have been sold will appear as the left-hand column of the matrix.
- 3.Drag the ReportingYear field into the Visualizations pane over the Columns fields area (this is called the field well). Figure 1-18 shows how to do this. This adds the model years as column headers in the matrix.
Figure 1-18.Adding the Columns fields to a matrix - 4.Leaving the matrix selected , click the check box to the left of the SalePrice field in the Fields list. The aggregated sale price for all vehicles sold by country and by year will appear in the matrix.
- 5.Drag the corner handle of the matrix to resize it so that there is no spare whitespace inside the matrix itself. It will look like Figure 1-19.
Figure 1-19.A matrix of sales per country
It would be hard to make this any simpler
. Within seconds, you have created a matrix of sales by year and country and the totals have been added automatically. Of course, there are many ways of extending and developing a matrix in Power BI Desktop—and you can discover them all in Chapter 15—but for now, it is time to press on and add a chart to your fledgling dashboard.
Note
In this short exercise, you saw that you can both select fields from the Fields list or drag them to the field well to add them to a selected visual. An alternative is to drag a field from the Fields list onto the visualization itself.
Adding a Column Chart of Delivery Charge by Model
Now that you have seen how easy it is to create a matrix in Power BI Desktop, the time has come to add some visual impact to your analysis. This time, you will use the available data to display the total delivery charge for each model of car sold.
- 1.Click an empty area of the dashboard canvas to unselect any visualizations.
- 2.Drag the Model field onto an empty area of the dashboard canvas. Power BI Desktop automatically creates a table displaying all the vehicle models sold.
- 3.Drag the DeliveryCharge field from the Fields pane onto the table that you just created. Power BI Desktop will calculate the total DeliveryCharge for each available make. The table will look like Figure 1-20.
Figure 1-20.A table of aggregated delivery charge per make - 4.Leaving the table selected , click the clustered column chart icon in the Visualizations pane. This is the second icon on the left on the upper row of the selection of visualizations. Power BI Desktop will switch the table to a chart.
- 5.Drag the corner handle of the chart to resize it so that all the makes are visible on the bottom axis. The chart will look like Figure 1-21.
Figure 1-21.A column chart of delivery charge by model
Equally simple, don’t you think? Yet, believe me, the fun has only just begun. While you will see lots more about how to create and enhance charts in Chapters 16 and 17, you can always try a few basic tweaks now. For instance, if you select the chart and then click any of the other charting icons in the Visualizations pane, you can change the type of chart instantaneously. Moreover, if you are not sure which icon does what, then all you have to do is hover the mouse pointer
over an icon in the Visualizations pane to display a tooltip that will guide you further.
Adding a Map of Labor Cost by Country
Tables and charts are all very well, but nothing beats a good picture when it comes to illustrating a point or highlighting an insight. So, as we have a dataset that includes information for a range of countries, why not display some of our analysis as a map?
- 1.Click any empty part of the dashboard canvas to unselect any visualizations.
- 2.Click the filled map icon in the Visualizations pane. You can see this icon in Figure 1-22.
Figure 1-22.Adding a filled map visualization to a dashboard - 3.Leaving the empty map visualization selected, click the check box to the left of the CountryName field in the Fields list. This will display a map of the world.
- 4.Leaving the map selected, drag the LaborCost field onto the map. This will highlight any countries where there are labor costs relating to vehicles sold.
- 5.Drag the colored European countries to the center of the map.
- 6.Using the mouse wheel, zoom in to the colored European countries. The finished map will look like Figure 1-23.
Figure 1-23.A map of labor cost by country
This time, and in only a few clicks
, you have used your data to create a map that clearly illustrates the geography of your sales. Once again, this is only a rapid overview of all that Power BI Desktop can do when it comes to displaying mapping data. You will learn more about creating and modifying maps in Power BI Desktop in Chapter 19.
Adding a Card Showing the Total Cost of Spare Parts
Sometimes you do not want to show a large amount of data but quite the opposite. You want to highlight a single figure to give it prominence on the dashboard. Power BI Desktop has a really effective way of doing just this. It consists of adding visualizations called cards, which are what you will now add to your dashboard.
- 1.Click the dashboard canvas to unselect any visualizations.
- 2.Click the card icon in the Visualizations pane, as shown in Figure 1-24.
Figure 1-24.Adding a card visualization to a dashboard - 3.Leaving the (slightly clunky) empty card visualization selected, click the check box to the left of the SpareParts field in the Fields list. This displays the spare parts total in the source data.
- 4.Drag the corner handle of the matrix to resize it so that there is no spare whitespace inside the matrix itself. It will look like Figure 1-25.
Figure 1-25.A card showing the total cost of spare parts
That is all you have to do. Three or four clicks and you have a clear visualization of a key metric for your audience. This is not the only way that you can create this particular visualization, but you have to wait for Chapter 15 to get all the details on adding cards to Power BI Desktop dashboards.
Adding a Slicer by Make
As a final tweak, you will add some interactivity to the dashboard that you are building. You will add a slicer (an interactive selection tool) that will let you—or any user of this dashboard—filter by any or all car models sold. Here is how you can do this:
- 1.Drag the Make field to a blank area on the dashboard canvas. Power BI Desktop will create a list of vehicle models.
- 2.Click the slicer icon in the Visualizations pane, as shown in Figure 1-26.
Figure 1-26.Adding a slicer to a dashboard - 3.Drag the corner handle of the slicer to resize it so that there is no spare whitespace inside the slicer. It will look like Figure 1-27.
Figure 1-27.A slicer on the model of vehicle
You can now test the slicer by selecting—or deselecting—any car model that is listed in the slicer. The other visualizations on the dashboard will instantly be updated to reflect the choice of models. You will soon get a first look at how this slicer can be used to filter data.
Arranging the Dashboard
Now that you have created a few visuals, it is time to coordinate them on the page so that you can deliver a meaningful dashboard that adds power to your insights.
Moving a Visualization
Moving a visualization is impressively easy:
- 1.Click the visualization that you want to move.
- 2.Drag the visualization elsewhere on the dashboard canvas.
Resizing a Visualization
Resizing a visualization is also extremely easy:
- 1.Click the visualization that you want to resize.
- 2.Move the mouse pointer over any of the corner or side handles of the visualization. The pointer will become a double-headed arrow.
- 3.Drag the edge of the visualization to increase or decrease its current size.
After a little effort, your dashboard could look like the one in Figure 1-28.
Figure 1-28.
The final version of your first dashboard
So here you have your first Power BI dashboard. I have to admit that this first stab at self-service business intelligence was not concentrating overmuch on the aesthetics of the output. I preferred to let you appreciate the speed and simplicity with which you have created an entire dashboard, from scratch and with no prior knowledge of the tool that you have used.
How long did it take you to build this dashboard, do you think? Fifteen minutes? Thirty minutes? Indeed, however long it took, you have also learned the basics of dashboarding with Power BI Desktop. You can now build on this knowledge as you progress through this book.
In fact, extending a dashboard
by adding further visualizations is so intuitive that it is too easy to miss the salient points of what you have seen so far. So, to resume, what you have just learned is that
- You can place any visualization anywhere on the dashboard canvas.
- You can resize an element quickly and easily.
- You can convert any type of visualization to any other type in a single click.
Interactivity in Dashboards
Building a dashboard was only the start, as far as Power BI Desktop is concerned. For a Power BI dashboard is never set in stone. In fact, quite the opposite is true, because every dashboard that you create is instantly and intuitively interactive. This means that you can use it to highlight salient points and drill down to expose the key insights that your analysis has led you to.
Even a simple dashboard like the one that you just created is immediately interactive. As an example, suppose that you want to use this dashboard to display data for only a couple of the makes that the company has sold. The following explains how to do it:
- 1.In the slicer (on the top right of the dashboard), Ctrl-click Bentley and Rolls-Royce. The dashboard will instantly update to show only data for these car models, as shown in Figure 1-29.
Figure 1-29.Interactively filtering a dashboard using a slicer - 2.In the slicer, click any element twice to clear the filter.
- 3.In the map, click France . The dashboard will instantly update to show only data for the selected country, as shown in Figure 1-30.
Figure 1-30.Interactively filtering a dashboard using a map
Formatting Reports
Power BI Desktop allows you to create reports with multiple visuals in record time. Out of the box you can produce stylish levels of presentation in a few clicks. Yet with a little effort, you can enhance the standard Power BI Desktop formatting and truly leave your audience impressed with your presentation skills.
This section will introduce you to a few of the techniques that you can apply to transform the look and feel of your reports. As was the case with the previous sections in this chapter, the aim here is not to give you an exhaustive tour of all the formatting possibilities that Power BI Desktop has to offer (Chapters 14 through 22 will do this). Instead, I want only to show you how easily and intuitively you can take the allure of your reporting to the next level. Fortunately, Power BI Desktop lets you format nearly all visuals in similar ways. So you only have to learn a few basic techniques to enhance all your reports in record time.
If you want to try formatting a report, but have not built the report that you have seen so far in this chapter, you can always load the Power BI Desktop file named C:PowerBiDesktopSamplesCH01CH01Example.pbix-assuming, of course, that you have downloaded the sample data as described in Appendix A.
The Format Pane
All formatting is defined in the Format Pane. You switch to the Format Pane as follows:
- 1.Select an existing visual.
- 2.Click the “paint roller” icon in the Visualizations pane. This is the Format icon. You can see this in Figure 1-31.
Figure 1-31.The Format icon
Clicking the Format icon will display the Format Pane containing the formatting options that are available for the type of visual that you have selected. The range of available formatting options will vary for each type of visual.
Note
You can only format multiple visuals at the same time if all the selected visuals are of the same type.
Borders
Nearly all visuals allow you to add a border
. To enhance a visual with a border:
- 1.Select the visual that you want to format (I will select the card in the report that you have built in this chapter).
- 2.Click the Format icon .
- 3.Click the Border button to add a border.
- 4.Expand the Border section of the Format Pane.
- 5.Click the color palette and select a color for the border. The Format Pane will look like Figure 1-32.
Figure 1-32.The Format Pane for card visuals
That is all you have to do. The border is now added to the selected visual. Fortunately, this technique will work in the same way for most Power BI Desktop report visuals, whatever they may be.
Background Color
Some visuals look better with a different background color
. I like to distinguish slicers from other visuals, so let’s add a different background color to the slicer in the sample report.
- 1.Select the visual that you want to format (I will select the slicer in the report that you have built in this chapter).
- 2.Click the Format icon, unless the Format Pane is already displayed.
- 3.Expand the Background section of the Format Pane.
- 4.Click the Background button to add a background color. The Format pane will display “Background On” and the background button will now be a filled circle.
- 5.Click the color palette and select a color for the background color. The Format Pane will look like Figure 1-33.
Figure 1-33.The Format Pane for slicers
As you can see, there are other background color options. I will explain these in Chapter 15.
Titles
Certain visuals need titles
, either to make a point or to differentiate a specific visual. Adding or modifying a title is easy:
- 1.Select the visual whose title you want to modify (I will select the chart in the report that you have built in this chapter).
- 2.Click the Format icon, unless the Format Pane is already displayed.
- 3.Expand the Title section of the Format Pane.
- 4.Ensure that the Title button is on. This should already be the case for a chart.
- 5.Change the title text to Delivery Charges.
- 6.Click the color palette and select a color for the font color.
- 7.Drag the slider for the Text Size option to the right to increase the font size. The Format Pane will look like Figure 1-34.
Figure 1-34.The Format Pane for chart visuals
As you can see, a chart has many more available formatting options than the other visuals that you saw so far. These options will be explained in more depth in Chapter 17.
Table Gridlines
Tables and matrices can be enhanced with your choice of gridlines. Adding these is as easy as
- 1.Select the visual that you want to format (I will select the table in the report that you have built in this chapter).
- 2.Click the Format icon, unless the Format Pane is already displayed.
- 3.Expand the Grid section of the Format Pane.
- 4.Switch on the vertical and horizontal grids.
- 5.Select an Outline Color from the Outline Color palette. The Format Pane will look like Figure 1-35.
Figure 1-35.The Format Pane for table visuals
Yet again, there are many more options
that you can adjust to add extremely precise formatting to tables. These techniques are explained in Chapter 14.
Data Colors
As a final quick example of how formatting can be applied to your visuals, suppose that you want to change the color of the data representation in the map that you created. To do this:
- 1.Select the map that you want to format.
- 2.Click the Format icon.
- 3.Expand the Data colors section of the Format Pane.
- 4.Click the color palette and select a color for the border. The Format Pane will look like Figure 1-36.
Figure 1-36.The Format Pane for maps
Now that you have tweaked the presentation of your starter report
, it should look something like the one shown in Figure 1-37.
Figure 1-37.
The original report with initial formatting added
Admittedly, these are only a few quick modifications
to introduce you to some of the formatting possibilities that are currently available in Power BI Desktop. There is much, much more that you can do once you are at ease with Power BI Desktop and have understood the basic approach to formatting visuals. With a little practice, you should be able to produce high-quality reports and dashboards that enable your analyses to leap out at the audience and convey the information that you want to deliver. If you want to take a look at the final, formatted report, you can open the Power BI Desktop file C:PowerBiDesktopSamplesCH01CH01Example_Formatted.pbix.
Note
Deleting a visual is as simple as selecting it and pressing the Delete key. Removing a visual will not affect the underlying data in any way.
Creating and Modifying Reports
So far in this chapter, we have treated the Power BI Desktop file as if it consisted of only a single page. In practice, you are likely to need to base several pages of analysis and information on the same underlying dataset. Consequently, Power BI Desktop makes it easy to add, copy, and delete the pages in your original file so that you can create complex data stories that all use the same dataset.
If (as I presume is probably the case for many Power BI users) you are used to using Excel, then you will likely find the way that pages are handled in self-service BI incredibly simple, because in Power BI Desktop, each page is very similar to an Excel worksheet. To make matters clearer, look at Figure 1-38, where you can see the page tabs of Power BI Desktop.
Figure 1-38.
The Power BI Desktop page tabs
Adding Pages
When you open a new Power BI Desktop file, it always defaults to having a single page, thoughtfully named Page 1. You can add a new page as follows:
- In the Home ribbon, click the New Page button . A new blank page named Page n will be added to the existing collection of pages in the report.
Tip
As an alternative to the New Page button, you can always click the small plus-sign tab at the bottom of the screen, as seen in Figure 1-38.
Renaming Pages
You can rename pages, as follows:
- 1.Double-click the tab of the page that you want to rename. The existing name will be highlighted.
- 2.Enter a new name for the page.
- 3.Press Enter. Click inside the dashboard canvas for the page or click another tab to confirm your changes.
Deleting Pages
If a page is no longer any use to you, then you can delete it, of course.
- 1.Hover the mouse pointer over the tab for the page that you want to delete.A small cross appears at the top right of the page name, as you can see in Figure 1-38.
- 2.Click the cross. A warning dialog will appear, as shown in Figure 1-39.
Figure 1-39.The page delete dialog - 3.Click Delete. The page will be deleted and all visuals on the page are removed from the file.
Moving Pages
To alter the sequencing of the pages in your report, do the following:
- 1.Click the tab corresponding to the page that you want to move.
- 2.Drag the page tab left or right to a new position in the set of pages.
Duplicating Pages
If a page contains a set of elements that you want to reuse (it may be a template page containing a logo and the background for a series of pages in a report, for instance), then you can make duplicates of pages, as follows:
- 1.Hover the mouse pointer over the tab for the page that you want to delete.
- 2.Right-click the tab. A pop-up menu will appear.
- 3.Select Duplicate Page.
An identical copy of the page will appear to the right of any existing pages. There is also a Duplicate Page option in the popup menu for the New Page button if you prefer.
Scrolling Through Collections of Pages
If your report contains dozens of pages, then it can get very wearing to trawl through the set of pages one at a time. Instead, you can click the page scroll buttons
(see Figure 1-38) to scroll through the set of pages in a Power BI Desktop file.
Conclusion
Welcome to Power BI Desktop. In a short chapter, you have seen just how amazingly simple and intuitive it is to use this free, self-service business intelligence tool from Microsoft. You have seen how to load data from an external source. This chapter has also given you an idea of the wealth of potential sources of data that Power BI Desktop can handle. You saw how to take data and use it to create tables, charts, maps, and slicers in an interactive dashboard that you can now share with co-workers and friends, if you want to. Once you have created your reports, you can then enhance them visually through a wide range of formatting options.
Yet all of this merely scratched the surface of the vast potential of this amazing application. As you will discover as you progress through this book, you are on the cusp of discovering a veritable treasure trove of analytical possibilities and stunning visualizations that will help you drive your data analysis and presentation skills to the next level.