42,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Serie: Tech Today
- Sprache: Englisch
Discover the utility of your organization's data with Microsoft Power BI In Beginning Power BI for Business Users: Learning to Turn Data into Insights, accomplished data professional and business intelligence expert Paul Fuller delivers an intuitive and accessible handbook for professionals seeking to use Microsoft's Power BI to access, analyze, understand, report, and act on the data available to their organizations. In the book, you'll discover Power BI's robust feature set, learn to ingest and model data, visualize and report on that data, and even use the DAX scripting language to unlock still more utility from Microsoft's popular program. Beginning with general principles geared to readers with no or little experience with reporting or data analytics tools, the author walks you through how to manipulate common, publicly available data sources--including Excel files and relational databases. You'll also learn to: * Use the included and tested sample code to work through the helpful examples included by the author * Conduct data orchestration and visualization to better understand and gain insights from your data An essential resource for business analysts and Excel power users reaching the limits of that program's capabilities, Beginning Power BI for Business Users will also benefit data analysts who seek to prepare reports for their organizations using Microsoft's flexible and intuitive software.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 419
Veröffentlichungsjahr: 2023
Ähnliche
Table of Contents
COVER
TABLE OF CONTENTS
TITLE PAGE
INTRODUCTION
WHAT DOES THIS BOOK COVER?
WHO SHOULD READ THIS BOOK
COMPANION DOWNLOAD FILES
PART 1: Introduction
1 Introducing Power BI
WHAT IS POWER BI?
WHY WOULD I USE POWER BI?
BUT WHY?
WILL I STILL USE EXCEL?
HOW MUCH IS THIS GOING TO COST?
WHERE DO WE GO FROM HERE?
NOTE
2 From Data to Insight
THREE GUIDING PRINCIPLES
DISTINGUISHING DATA, INFORMATION, AND KNOWLEDGE
FOLLOW A CONSISTENT, PROVEN METHOD
USE VISUALIZATIONS WELL
CONCLUSION
NOTES
PART 2: Power BI Guided Walk-Throughs
3 Let's Take a Flyover!
GETTING CONNECTED
A QUICK TOUR OF THE POWER BI SERVICE
POWER BI BUILDING BLOCKS
FLYOVER OF POWER BI DESKTOP
CONCLUSION
4 Building Your First Report
CONNECTING TO A DATA SOURCE
EXAMINING THE DATA MODEL
EXPLORING THE DATA
BUILDING YOUR REPORT
DESIGNING FOR MOBILE DEVICES
CONCLUSION
NOTE
5 Preparing Data
GDP DATA
CLEANING MESSY DATA
CONCLUSION
NOTES
6 Modeling Data
DIMENSIONAL MODELING
FACTS
DIMENSIONS
DATE DIMENSIONS
POWER QUERY AND DATA MODELING
PUTTING IT ALL INTO ACTION
CONCLUSION
NOTES
PART 3: Going Deeper into Power BI
7 Relationships and Filtering
FILTERING
THROUGH
RELATIONSHIPS
VISUALS INTERACT INDEPENDENTLY
THE DIRECTION OF RELATIONSHIPS
BIDIRECTIONAL FILTERING
REPORT FILTER TYPES
CONCLUSION
8 Enhancing Your Report
CONFIGURING DRILL-DOWN
CONFIGURING DRILL-THROUGH
BUTTONS
BOOKMARKS
SELECTIONS AND VISIBILITY
CONCLUSION
9 Refreshing, Sharing, and Collaborating
REFRESHING DATA
SHARING AND COLLABORATING
SUMMARY
10 Introducing DAX
SETUP
CALCULATED COLUMNS
DAX MEASURES
FILTER CONTEXT
CALCULATE FUNCTION
CONCLUSION
11 Conclusion
WHERE DOES MY ORGANIZATION BEGIN?
WHERE DO I GO FROM HERE?
NOTE
INDEX
COPYRIGHT
DEDICATION
ABOUT THE AUTHOR
ABOUT THE TECHNICAL EDITOR
ACKNOWLEDGMENTS
END USER LICENSE AGREEMENT
List of Tables
Chapter 1
TABLE 1.1: Power BI Service Levels
Chapter 4
TABLE 4.1: Sales Table with Everything
TABLE 4.2: Sales Table with Item ID
TABLE 4.3: Item Table
Chapter 5
TABLE 5.1: Mathematical Transformation Options
Chapter 9
TABLE 9.1: Power BI Workspace Roles
List of Illustrations
Chapter 1
FIGURE 1.1: Power BI Desktop
FIGURE 1.2: Publishing to the Power BI Service
FIGURE 1.3: Power BI Mobile
FIGURE 1.4: Report creators, report consumers, and Power BI admins interact ...
FIGURE 1.5: Excel's Hide Selected Item function
FIGURE 1.6: Excel's Get Data feature
Chapter 2
FIGURE 2.1: A sample worksheet
1
FIGURE 2.2: From data to wisdom pyramid
FIGURE 2.3: Using a gauge for a report
FIGURE 2.4: A more compact visual representation of the data
FIGURE 2.5: A dashboard with too many types of visualization
FIGURE 2.6: A simplified dashboard
FIGURE 2.7: A dashboard visualization with too much information
FIGURE 2.8: Wrong visualization for data
FIGURE 2.9: A visually misleading pie chart
FIGURE 2.10: Removing unnecessary borders from a graph
FIGURE 2.11: Removing unnecessary borders from a table
FIGURE 2.12: De-emphasizing nondata pixels that remain
FIGURE 2.13: Muting row shading to reduce distraction
FIGURE 2.14: Highlighting the most important data
Chapter 3
FIGURE 3.1: Frequent hotspots
FIGURE 3.2: The results of adding the sample report
FIGURE 3.3: Elements of the Power BI report
FIGURE 3.4: Example tooltip
FIGURE 3.5: Legend in “This Year Sales by Chain”
FIGURE 3.6: Fashions Direct chain data highlighted
FIGURE 3.7: Corporate Spend report
FIGURE 3.8: Built-in tooltip example
FIGURE 3.9: Using the Sales Region slicer
FIGURE 3.10: Comparing slicer selection to filtered values
FIGURE 3.11: Impact of filtering the Plan value
FIGURE 3.12: All IT areas listed in the decomposition tree
FIGURE 3.13: Drill-down arrows displayed
FIGURE 3.14: Drilling down into an IT subarea
FIGURE 3.15: Pinning a visual to a dashboard
FIGURE 3.16: Example of the new dashboard
FIGURE 3.17: Focus mode icon in right corner
FIGURE 3.18: Drill Through menu item
FIGURE 3.19: Using the Show As A Table feature
FIGURE 3.20: Steps to change sort order of visual
FIGURE 3.21: How to include or exclude values from a visual
FIGURE 3.22: Power BI Desktop feature areas
FIGURE 3.23: Format button for every visual
Chapter 4
FIGURE 4.1: The Data hub
FIGURE 4.2: The Data pane
FIGURE 4.3: Datasets within and outside your report
FIGURE 4.4: The Model view
FIGURE 4.5: The entire data model in one glance
FIGURE 4.6: Meaning of icons
FIGURE 4.7: The relationship displayed between two tables
FIGURE 4.8: Relationship lines
FIGURE 4.9: Comparison of Model view and Report view
FIGURE 4.10: Selecting the Table visual
FIGURE 4.11: Creating a placeholder on the report canvas
FIGURE 4.12: Manipulating the Table visual
FIGURE 4.13: Fields added to the Store table
FIGURE 4.14: Table sorted by StoreNumberName
FIGURE 4.15: Table sorted by Store Type
FIGURE 4.16: Default aggregate is to sum the values
FIGURE 4.17: Four date values
FIGURE 4.18: Only the quarter is displayed
FIGURE 4.19: Changing aggregation to Average
FIGURE 4.20: SellingAreaSize giving average value
FIGURE 4.21: Calculating the measures based on the surrounding context
FIGURE 4.22: Earlier version of sample report
FIGURE 4.23: The Format Your Report Page button
FIGURE 4.24: Adding text to a new text box
FIGURE 4.25: Resizing text in a text box
FIGURE 4.26: Selecting the Card visual
FIGURE 4.27: The Card visual in a prominent position
FIGURE 4.28: Resizing the text box
FIGURE 4.29: Selecting the appropriate font
FIGURE 4.30: Changing the caption font
FIGURE 4.31: Pasting the copied visual
FIGURE 4.32: Report with the New Stores visual added
FIGURE 4.33: Selecting all five visuals
FIGURE 4.34: Original pie chart showing Lindsey's with an $ amount but not ...
FIGURE 4.35: Selecting a 100% Stacked bar chart
FIGURE 4.36: Adding the Chain field to the Y-axis property of the visual
FIGURE 4.37: Formatting the visual
FIGURE 4.38: Assigning a value to the x-axis
FIGURE 4.39: The titled visual
FIGURE 4.40: Pasting the copied visual
FIGURE 4.41: Graphic from original report
FIGURE 4.42: Selecting a Matrix visual
FIGURE 4.43: Adding a Matrix visual to the report
FIGURE 4.44: Adding conditional formatting
FIGURE 4.45: Conditional formatting for this example
FIGURE 4.46: Scatter chart
FIGURE 4.47: Scatter Plot fields added
FIGURE 4.48: Completed report
FIGURE 4.49: Report publish confirmation
FIGURE 4.50: Modifying the canvas for mobile layout
FIGURE 4.51: Note that mobile layout does not affect report layout.
Chapter 5
FIGURE 5.1: GDP data summarized by categories
FIGURE 5.2: The Navigator window
FIGURE 5.3: The Power Query tool
FIGURE 5.4: Modifying the query
FIGURE 5.5: Drop-down arrows in the column header
FIGURE 5.6: Four remaining rows of data
FIGURE 5.7: Unpivot Columns options
FIGURE 5.8: Selecting a data type
FIGURE 5.9: The Data pane
FIGURE 5.10: Adding a new column
FIGURE 5.11: Adding a Previous Index column
FIGURE 5.12: The upper-right cell shows an empty value.
FIGURE 5.13: Formatting the new columns
FIGURE 5.14: Adding a Line and Stacked Column chart
FIGURE 5.15: Formatting and labeling the chart
FIGURE 5.16: Adjusting the chart
FIGURE 5.17: Viewing GDP change relative to time
FIGURE 5.18: Many-to-many relationship
FIGURE 5.19: Extract the data into multiple tables.
FIGURE 5.20: The Split Column By Delimiter window
FIGURE 5.21: A Genre value displayed for each movie
FIGURE 5.22: The Format button
FIGURE 5.23: Clicking Merge Queries
FIGURE 5.24: Expanding the Genre column
FIGURE 5.25: Notification of pending changes not yet applied
FIGURE 5.26: The Model view
FIGURE 5.27: The Edit Relationship window
FIGURE 5.28: The Transform Data button
FIGURE 5.29: The Replace Values window
FIGURE 5.30: Creating a custom column
FIGURE 5.31: Data type errors
FIGURE 5.32: Sum of Income by genre name
Chapter 6
FIGURE 6.1: Dimensions and facts example
FIGURE 6.2: A dimension as a group of related attributes
FIGURE 6.3: Fact and dimension table
FIGURE 6.4: Multiple dimensions can describe Sales, Inventory, Purchase Orde...
FIGURE 6.5: A star schema
FIGURE 6.6: A hierarchy within a table
FIGURE 6.7: A flattened product table
FIGURE 6.8: Transforming data into dimensions and facts
FIGURE 6.9: Sample GDP data
FIGURE 6.10: Sample GDP data model
FIGURE 6.11: Enter the server name.
FIGURE 6.12: Viewing the Data Dimension table
FIGURE 6.13: The area to be modified in the script
FIGURE 6.14: The new Calendar table
FIGURE 6.15: Selecting the Date data type
FIGURE 6.16: A basic Date dimension
FIGURE 6.17: Changing the query source
FIGURE 6.18: Selecting the State column
FIGURE 6.19: Selecting Unpivot Other Columns
FIGURE 6.20: Model view showing changes
FIGURE 6.21: The completed report
Chapter 7
FIGURE 7.1: Gross domestic product (GDP) report with date and region slicers...
FIGURE 7.2: Excluding a value
FIGURE 7.3: Excluded section
FIGURE 7.4: Region slicer displaying “Far West”
FIGURE 7.5: Nothing displayed in the Far West region
FIGURE 7.6: Removing the Region field in the Rows section
FIGURE 7.7: Before and after having removed the region from the Rows propert...
FIGURE 7.8: Region and GDPValue added to new matrix and totals not matching ...
FIGURE 7.9: The Far West region selected in the slicer filters both the Matr...
FIGURE 7.10: The direction of relationships
FIGURE 7.11: Unhiding the StateID value
FIGURE 7.12: Adding the State field to the slicer
FIGURE 7.13: Selecting Alabama
FIGURE 7.14: Removing the
Far West
Excluded filter
FIGURE 7.15: Excluding the Far West region
FIGURE 7.16: Selecting the relationship between the State GDP fact and the S...
FIGURE 7.17: The cross-filter direction property is now Both.
FIGURE 7.18: Date filtering
FIGURE 7.19: Changing the first column from Region to State
FIGURE 7.20: Selecting New Measure
FIGURE 7.21: New measure displayed
FIGURE 7.22: Table showing State and TOPI columns
FIGURE 7.23: View with only five rows
FIGURE 7.24: Editability and visibility
Chapter 8
FIGURE 8.1: Selecting the Sales Variance From Target By District Manager mat...
FIGURE 8.2: District field added above DM field in Rows property and reflect...
FIGURE 8.3: Removing the Year field
FIGURE 8.4: Drilling on Rows property displayed next to the Drill Mode icons...
FIGURE 8.5: Table visual configured to show only one row
FIGURE 8.6: Moving down to the next level of hierarchy
FIGURE 8.7: Every territory in the second level of the hierarchy displayed a...
FIGURE 8.8: Every salesperson displayed in the third level of the hierarchy ...
FIGURE 8.9: Drill-through report filtered to value selected on previous page...
FIGURE 8.10: Arrow button in upper-left corner selected
FIGURE 8.11: Button properties in Format pane
FIGURE 8.12: Options for the action property displayed
FIGURE 8.13: Overview page with the Lindseys chain highlighted
FIGURE 8.14: The Bookmarks pane
FIGURE 8.15: Bookmark options
FIGURE 8.16: Extra whitespace where buttons are going to be placed
FIGURE 8.17: Bookmark menu item location
FIGURE 8.18: Bookmark button located in available whitespace
FIGURE 8.19: Configuring the bookmark action properties
FIGURE 8.20: Bookmark buttons created and renamed
FIGURE 8.21: c08report displayed In My Workspace
FIGURE 8.22: Show More Bookmarks menu item displayed
FIGURE 8.23: Copied and pasted visuals overlapping
FIGURE 8.24: Dragging the Total Sales Variance measure into the Values prope...
FIGURE 8.25: Copied and pasted visuals overlapping
FIGURE 8.26: Title changed on pasted visuals
FIGURE 8.27: Visuals listed in the selection pane
FIGURE 8.28: Two bookmark buttons created
FIGURE 8.29: Hiding the Sales Variance ($) visual
FIGURE 8.30: Selecting the Show % bookmark
Chapter 9
FIGURE 9.1: The manual process of updating and publishing Excel reports
FIGURE 9.2: The manual process of refreshing a Power BI report
FIGURE 9.3: Distinguishing between a report and a dataset and the location o...
FIGURE 9.4: The automatic process of refreshing a Power BI dataset
FIGURE 9.5: Accessible file and database cloud-based sources of data
FIGURE 9.6: Finding the Settings menu for a dataset
FIGURE 9.7: The location of the link to edit data source credentials
FIGURE 9.8: Editing the data source credentials for an anonymous source
FIGURE 9.9: Editing basic username and password credentials
FIGURE 9.10: On-premises data comes through Data Gateway into the cloud into...
FIGURE 9.11: Configuring your dataset to use the on-premises data gateway
FIGURE 9.12: Setting up the schedule to refresh your data
FIGURE 9.13: Finding the Workspaces area in the Power BI Service
FIGURE 9.14: Entering the details for a new workspace
FIGURE 9.15: Picking a workspace in which to publish a report
FIGURE 9.16: Creating a new security group in Azure Active Directory
FIGURE 9.17: Members listed in a security group
FIGURE 9.18: Menu link to the Workspace Access area
FIGURE 9.19: Button to navigate to the Workspace Access area
FIGURE 9.20: Adding the security group to the Workspace Access area
FIGURE 9.21: Location of the Create App button
FIGURE 9.22: Defining a Power BI App
FIGURE 9.23: The Add Content button to specify what will be in the app
FIGURE 9.24: Selecting content for the app
FIGURE 9.25: Describing a link to add to the app
FIGURE 9.26: Specifying security group to use the Power BI app
FIGURE 9.27: The New Audience button
FIGURE 9.28: Hiding or unhiding content for an audience
FIGURE 9.29: External web content added into Power BI App
FIGURE 9.30: Apps link in the Power BI Service
FIGURE 9.31: Finding the IT Spend Trend report page in the app
Chapter 10
FIGURE 10.1: Sales star schema data model
FIGURE 10.2: Sales table selected and the location of the New Column button...
FIGURE 10.3: DAX formula editor window
FIGURE 10.4: Formatting a DAX measure
FIGURE 10.5: Location of the Percent formatting button
FIGURE 10.6: Selecting Average for the default aggregation
FIGURE 10.7: Calculated fields added onto a Matrix visual
FIGURE 10.8: The New Measure button on the Home ribbon
FIGURE 10.9: Identifying calculated columns versus DAX measures in the Data ...
FIGURE 10.10: Controlling the number of decimal places displayed when using ...
FIGURE 10.11: Controlling the unit values displayed in a matrix column
FIGURE 10.12: Using the Small Multiples property on a Clustered Column chart...
FIGURE 10.13: Calendar Year slicer added to the report along with another Ma...
FIGURE 10.14: Three charts demonstrating filter context
FIGURE 10.15: Filter context working in conjunction with highlighting
FIGURE 10.16: Total average sales per day calculated across the whole year
FIGURE 10.17: Excel averaging values in the cells above versus Power BI aver...
FIGURE 10.18: Total Sales 2020 measure added to the Table visual
Chapter 11
FIGURE 11.1: Power BI delivery approaches in an enterprise
Guide
Cover
Title Page
Copyright
Dedication
About the Author
About the Technical Editor
Acknowledgments
Introduction
Table of Contents
Begin Reading
Index
End User License Agreement
Pages
v
xxi
xxii
1
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
169
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
257
258
259
260
261
262
263
264
265
vi
vii
viii
xi
xiii
266
BEGINNINGPower BI® for Business Users
LEARNING TO TURN DATA INTO INSIGHTS
Paul D. Fuller
INTRODUCTION
As a data analytics engineer, I've loved using Power BI since it was introduced many years ago. But I'm a tech nerd. And many of the experts in Power BI are also geeks like me. But here's the thing—Power BI is meant to be a tool for business users and data analysts. It's intended to be simple enough to get started and see amazing things quickly but sophisticated enough that the information technology (IT) folks can use it as well.
In my years in consulting, I have worked with many clients who are simply business users wanting to learn Power BI. They typically have been Excel gurus who live and die by their spreadsheets. And they still will need those spreadsheets at times, but they know that Power BI will take them where they couldn't go with Excel. But they were stuck.
Their IT departments would give them access to use Power BI. Some of these clients would start with Power BI quickly but get discouraged because they weren't exactly sure how to use it correctly. Some of these clients didn't know how to begin. And some of them took training or read books on Power BI but got overwhelmed by the technical aspects.
Sadly, all the introductory Power BI books that have been published to date are tailored more toward technical individuals than business users. That's where this book comes in.
This book is intended to get you started on your journey with Power BI in a way that teaches you the methods and principles that the IT professionals know but are simplified so that you don't feel like you're switching careers in learning this tool! If I had to use technical terminology in this book, I worked hard to make sure that I explained it in a way that anyone working in a business or organization could understand.
This book is intended to help guide you in the best ways to use Power BI to help you build sustainable tools for doing business analysis that, in turn, will lead to insights. That's my goal. I intentionally do not cover all the numerous bells and whistles, which continue to grow monthly. The concepts that you'll learn here have stayed the same since nearly the very beginning of Power BI. While Microsoft will continue improving this product, what you learn here will still apply for years to come.
WHAT DOES THIS BOOK COVER?
This book is divided into three parts.
Part 1 starts with an introduction to Power BI as a tool. In Chapter 2, I lay out three guiding principles that should shape how you think about business intelligence and visual analytics (terms which we’ll define in that section!). One of those guiding principles is a proven method that professional data engineers use and is important to follow when using Power BI.
Part 2 provides detailed, step-by-step walk-throughs of using Power BI. In Chapter 3, we'll look at the general features of Power BI and start playing with it immediately. In Chapter 4, you will jump right into creating your first report. From there, we'll apply that proven method discussed in Chapter 2 to the content in Chapters 5 and 6. In Chapter 5, we'll talk about how to prepare data to be useful for doing analysis. In Chapter 6, you'll learn a technique to ensure that your data is arranged in a way that makes sense to business users like yourself but is also optimized for working well with Power BI.
Part 3 is about taking you deeper into Power BI features. In Chapter 7, you'll learn all about relationships in data (not dating) and how Power BI can help you slice and dice data. Chapter 8 walks through how to add more sophisticated features to your reports to make them extra engaging. In Chapter 9, we will look at how to share your Power BI content with others, how to keep your reports automatically refreshed with recent data, and package up your reports for different audiences. In Chapter 10, we'll cover the most technical content of the whole book—a formula language similar to Excel's formula language called Data Analytics Expressions (DAX). Chapter 11 concludes with resources for learning further about Power BI.
WHO SHOULD READ THIS BOOK
As the title states directly, this book is intended for business users. By “business users,” I mean pretty much anyone not in IT departments. This could be an actuarial in insurance, a department head at a college, a chief financial officer of a nonprofit, a business analyst for a manufacturer, an administrative assistant in a law firm, or anyone else you can imagine. It's for those who use data every day in some way to do their job, and probably with Excel. If there is any prerequisite to using this book, it might be that you have a basic understanding of how to use spreadsheets.
Although this book is intended for business users, IT professionals can still glean something from it. In this book, I have introduced the technical principles I have learned over the years in data analytics. If you're in IT, but in a completely different field, this may be a great introduction for you to the world of business intelligence!
COMPANION DOWNLOAD FILES
The first several chapters of this book do not require any additional downloads other than installing Power BI Desktop. However, as the book progresses, there are some data files and sample Power BI files that will be helpful to you. These items are available for digital download from https://beginningpowerbi.com/resources. Look for the BPBIResources file, which contains all the materials from the book in a single zip file.
PART 1Introduction
Chapter 1:
Introducing Power BI
Chapter 2:
From Data to Insight
1Introducing Power BI
In the “old days,” the kinds and sources of data available to us in the workplace were limited. Making data-driven decisions was a process of gathering enough reports, synthesizing that data into spreadsheets, and pivoting that data into various ways of slicing and dicing until you reached some level of clarity to proceed with a decision. Some folks were lucky enough to have data warehousing tools to assist them with that process, but more often than not, even they had to wrangle that data from the warehouse and pull it all together into multiple tables and charts side-by-side to try to make heads or tails of what was seen. And if someone had the audacity to ask, “What would happen if I changed this one variable?” or “How does this data category impact this other category?” then you'd be spending another week reloading, rearranging, and reformatting the results.
But things have changed dramatically even in the last decade. The simultaneous growth of processing power and data availability has been accompanied by applications that are designed to leverage that explosive growth. Data analysis tools now easily enable exploration of large volumes of data and provide a dynamic experience where one can quickly visualize, “What would happen if I change that one variable?” or “How does this data category impact this other category?” Enter Power BI: Microsoft's game-changing data analysis service.
NOTE All those efforts, tools, and infrastructure to gather data from the various sources that are available in order to provide good insights for making business decisions is what the industry calls business intelligence (BI).
WHAT IS POWER BI?
Microsoft describes Power BI like this:
“Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Your data may be an Excel spreadsheet, or a collection of cloud-based and on-premises hybrid data warehouses. Power BI lets you easily connect to your data sources, visualize and discover what's important, and share that with anyone or everyone you want.”
https://learn.microsoft.com/en-us/power-bi/fundamentals/power-bi-overview
Power BI has three primary components: a Windows desktop application, an online service, and a mobile application.
The desktop application is used to create reports and build datasets (from which reports can consume data). While Power BI reports can be created online in the Power BI Service (more to come on that in a little bit), Power BI Desktop (see Figure 1.1) allows you to take advantage of all the features available in report creation and data modeling (Chapter 6 will cover what in the world that means).
FIGURE 1.1: Power BI Desktop
HOW DO I GET POWER BI DESKTOP ON MY COMPUTER?
There are two primary ways to install Power BI Desktop: through the Microsoft Store (https://aka.ms/pbidesktopstore) or through the Microsoft Download Center (www.microsoft.com/en-us/download/details.aspx?id=58494).
The advantage to using the Microsoft Store is that your desktop version will continue to stay updated to the latest version. Microsoft releases a new version of Power BI Desktop every month with new features or enhancements as well as bug fixes. Staying on top of the latest version ensures you have the best experience using Power BI.
The downside to staying up-to-date on the latest version would be if you are working with another person who is unable to use the Microsoft Store and has to use the Download Center; in that case, if you used a new feature that came out this month, published a report using that feature, and then asked your colleague to work on that report, they would be forced to update their installed version.
You may wonder, “Isn't that an odd situation? Shouldn't everyone just stay up-to-date on the latest version?” And you wouldn't be out of bounds to think that—it is a best practice to use the Microsoft Store version so that you can stay up-to-date. However, I have found that many of our customers work in large enterprises that have what are referred to as managed desktops. This means most of the users do not have the ability to install software on their Windows computers. In those situations, the central IT department manages which version of software is available. In this case, the IT department would indicate which version of Power BI Desktop they would want to be used.
You might also be wondering, “Wouldn't my colleague and I automatically have the same version then?” You wouldn't be off your rocker to think that! Again, however, I have run into scenarios where some individuals have elevated privileges on their Windows computers and some of their colleagues do not.
So, the first lesson to be learned is: take some time to learn within your organization what version of Power BI Desktop they would prefer you to use. And…ask to use the Microsoft Store version! The second lesson would be to consider whether you'll be working within a team of people who will all be creating Power BI reports and talk about how you'll plan on staying in sync and which version of Power BI Desktop you'll use as a group.
With Power BI Desktop you will import data from your different sources, arrange it in ways that make business sense, and then create reports on top of that imported data. (You won't always have to import data, but in this book that is the approach we will take.) Once you have a report in a state that you think is useful enough to share, then you will publish that report to the Power BI Service (see Figure 1.2).
FIGURE 1.2: Publishing to the Power BI Service
The Power BI Service is a cloud-based service (sometimes you'll hear it referred to as software as a service, or SaaS) that serves as a central repository for all of your reports and datasets and those others have created in your organization. The Power BI Service's main job is to serve up the reports that you published. The service enables you to share content with others. Here you'll organize your reports and datasets into workspaces. While workspaces give you the ability to organize like-for-like, more importantly, they give you a space in which to collaborate with other people developing reports with related content. Furthermore, workspaces give you the ability to say who and how others can access your reports. This is called governance. While it's great that you can share your insights and allow others to gain insights, you always have to think, “Who should have access to reports and data in this workspace, and what should they be able to do in this workspace?”
The third component of Power BI is the mobile application (see Figure 1.3). Unlike the Desktop version, there is a version for iOS as well as Android. The mobile application is similar to the online version but behaves much like you would expect a mobile application to behave and has a modern look-and-feel. When you create reports, you are able to create a separate view of the same report as it will be displayed in the mobile application. This means that it will fit well into a mobile format.
FIGURE 1.3: Power BI Mobile
There are three kinds of individuals who interact with Power BI: report creators, report consumers, and Power BI admins, as shown in Figure 1.4. We will skip talking about Power BI admins, because that is beyond the scope of this book, but just know that there is a wide array of options available to your organization to manage and control the use of Power BI.
FIGURE 1.4: Report creators, report consumers, and Power BI admins interact with Power BI
Report creators are those who, well…create reports! Presumably that would be you. Report creators can fall into two categories: business users and IT professionals. IT professionals are sometimes referred to as data engineers. These are individuals who get paid to do this! Data engineers are familiar with writing sophisticated code to extract data from a plethora of sources, bringing that data into a centralized location (maybe in the cloud in a data lake or into a database on a server located in an on-premises data center), and then arranging that data so that it is readily consumed by business users. They often will also make very complex reports that retrieve this data and present it in a Power BI report or with some other reporting tool. On top of all this, they often are dealing with volumes of data on a scale of not just millions of rows but possibly billions of rows!
You, on other hand, may have a whole set of responsibilities that make up your job, and just one aspect is figuring out how to wrangle the data you work with into something that can help you or someone else make heads or tails of the data you use on a daily basis. What is interesting, though, is that the methods (and consequent skills) you will learn in this book are based on the same proven methodologies that those data engineers use day in and day out. That should give you a level of confidence as you proceed down this journey with Power BI!
That other group of individuals, report consumers, are your customers. They may be customers internal to your organization, like your boss! Or, they may be external customers. They are the ones for whom you are making Power BI content to consume to gain insights and make decisions. Frankly, you may be your own customer! You may be wanting to learn Power BI just so that you yourself can glean insights in better ways than you have before.
But then again, you may still be wondering, “Why would I use Power BI?” or “How do others use Power BI?”
WHY WOULD I USE POWER BI?
The ways Power BI is used by thousands of organizations around the world is innumerable. But if you're new to Power BI, it might be difficult to begin to see how you might use it. In my years of consulting with many customers, I have seen Power BI used in many creative ways. Some of these have involved sophisticated data extraction routines and leverage enterprise data warehouses and even artificial intelligence capabilities. But I'd like to describe three common usages. These are three real-world scenarios that involve using the same tools and methods that you will learn how to use yourself within this book.
An international gas supplier maintains a Google spreadsheet of a couple thousand contracts including the terms and lengths of the contracts. They want to be able to know when these contracts are coming close to the time they need to be renewed. They also want to know how much that customer normally purchased. That info was not stored in the spreadsheet, but in SAP. With Power BI they were able to bring both sources of data together and not only be able to visually demonstrate which contracts would need to be renegotiated within the next 90 days but were also able to prioritize those based on their sales volume.
A state-based education center exists to ensure success for all students. They collect student test data as well as training records for staff. There are more than 300 attributes that need to be tracked and correlated across multiple aspects. This center imported their collected data into Power BI and was able to identify patterns and areas of concern across the state, by school districts, or within specific schools.
An HVAC distributor has sales goals for their account reps. These goals exist at the regional level as well as for each rep. This company connected Power BI to Salesforce and was able to retrieve sales data, goals, and account information and was able to create custom dashboards that display actual sales performance against goals by day, month, quarter, and year. In addition, an account rep can quickly see which are their top customers and only their customers, but their regional manager can see which are the top performing account reps and the highest volume purchasers across the region. And while the regional managers are restricted to their regional data, the vice president of sales is able to see all data across the regions and is able to drill down into the lowest level of sales detail.
BUT WHY?
You may be reading this book as someone who has been using Microsoft Excel for years and have figured out all kinds of ways to push its analytics capabilities. And to be sure, Excel makes the business world go round. Powerful functions such as VLOOKUP() or INDIRECT() combined with pivot tables and macros make Excel's limitations almost seem to be nil. But there are plenty of limitations on Excel that have brought many customers to the breaking point, and they end up calling consulting groups like the one I work for and pleading with us to come up with a more robust solution. Let me give you a couple examples.
An injection-molding manufacturer has several plants around the country. Each location has a financial controller responsible for gathering month-end accounting and financial data as well as other important metrics that the company wants to track. Each controller uses a template Excel spreadsheet that corporate provides and requires the controller to fill out and submit. The controller will post general ledger data for the month as well as enter the metric data. The Excel template has prebuilt pivot tables that aggregate the data by accounts. Once the controller has finished their prep work, they execute a macro stored in the template, which will further manipulate the data and copy it to a central file location. At corporate, another controller will utilize an Access database, which connects to all of the submitted Excel workbooks, extracts the data, and then lands the data in a parent Excel workbook. That parent workbook then arranges the data further for different levels of reporting needs. The workbook is quite sophisticated in its use of advanced Excel formulas, but the whole process is fraught with potential for human error. Furthermore, the company came to us asking for help because the process was grinding to a halt since the Excel workbooks were hitting size limits and experiencing significant performance issues.
A health insurance company received monthly actuarial data from an outside firm and would use Excel to marry that data with their claim data. They had to upgrade to the 64-bit version of Excel to accommodate size restrictions, but they also began running into other challenging limitations. For them to prepare monthly reports for executives, they would take several days for data manipulation and to update formulas across several pivot tables that needed to be synchronized side by side. Instead of putting their efforts into analyzing the data they had at their fingertips, they paid their data analysts to spend 25 percent of their month just preparing data for analysis. This was alongside all their other job responsibilities.
Beyond the intricacies of managing complex reporting processes, there are other limitations of Excel. These limitations might not have occurred to you because you've just learned to live with them. I'm thinking about two areas: disconnected data and fixed formulas.
Disconnected Data
Imagine you have built a complex Excel workbook that has multiple related worksheets of pivot tables of data. Then on one worksheet you have multiple visuals bringing together the data from your different worksheets. And let's imagine you have a couple of charts and a handful of tables that display data from the different worksheets. If you wanted to filter out just one aspect of data that is related to each of the visuals and tables, you would have to go to each pivot table or visual and find the related data and use Excel's Hide Selected Item function (see Figure 1.5). Naturally, as you see the impact of your filtering, you'll analyze whether there is any impact by removing other data. Consequently, you'd repeat the cumbersome tasks of filtering throughout the workbook. Your worksheets of data are related to each other in a logical way in your head, but they're not connected in a technical way such that Excel “knows” they're connected.
FIGURE 1.5: Excel's Hide Selected Item function
Power BI addresses this challenge with ease. First, in the previous example, the data in those worksheets are related to each other in some way, logically. In Power BI, those worksheets would be represented as tables, and those tables would have relationships defined between them. By you simply telling Power BI how those worksheets/tables relate to each other, you enable Power BI to do dynamic filtering across those related tables all from one spot. In the previous scenario, you would simply “exclude” the data as you see it on your report page, and it would automatically filter the data across all the related tables.
Fixed Formulas
A second common limitation of Excel is not as much a limitation as it is an impediment. Excel has the ability to create incredibly complex formulas using statistical calculations or aggregations that would take much manual effort to produce. However, once you create a formula to perform such calculations, you create it where you want to use it, and that formula is a fixed formula that can be used just in that cell. If you want to repeat using that formula in a different location in your workbook, then you must copy and paste the formula where you'd like to use it and then update the formula's cell references every time you want to reuse that formula.
Power BI addresses this encumbrance with a formula language called Data Analysis Expressions (DAX). If you're comfortable with writing Excel formulas, you will really come to love the DAX language. In the previous example, you would write the complex formula one time and use that formula wherever you need to without having to adapt it for each location. As Rob Collie has called it, they're “portable formulas.”1 Furthermore, if your formula is one of those formulas that your colleagues say, “Hey, I want to use that formula in my report!” then with Power BI you can just share your dataset with its great formulas in such a way that others can build their reports using those formulas. So not only is your formula portable within your dataset, but your dataset with all of its amazing formulas is shareable as well!
We've seen a handful of reasons why Power BI will take your report building solutions to the next level. I want to show you that Power BI will not just improve your ability to build reports but will transform your work.
Ten Reasons Why Power BI Transforms Your Work
These are the reasons:
Security, security, security
. Power BI gives you the ability to make sure that only the right individual or group has access to your reports. In addition, Power BI gives you the ability to control within a report
what
data they can see. For example, you can control whether one salesperson can see only their regional data. There simply is no way to implement this kind of security inherent in Excel.
Exponential storage capabilities
. If you've spent much time working with Excel building very large workbooks, sometimes with millions of rows, you know that when you need to make a change, sometimes you need to go get a cup of coffee while you wait for it to finish applying your change. With Power BI, as long as you design your datasets with the methodology I'll teach you in this book, you will see the file size of your Power BI reports be exponentially smaller than the size of your Excel files (or other sources of data). I recently was able to import two database tables with a combined 30 million rows that consumed 20 GB of storage. Power BI compressed that 20 GB to 45 MB: a percent change of 99.76!
Faster performance with your large datasets
. Not only will you see incredibly smaller storage size on your files, but when you follow the methods I'll teach you in this book, you'll also see insanely fast performance compared to that very large Excel workbook. This small storage size and fast performance is a result of the way Power BI is designed to store and operate on data.
Data sources just keep coming
. If you have used Excel's Get Data feature (see
Figure 1.6
), you know that the list of sources from which you can retrieve data is quite extensive.
But with Power BI, the list of sources available continues to grow monthly, far surpassing what Excel and many other reporting tools provide. Just check out this page from Microsoft, and you will see that the list is very impressive: https://learn.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources.
Collaboration
. With Excel, if you want to share your files, you can place them into a network-accessible location or use something like Dropbox or SharePoint, but collaborating on a spreadsheet is not simple. Power BI excels at collaboration allowing you to organize your files into workspaces and determine who can work in that workspace with you and how they can use that workspace.
FIGURE 1.6: Excel's Get Data feature
Your Excel files don't have to go away
. It's quite likely that you have several Excel workbooks that drive your day-to-day business. Those workbooks can be the basis of source data for your Power BI reports. So all that effort you have fine-tuned into a science to prepare for month-end reports does not have to go away. You can connect Power BI to that month-end Excel report and use it to give your users a whole new level of analytical insights.
Refresh your datasets while you're away
. Once you publish your Power BI Reports to the Power BI Service, you'll have the ability to schedule that report to refresh its data automatically. That means your report will contain data that you import from some source. That source may be a file, a database, a web service, or something like that. If you schedule your report to be refreshed automatically, this means your report will be able to go out to the original source(s) of data and automatically retrieve and import your data. If you're used to working in Excel, this is like you opening your monthly report and copy-and-pasting new monthly data into your report from other sources—except now it's automatically done for you.
Subscriptions and alerts
. Beyond just getting your data refreshed, once your data is refreshed automatically, wouldn't it be great if people could set up subscriptions to receive your report with the refreshed data? And what if you had a particular business metric that you want to monitor to make sure you know when it changes below or above a certain level (like making sure you know when stock levels get too low)? Power BI does both of those things for you! Subscription and alerts let your users control for themselves when they receive automatic notifications about your data.
Visually appealing analytics
. One of the things that often drives people to Power BI is the wide array of visually stunning ways of presenting data to your users, ways that far surpass the abilities that Excel provides. In addition to the built-in visuals (such as interactive charts, tables, and graphics), Power BI provides a marketplace where you can find third-party visuals that others have built using the tools that Microsoft provides to create custom visuals within Power BI.
Basic AI capabilities
. Power BI also introduces some built-in artificial intelligence (AI) capabilities. Features such as key influencers, decomposition trees, anomaly detection, natural language querying, trending and time-series forecasting, and automatic insights give you the ability to leverage serious computing horsepower to drive deeper insights into your data.
We won't cover all of these features in this book since we're all about beginning Power BI here, but know that these features of Power BI truly are transforming the way people work with data in their day-to-day.
WILL I STILL USE EXCEL?
You may be wondering at this point whether you'd ever need Excel again or maybe that I'm arguing that Excel should go away, but that is not the impression you should have. Excel will likely continue for many years to be the de facto standard for businesses as the spreadsheet tool. I want to briefly show you four ways you'll still continue to use Excel.
While Power BI can most certainly handle complex math formulas in its DAX language, you will find that Excel is conducive to quickly building complex mathematical calculations in stepwise fashion and seeing those results immediately as you work on your formulas. In fact, you may use Excel as a scratchpad to determine how you want to build your DAX formulas in Power BI.
Excel is still the tool of choice if you're needing to do manual data editing of simple file data (like comma-delimited files or text files, often called
flat files
) or of Excel files that you use as the data source for your Power BI reporting. Once data is imported into a Power BI dataset, you are not able to directly edit that data. So, if your data comes from something like flat files and you have a need to update that data before it gets imported, then Excel is an excellent choice.
