Aspin, Adam - Pro Power BI Desktop-Apress (2018).pdf

| Pro Power
| Bl Desktop

Interactive data analysis and
visualization for the desktop

Second Edition

Adam Aspin

| Apress

Pro Power BI Desktop

Second Edition

Adam Aspin

Apress*

Pro Power Bi Desktop

‘Adam Aspin
Stoke-on-Trent, Staffordshire,
United Kingdom

ISBN-13 (pbk) 978-1-4042-3009-5 son.
np /dokong/10.1007/978-1-1012:210-1

troie: 9781-4042-3210-1

Library of Congress Control Number 2017962307

Copyright© 2018 by Adam Aspin
‘Thisworkis subject copyright A ight ar reserved by the Publiez, whether the whol or partof he
materials concemed, special the right o ranslao, reprinting reuse allusions recta,
treadeating reproduction on microti orn any ther physical way, and transmision or information storage
snd reievaleectroni adaptation computer sofware, orby similar or dissimilar methodology nowknown ot
hereafter developed.

‘Trademarked names logos, and images may appear in ths book. Rater than use a trademark symbol with
every ccrurreneeofa trademarked name logo, or Image we use the names logos, and mages oy nan
dora fshion and othe beneft ofthe ademarkowmer with no intention ofifingemen ofthe trademark

‘The use inthis publcaonoftade names ademas Sercemars andsimlar terms, even they are
note as suc, isnot tobe taken as an expression opinion ast whether or not they are slt 1
proprietary righ.

While the advice and informatonin this book are believed to be true and accurate at the date of publicaon,
neither the author norte editors nor the publisher can acepta legal esponsibility forany errors Or
missions that may be made. The publisher makes no warranty, expreso imple, with respect dhe ate
contained here.

‘Cover image designe by Frepik

Managing Director: Welmoed Spa
Editorial Director: Todd Green
Acquisitions Editor: Jonathan Gennick
Detelopment Editor: Laura Berendson
"Technical Reviewer: Chad Schuessler
Coorinatng or: Balzano
(Copy Bator Bill MeManus
CCompostor: Si Global,

Indexer SP Global

‘Artist: SP Global

Distributed 1 the book trade worldwide by Springer Science Business Media New York,
233 Spring Sweet, 6th Flor, New York NY 10013. Phone 1-800SPRINGER, fx (201) 348-4505, e-mail
ovders-nyespringer-sba.cos, o isk wo. spr ingeron ine. cos. Apress Media, LLC is California LLC
and the sole member (owner) Is Springer Science + Business Media Finance Inc (SSBM Finance Inc).
SSEM Finance Incisa Delaware corporation.

Forinformation on translations, please e-mail ht apres cor, or sit http://hoo. press. co
Fights-pemissions,

‘Apres titles may be purchased in blk for academie corporate or promotional use. eBook versions
and licenses are also available fr most tides. For more information, reference our Print and eBook Bulk
les web page at http: 7/10. apress.con/bulk-Sales.

Any source code or other supplementary material referenced by the author in bis book. available to
reader on GitHub ia the books product page, located at es. apress.coW /9783484232095. For more
‘etaled information, please sit http: / nw. apress.con/source-code.

Printed on acid fe paper

To the memories of my mother and grandmother.

‘About the Technical Reviewer
‘Acknowledgments...
Introduction,

Chapter 1: Introduction to Power BI Desktop
The Microsoft Self-Service Business Intelligence Solution.
Importing Data rom Diverse Sources
Modeling Your Data.
(Creating Report and Dashboards.
Power BI Desktop Fes.

The Power Bl Universe
Installing Power BI Desktop
Removing Power 8 Desktop
Running Power BI Desktop.
A First Power BI Desktop Dashboard.
Loading the Source Data...
The Data Load Process.
‘The Navigator Window.
‘The Navigator Data Preview
Modiing Data...
The Power Bl Desktop Window

ES

Refreshing Data from Databases and Data Warehouses.

etrsting the Ene Data in the Power B Desktop -Memory Mode. 108
Refreshing an Individual Tabl... 109
Conclusion...

Chapter 4: DirectQuery and Connect Live.
DirectQuery and Connect Live.
Microsoft SQL Server Data
SQL Server Analysis Services Dimensional Data.
‘Microsoft SOL Server Analysis Services Tabular Data Sources.
DirectQuery with Non-Microsoft Databases...
DirectQuery and In-Memory Tables.
DirectQuery and Refreshing the Data.
Conclusion,

111

Chapter 5: Loading Data from the Web and the Cloud.
Web and Cloud Services...
Web Pages.
nine Services...
Microsoft Azure.
Web Pages...
‘Advanced Web Options.
Table View or Web View...
Salesforce... 0
Loading Data from Salestorce Objects.
Salestore Reports
Microsoft Dynamics 365.
Google Analytics.
OData Feeds
‘Data Options.
Azure SOL Database...

si

ES

Font Sizes in Tables. —

‘Changing Column Wits.

Sorting by Column.
Formatting Tables.
Table Style...
‘Adding and Formatting Titles.
‘Modifying the Table Background,
Table Borders.

‘conditional Text and Background.
Conclusion...

Chapter 15: Matrix and Card Visuals...
Creating a Matrix.
Creating a Row Matrix.
‘Adding or Removing Subtotals in a Mati
Column Mar 7 7
Expanding and Driling Down and Up
The Data D DO.
Displaying Data at te Previous Love
Displaying Data ora Sublevel.
Dring Down at Row Lev
rit Down Using lck-Through. =
ring Down at Column Level

461
462
464
465
466
..467
..467
...468
..469

ii

ne 502
xix

ES

‘Streamgraps.
Tornado Chats
herd Charts.
‘Sankey Diagrams...
Correlation Pats...
‘countdown.

Custom Stivers
Conclusion.
Chapter 19: Maps in Power BI Desktop.
Working with Bing Maps.
Creating Maps in Power Bl Desktop..
Using Geographical Data.
‘Geographical Data Types
Drilling Down in Maps nu
Adjusting the Map Display in Power Bl Desktop
Positioning the Map Elements.
Zooming Ino ut
‘Mutivaue Series.
Color Saturation. =
Highiging Map Dal
Filled Maps..
Shape Maps
Map Keys. 7 7
‘Adding Shape Maps. sn
Formatting Maps.
Maps,
led Maps...
Tooltips.
Shape MAPS z

geeekeeeee

en

ARCGis Maps
(rating an ARCSIS Map... 602
Selecting a Basemap Type. ‚os
Seletng a Location Type
ding a Map Theme
Choosing a Symbol Type.
‘ing Pins
ing aReteronce Layer Pa . 607
ing Infographies. 60

Conclusion.

Chapter 20: Filtering Data .
Filters.
Visual-Level Filters.

Te Firs Wella
bing Fiters ……
‘Aoplying Fits.
“The Select A Fe
ClarngFiters
Filtering Different Data Types.
Feng Numeric Data.
Feng Date and Time Data
Fitering ue o False Data.
Advanced Text Filters.
‘Aoplying an Advanced Tot Fite.
‘tearing an Advanced Fite.
Reverting to Basie Filtern …
‘Text iter Optio.
Specific Visualization-Leve Fit
Multiple Fitters
Page-Level Filters.

ES

Report-Level Filters
Removing Filters
Filter Field Reuse
Requiring Single Selection
Using the Filter Hierarchy..
Filtering Tips.
Dont Fiter To Soon.
‘Annotate, Annotate, Anata.
‘void Complex Fier.
Conclusion...

Chapter 21: Using Slicers..
Slicers.
Adding a Sicor
Apphing Sices..
Glearing a Sicer.
Deleting a Stier.
Modiying a Sie...
Date Slicers.
Formatting Slicers..
Sr OE nn i
Modiying ne QUO 7
‘Adjusting Selection CO. sa
Setting the Exact Size and X and Y coordinates of a Sr.
Formatting he Slicer Header
Formating Slce tems...
Sorting Ser Elements.
Sutehing to Dropdown SIS...
‘Exporting Slicer Data. E
Using Charts As Slicers.
Chart ASS Bun .
ighigting Chat DA nn sé
xiv

geeESeeee2k8 22228328282

aghaaanee

ES

Images.
Image SOUS u a E
‘Adding an Image. mess 7
‘Removing an image.
Resiing Images.
Formatting Images.
Background Images,
‘Some Uses for Independent images.

Adding Shapes
Formatting Shapes
‘Removing Shapes...
tandardizing Shapes………. 7

Organizing Visuals on the Page..

eeeeeeseasee

‘The Source Page.
‘The Destination Page.
Apphing Drithrough..

Exporting Data from a Visualization.
Conclusion...
Chapter 23: PowerBl.com.
Publishing Reports to PowerBl.com..
Creating a Power Bl Account
Using Power BI Desktop Files in PowerBl.com
Logging Onto PowerBlcom .

‘Adding a Power BI Desktop Fle. 73
Publishing a Power Bl Desktop Fe. 0 T4
Dashboard, Reports, and Dataset. si ns

with a Report on PowerBl.com.
Printing PowerBl.com Reports. 716
Working with PowerBl.com Reports
‘The PowerB.com Report Menu.
Report ating,
siting Edit Mode... q
Adding Datasets to PowerBl.com.
Creating New Reports in PowerBl.com
PowerBl.com Dashboards.
Adding Tes to PowerBl.com Dashboards.
Pinning Entre Reports.
Edting Dashboard Tes. ra
Modifing Dashboards.
Powerël Pro...
Sharing Dashboards.
PowerBl.com Gateways.
Downloading a Gateway.
Configuring Data Sources.
Applying Dataset Settings.
Manual Data Refresh...
Scheduled Data Refres!
PowerBl.com on Tablets and Smartphones.
Conclusion...
Appendix A: Sample Data.
Sample Data……
Downloading the Sample Dat...
Images 169

About the Author

‘Adam Aspin isan independent Business Intelligence consultant based inthe United Kingdom. He has
‘worked with SQL Server for over 20 years. During hs time, he has developed several dozen reporting and
analytical systems based on the Microsoft Bl product suit.

‘A graduate of Oxford University, Adam began hls carer in publishing before moving nto I: Databases
soon became a passion, and his experience in this arena ranges from Access to Oracle and MySQL, with
‘crasionalvortes into the word of DB2 He's however mot at home the Microsoft universe when sing
SOL Server Analysis Services, SQL Server Reporting Services SQL Server Integration Services, SharePoint,
and Power

‘Business Inceligence has been his principal fous forthe last 5 years. He has applied his skis for a
range ofellents in industry sectors rom Finance to lies, Telecoms o Insurance and Manufacturing o
Banking.

‘Adam is a requent contributor to SQLServerCentraLcom and Simple-Tlk. He has writen numerous
articles for various French I publications À uen French speaker, Adam has workedin France and
Switzerland for many years. He speaks regularly at evens such as SQLBIS SQL Saturdays, and local SQL
Server usergroups.

Hels the author of SQL Serer 2012 Data Integration Recipes (Apres 2012) Hig Impact Data
Visualiza in Excel with Power View 3D Maps, Get & Transform and Power BI (Xpress, 2016) and Busnes:
Intelligence with SQL Serer Reporting Services (Apres, 2015)

xx

About the Technical Reviewer

Chad chuessler san experienced database consultantspeciaizingin
Busines IncligenceI addition o many programming languages.
Chad has worked witha wide range ol database technologies over the past
22 years including Informix, Oracle, DB2 MySQL, and Sybase. Microsoft

\ ‘SQLServer however has been he primary lar he has used sine 2000,
implementing solutions across Manufacturing, Health Car, Real,
Financial Services, Automotive, and Energy

‘Being dual Amerlcan-Brtish ein, Chad hasbeen able topan

international experience working extensively in both America and
England,

M

Acknowledgments

ing technical book can be a lonely occupation. So Lam all the more grateful for ll he help and
encouragement that have received rom so many fabulous fiends and colleagues.

"Rist my considerable thanks gto Jonathan Gennick, the commissioning editor of this book.
‘Taroughoutthe publication process Jonathan has been both atower of strength and an exemplary mentor
He has always been avallable o share his vast experience soles and courteous

Heart thanks ot il Balzano the Apress coordinating editor for managing this book trough the
Production process. She succeeded—once again—in the wel nigh impossible ask of makinga potentially
stressfilledtekintoa pleasant journey ed with ight and humor. He eam also deserves much praise for
{heir eal under pressure.

one a deep debt of gratitude to Chad Schuesler who reviewed his ook fom a technical standpolt.
He took fartoo many hours away rom hs young family to go through athe detal in the book and provided
plethora of valuable insights and recommendations His ast experience of Power BI vas invaluable.
‘Thanks Chad:

My thanks also goto Bl MeManus for his css and sube work editing and polishing the prose.

(Once again, my deepest gratitude Is reserved forthe wo people who have given the mostto thls
book. They are my wife and son. Timothy has put up with a mentally absent father for months while
nonetbeles proving continual encouragement 10 persevere, Karine has not only given me the support and
‘encouragement o persevere, but also the love without which nothing would be wor any efor. Lam avery
lucky man to have both ohm.

Introduction

‘Busnes Intelligence has become one ofthe buzzwords that defines an age. Isyounger sibling seservice
BI hasattained the sais ola hol ral for businesses. Managers wart thee stato deliver insight
seconds and users just want todo heirjobs quickly and to produce clea ing and accurate analyses with
tools that are tive and easy to se,

‘Microsoft recognized these trend and needs shor time ago when it produced the ist version of
Power BI Desktop. Moreover, Microsoftmade this amazing walk absolutly fee

‘Since ts auch, the product has grown beyond recognition, now allows anyuserto take data from
ral any source and use to produce stunning dashboards and compelingrepor that wil eze their
audience attention, Using his rapidly evolving oo, any user can slice and dice the data wih remarkable
use and then add metia instant analyses and PIS to project the insights that rete areal compete
advantage.

“This book showshov t deliver eye-catching Business Intelligence with Microsoft Power BI Desktop
Inteaches youhowto make raw data into lea accurate, and interactive information. The am ofthis book
{stohelp you to push your Bl deliveryto the mx ev In these chapters you willean to create grat
looking isualzaions and et your audience have fun by interacting with the elegant and visually arresting
‘utp that you can nove deliver You vil see how to choose from a wide range ofbult in and third-party
visualizacion types so that your messages always enhanced, You discover ways to deliver those results on
eon and en marins, ae een ihe dno Roly eto
save ime by preparing the underlying data correctly without needing an IT department to prepare
ower BI Den ville your anlyesspeak or themselves This book wil hep youlearahow o una
Asvartresources,

"you wish, you can wad thls book fom star to nish, as signe dt bea progressive trial
‘hat wil help yout leur Power BI Desktop. However as Power BI Desktop Is composed of several
{nterdependent functions, his book is broken down into groups of chapter that focus on the various
areas ofthe product.

+ Chapter inmoduces new users tothe product You can consider ita Kind of
“executive summary” of Power Bl Desktop.

+ Chapters2 through 5 show jou howto connect toa
bring ths data into Power Bl Desktop

+ Chapters through 9 explain how to wansform and ean daa so that ou ca use e
foranayss.

ago of varied datasources and

+ Chapter 10 introduces you tothe art of rating a data model on which youcan base
yourinteactive dashboards and reports.

+ Chapters) through 13 ar an inroduction to DAX: the Power Bl Desktop language
that you use to advance and talr your analysis. You wil use this language to create
he mere for your business nelligence.

‘ernoovenan

‘+ Chapters through 22 take you through the myriad posible that Power BI
Desktop offers to help you create stunning eporisand dashboards This covers
tables, charts maps, KP and many other types of nals

+ Chapter 25 explains how you can share you sights using PowerBLesm. This ithe
‘doud-based service avallable rom Microsoft tres colleagues and frends see and
Interact with your reports and dashboards.

‘This the second edition of Pro Power BI Desktop. tn the course of he year and a halfsince the st
cation, Power BI Deskop has evolved magistral. o new elon ofthe book was necessary to update
the original with ll he new and exci changes and extensions that have been added to te product.
Indeed he monthly ease cyl that Microsoft has maintained —and sl maintaning—Is proofof the
company’s dedicaion to improving an already outstanding product. This book includes the latest updates
dein September 2017.

his book comes wiha small se of sample data has used to create the examples that ar presented,
throughout the book. realize that it may seem paradoxal o use a dy data set or aproductiha can
handle tens of milions ofrows of data but prefer use a comprehensibe set ol soute data so that ho
reader can concentrate on whats being learned, rather han he dat sel,

Iris inevitable ua nor every question canbe anticipated and answered in one book. Nonetheless,
"hope that have answered many othe selservie Bl questions that you might encounter and. more
Importanty-have given you the approsches and the confidence to resolve mosto the Power Bl Desktop
challenges hat you might encounter when applying hs producto solve real-world problems.

Tish you goodluck in using Powe BI Deskiop and sincerely hope that you have as much fan wiht
as din ering bis book.

CHAPTER 1

Introduction to Power BI Desktop

you ae reading this book is probably because you need to use data. More special, you wantto
take journey from data through to insight where quant of facts and figures need tobe shaped into
‘comprehensible information and given clear and visual meaning, One you have coreatod andate
‘Sour analyses you probably want share them with colleagues or fends.

‘Tis book is ll about that journey. covers he many ways hatyou can transform raw data into
"lg. Impact analyses using Power BI Desktop the self service busines intelligence (BI) and analytes
application that Microsoft is making rely available This fresh approach to self service BI presumes
‘minimal central IT intervention or dependency. ts based on giving he ser the ably to ue simple yet
povterfl ool to handle induseal-strength quandes of data ando share sunning ouputin the shortest
possible timescales

‘The following are keywordsin this universe:

+ Speed
+ Delivery

‘+ Empowerment
+ Decentralization
+ Disimermediaion

‘Once you have mastered the tools and techniques described in his Book, you willbe able to
‘Discover sure and loud your data from a wide range ol sources
+ Add athe calculations you need 1 enhance information and extract accurate analysis
+ Create sts interactive presentations
+ Shareyourinsights wih yourcalleagues andellents

follows that hs book writen from the perspective ofthe user Essential, sal abou ing users
‘he tools and knowledge define their own requirements and sats ther own needs simply and efficiently
‘through developing new and existing kl,

"Tis chapter assumes that you have no prior knowlege of Power BI Deskop or even business
Intelligence. Consequently sar fom the very beginning by explaining what exactly sel servie business
intelligence ell Then takes you through the necessary stepsto dowmload and install Power Bi
Desktop. Finally, takes ou ona whilvind tour of Power Bl Desktop, where you see st how quickly and
sil can go rom raw data to polished insight using this amazing wel

you intend follow the dashboard example tha You findin this chapter (and hope that you il
‘hen you need to download te source material or his book fom the Apress web ite ase. 29 55
‘im/ 9781484232095. Append A describes how tod thls so hat you can prepare the terrain fr your
‘upcoming adventure with Power BI Desktop.

Adam spin 2018 1
A Aa Pro Power Bestop apr 1078: 1464232

GER = TRODICHE TO OER BEST

The Microsoft Self-Service Business Intelligence Solution

Itisimporantto understand from the tarthat Microsoft's el service business nllgenc solution, Power
Bi aconstnty evolving proces. Indeed is ina continuous state of ux. Fortunately, his perpeual
‘motions now ata peako readiness and while ts sll undergoing some enhancements and revisions is
ready for immediate use

‘Sowhateracty ls Power BI? Aris hearts a cloud-based sevice that et you store and share essential
business data inthe form of dashboards and reports. However, before you can share dashboard you need
Ko rate them and this where Power BI Desktop comesin.Thiseay-o-use tools completely fee is
sed in, cleanse, and mash up data so that you can then develop telling metres and deliver them inthe
form of stylish visualizations. Once your ables, chars, and maps ae assembled in report you can then
share them with selected audience in Azure the Microsoft Cloud (should you want woof cour). Yet,
the good news does notstop thee. Your publican view your insights on just about any Windows, 10S, or
‘Android device using the rc Power Bl apps that Microsoft has madeavallable for these platforms.

‘Sosll hat you have todo isto create dashboard wit Power BI Desktop share the output in Azure,
and then view and ineraet withthe resus using the Power BI apps. real is that simple. Moreover up
‘oa certain limiton le size, is completely re. In large corporate environment, you may even instal à
Power BI Report Server to make Power Bl reports and dashboards valle to employees without using the
Web to share them.

‘There fs more -much more in the Power Bl univers, bt his short description ll sulfito get
you started: Inany ease, Chapter 23 provides more detal on the way that Power Bl Desktop fs into the
Microsoft self service business intelligence solution. Inthe meantne les move he focus ack to Power
BI Desktop To begin what exactly wil You be using thls applcation for? There are three answers:

+import data
+ Model data

+ Greatereponts and dashboards

Lerstake a quleklook at some ofthe thing that these may ental

Importing Data from Diverse Sources

“Theft stp onthe path to delivering concrete business inteligence to ind and import alle data that
you need for your analysis. Power BI Desktop les you

‘+ Import data froma wide varity ofsources This covers corporate database to
desktop fle, sci media to bg data.

“+ Merge data rom multiple sources and shape it int coherent structure
‘+ Cleanse your data to make it relable and easy to use.
‘+ Breakdown he data ino the rowsand columns tha sul yourrequirements

‘There was atime when these tasks required dedicated teams of specialists. In fac, was considered
so complex that earned Ks own acronym, ETL, short or Exact, Transform, and Load. Wel this process
‘no longer needs speclaliss. With Power BI Desktop, youcan mash up You own data so tar ready 10 use
as part of yourself service BI solution.

"mportingand connecting 0 data discussed in Chapters 2 through 5. Mashing up (cleansing joining
and transforming) datas examined in Chapters through.

GER = TRODUCTENT OER SKI

Modeling Your Data

Power BI Desktop isnot Just a data store or our information. also es you extend and develop the
canseddara. More special, allows yoo.

+ Create adata modelby joining tables o develop a coheren data structure from.
‘multiple separte sources f dat. This data model en wed in dashboards.

+ Enrich he

model by applying coherent names and data types.
+ Createalculaions and prepare the core metiesthat you want © usein your
analyses and presentations

[kis wort noting that you can load data into Power BI Desktop diecy without going through the
ata cleansing and modeling stage. the source data area in good shape, then youcan str sing
straightaway.

‘Modeling data and adding calculations ts discussed in Chapters 10 through 13.

Creating Reports and Dashboards

"think of creating reports and dashboard as he “Jewel in the crown’ osel service business intelligence,
Atrly dynamic analysis and presentation approach les you deliver business intelligence composed of

+ Tables
+ Maries
+ Chan
+ Maps
+ Gauges
+ images
.om

and many other types alviualizaion with Power BI Desi.
Not on that bu is ncrdiy fst and highly ture provides advanced interactivity so hat you
and your users can “lie and dice” the data “onthe fy” rel ime using
+ Slicer:
es

‘Creating dashboards and reports discussed in Chapters through 22

Power BI Desktop Files

Power BI Desktop lets you create multiple pages ina single fle. Each collection of pages has based on the
same underlying data called a repr A Power BI Desktop fle therefore contains ll he dashboards and.
the data thas needed by each element (called a usualization) on each page. So, a Power Bl Desktop es
completly self-contained.

‘Power BI Desktops bull ro handle vast quantities of data. Fortunately, however, compress the
ara hat ou load in an extremely ecient way This means hat Power BI Desktop Mes often ake up only
‘fraction othe space that they would they contained only the raw source ata Indeed, when connecting
to cena data sources, you do notevenhave 1 loud and compress the data You can connect ec tothe
data and use t immediately.

GER = TRODICHE TO OER BEST

‘This compression also apples tothe data that Power BI Desktop uses when you are modeling data and
creating dashboards, This because Power BI Desktop loads al the data that you are using ino memory,
‘where ts compressed to make he most othe valable memory. This means hat Power Bl Desktop is
semer fat to use and normally shows you the resus ofany changes that you make or any ters hat you
apply infractions ofa second, This instantaneous interactivity also apples to dashboards that you display In.
‘Windows 108, or Android apps

The Power BI Universe

Power BI Desktopis one par fan integrated collection of products and services that are general known as
“Power BL This universe constant changing, but or he moment itis composed of he following:

+ PouerB Com: An Azur based service where you can create and share (depending.
on your subscription lve) data nd dashboards

+ Power BI Desktop: The core tool that you use to crete Power Bl repors. This can involve
‘connecting tomultipe daa sources and modeling and cleansing the source data.

‘+ Power BI Report Server: An on-premises Power Bl repor server that allows you to
dstebuteand deliver reports inside à corporate reall

+ Power B Mobile Apps: Apps thaallow you to Wew and inertie with
Power BI reports and dashboards on Windows, OS, and Androld devices,

‘+ Power BI Apps Apps that provide a method ofcllectingand deploying purpose
bull dashboard and reports for tor groups ofusersin the cloud.

“+ Power B Embedded: An Azure service that enables application developersto add
Interactive Power BI reports Into ther own applications

+ Power BI Gateway: Allows you to connect the Azure-based Power B service to
on-premises data sourees and automate data refresh.

‘+ Third partysuas A colleedon of sual elements, often created by hed pares.
You ean add these visuals t your Power Bl reports and dashboards.

would take an entire book to describe all the elements that make up the Power Bl ecsystem-and
this isnot that book So, while wil provide brit overview of third-party visual in Chapter 1, and an
Introduction 1 the Power BI Server, the Power BI Gateway, nd Power BI Apps in Chapter 2, Lean only
suggssthat ou keep close eye onthe Microsoft Power Bl web ste (1ttps://powerdi microsoft coe).
Keep abreast ofthe atest developments inthis fast-changing product universe.

Installing Power BI Desktop

"Te st hing that you have to do wo create dashboards (or reports or pages of visualizations) is download
and instal Power Bl Desktop. Although this process sel easy, you wil save ime you ensure that the
computer where you want to install Power B Desktop haste capability to run this application Current,
the minimum requirements area follows:

+ Windows 7, Windows Server2008 2 orlater
Internet Explorer 9 orgreater

+ Nes

“+ Atleast 1GB ofavallable RAM

GER = TRODUCTENT OER SKI

Note Power Bi Desktop Designer works on both 32- and 64-bit computers, However, if you intend to
‘analyze large datasets, a 64-bit workstation with several gigabytes of memory cut very wel prove necessary.

Microsoft specifls 1GBas a minimum memory requirement, but you need to be aware that although
the application selfs nota memory hog ican let you load huge amounts of data. Given tat al ofthis dara
‘win mos eases be loaded into memory, you need to ensure a you have enough avalable memory you
Intend to analyze large amounts of data The exception 1 his principles when you wll be using Power Bl
Desktop as “front end” to certain databases or data warehouses, and establishinga direct connection o,
these sources ethout loading any data

So, you are sure that your PC or laptop is ready for Power Bl Desktop you an instal iby following
these steps:

1. Gotothe Power BI Desktop download page on the Microsoftweb se.
‚Thisiseumeni https:/ un microsoft .con/en-us/domlond/details.
spa 4531. You can eas find the ight page by entering Power BI Desktop
‘download in yourfavrie search engine You shouldseea web page containing
Something ike the information shown in Figure I.

aos a

gos Pome E Det bat for acosta tercio vations
se anne spre.

Om
CE
© mann

Figure 1-1. The Power BI Desktop download page

2. Selectte language.

3. Glckthe Download button. You wll be taken tothe nex pag, where ou should
choose the ype of download (32-bitor bi) asshowntn Figure 17

GER = TRODICHE TO OER BEST

Choose the dounload you want

Figure 1-2. The download selection page

4. Click Next (this buton only appears once you have selected the ype of download
‘thatyou want). The inal download pages displayed. A pop-up appears. as,
shovemin Figure 1,

rennen = ae

Figure 1-3. Thesaveorrun download popup

5. ClickRun-The Power B Desktop installation package wil download (probably in
under minute) an te inal setup dalgís displayed, as shown in Figur 14
you do not ee this lalo once the download has completed, then cick the
toolbaricon (his will have appeared in he toIbarto make it shove on tp of any
‘other open windows).

ume oe Mat Po Bt
Dates Go Sean tee

O ow
Figure 1-4. The nial Power Bt Desktop setup dialog
6

GER = TRODUCTENT OER SKI

6. Clik Next Theserplicense agreement dialog willappear as shown In Figur 5

CS

me De CE eu

Figure 15. The setup lens agreement dialog

7. Ensure dat the check box accepting the cens agreement is checked and click
‘Nest. The setup destination dialog will appear as shoven in Figure |. Ifyou,
prefer to install the Power BI Desktop ls ina diferent directory, then you can
enter there (relickthe Change button and browse o select I)

w eo ow

Figure 18. The setup destination dialog

GER = TRODICHE TO OER BEST

8. ClickNext The final confirmation dialog wllappeae as shown in Figure 17

Pure 1-7. The sep nal confirmation dialog

8 ClickInsall-The Power BI Desktop installation package wilrunand will
‘complete the instalation in afew seconds. You may have to confirm that you
allow this application o make changesto your stem. You wl sea progress
log as showntn Figure.

Figure 1-9. The installation proses dialog

GER = TRODUCTENT OER SKI

10. Once the instalation process has inished successfully you will ee the
competion dialog, as shown in Figure 1-2

amped te Het ae a
Bed bo sep ard

= me
Figure 1-8. The final Power BI Desktop installation dialog

11, you wanttorun Power BI Desktop immediatly, then leave th Launch
Microsoft Power B Desktop check box ticked otherwise, uncheckit and click
Finish The dialog wil lose and Power BI Desktop snow installed on your
computer,

Removing Power BI Desktop

Should you exe want to remove Power BI Desktop fom a computer where has been installed, you have a
ple ofehoices:
‘Run the web-based instalation proces a described earlier. Atstep 4, you see
dialog asking you you want repair o remove Power B Desktop rom your
computer Click Remove and follow the process that indicated to delete the
application from your machine.

+ Open the Windows Control Panel. nthe Programs section, click Uninstal a
program. Select Power BI Desktop rom thelist of current installed programs to
tinal

Running Power BI Desktop
Once you hae stated Power Desktop successful youre ready at creating dashboards and
maya your dats, You can stan your Poner Dl Desaop experience allow

1. Gickthe Power BE Desktop icon that was created on the Desktop as part ofthe
instalacion process You wll se the Power BI Desktop splash screen, as shown
in Figum 1-10

GER = TRODICHE TO OER BEST

Power BI

Desktop Q

.-
CA

Sign into get the most out of
Power Bi

igure 1-10, The Power BI Desktop splash screen

Forthe moment however itstime to stop and draw breath. You have succesfully installed Power BI
Desktop andyow ae ready to create your fit dashboard with this exciting and revolutionary ppliation,

A First Power Bl Desktop Dashboard

This book takes you through an immense amount of detal hat explainshow to import cleanse and shape
data rom a mue of diferent sources, You then learn how to carry ou a varity of calculations that wil
Rep yu to tease ourthe meaning rom the data that you are analyzing, Finally, you see howto transform,
this analy into telling visuals ar make your insights inlvely comprehensible to your audience.

‘et before delving mo al ts deal, tls perhaps more importan to appreciate the really
Fundamental quais ofthis amazing application. Despite he depth and reach ofi piece of software,
there are other quals hat male and out and tht ae possibly even more fandamer

Simply. Anyone can lean to create stunning visualizations and carryout
naiss ef data withouthaving o endutea step or arduous learning curve

‘+ Power Data rom virtual any source canbe loaded, manipulated, and combined
With other data clement, and then modeled and extended without needing
advanced knowledge of T systems or data management.

‘Consequently tis important see ust how easy it st use the Power B Desktop dashboard. Indeed,
the fastest way to et you “hooked” on this particular tool tlt ou se for yourself how fastyou can go
from zero to heroin delivering compeling dashboards Sole'sse justhow quick and easy can boto take
datasource (an Escel fl in hs instance) and transform tinto a Power BI Desktop dashboard,

GER = TRODUCTENT OER SKI

Loading the Source Data

Once you have launched Power BI Desktop you are faced withthe startup screen that you sain
Figure 1-10. Given that you are working wth an application tat ives and breathes at, not really

surprising that the fit step Ina new analytical challenge so Bind and load some data So the allowing
‘explains what you have to do (assuming that you have downloaded he sample data that accompanies this
‘ook from the Apress web sitet explained in Appendix A).

.

{ck Get Dat in he startup screen. The Get Data dalog will appear aschown in
Figure 11

Gerona

Figure 1-11. The Get Data dialog

In thelist ofall he possible datasources on the right ofthis dilo, click Excel,
and then ick Connect. The Windows Open Fl dialog will appear

ck fe C: PowerBiDeskopSamples\CHON\CarSaes.Xlsx. The Windows
Open dal willoaklike the oe in figure 12

u

GER = TRODICHE TO OER BEST

ARE à Po ON
one

Store
ELA
Free

> mee
mo

E Gee
Figure 1-12. The Windows Open Fle dialog when loading data from al source
4. Clickthe Open bution. The Connecting dialog wi appeur fora second or suo
and then the Navigator dialog will appear

$5. You will se thatthe CarSaes xs le appears on the ef ofthe Navigator dialog.
and that any workbooks, named ranges or data tables that contains are also
listed. Click the BaseData worksheet mime that ison thele The contents of hs
‘workbook will appear inthe data pane onthe right he Navigator dialog.

6. Clickthe checkbox forthe BaseData workaheeton the left. The Load and Edt
‘butons wil be activated. The Navigator alg should look ke Figure 1-13.

12

GER = TRODUCTENT OER SKI

Figure 1-13. The Navigator dalog with data selected

7. ClickLoad The data willbe loaded from the Exe fle into Power Bl Desktop.
You wil see the Power BI Desktop report window, ke the one shown
InFigure 1-1

igure 1-14, The Power BI Desktop repor window
13

‘ETERS = ROUCO To FONEREN DESK

imagine that loading this data took under a minute, Ye you now have fully operational data model
In Power BI Desktop that ready or action. However before moving forward and creating. dashboard, 1
world like to pause for an instant and explain exact what you have seen so ar Ofcourse, youre ching
10 ace ahead and actual create à couple oftables and charts, in you can always jump ahead othe "our
First Visualization secon.

The Data Load Process

\Wharyouhave sen so farisanexremely apd dash through a Power BI Desktop data load scenario. In ely,
this proces can range rom the binding} simple (as ou us saw) othe more complex where you ol, er,
and modi multiple datasets rom diferent sources (as you wl discover in Chapters? tough 9 However,
loading data wil ahrays be the ist step in any data amis scenario when you are using Power BI Desktop

Inthis shor example, you nometheles saw many ofthe key elements ofthe data load process These
Included

‘+ Accessing aa thats avaablein any othe source formats that Power Bl Desip can ead
“+ Taking ist look the dat before loading into Power Bt Desktop

‘What you did notsee here i how Power BI Desktop ca add an intermediate stp tothe data load
process and eit the source data In Power BI Desktop Query Editor. This aspect of data manipulation is
covered extensivey in the following few chapters

The Navigator Window

One ey aspect ofthe data load process is using the Navigator window corey. The Navigator window
appears when connecting to many, but otal, datasources. ts there o let you

‘+ Take aquicklook atthe available data tables inthe data source
+ Bkermuliple data elements that are available ina single data source
“+ Look atthe data in individual tables

+ Setectone or more data tables toad nv Power BI Desktop

Depending onthe dat source o which you have connected, you might see only fe data ables in
the Navigator window, orhundredsofthem.In any case, what you can sear the structured datasets that
Power BI Deskupcan recognize and is confident that can Import Equal dependent on the data source
Hs the level of compleiy of what you wil seein the Navigator window you ae looking ta database
server, for instance, nen you may ar out wäh alist of databases and you may need to dig deeper nto the
rborescene ofthe data by expanding databases tls the avalable data tables and views.

‘ou wil see much, much more ofthe Navigator window in the folowing the chapters

‘The Navigator Data Preview

‘The Navigator Data Preview pane (on the rights asis name imple, preview ae data in a datasource,
provides

+ Abriefovervew ofthe top few records inanyofthe datasets that you ean to look
a Given that the data you are previewing could be hundreds of columns wide and
mins of tows ong, there could be seo bars for dhe data able sible nido the
‘Navigator Data Preview.

+ Allcofthe available columns inthe data table. These ae shown atthe botom ofthe
‘Navigator Data Preview.
1

‘Uren = TRODUTIONTO ROWER BESTOP

Power BI Deskiop can preview and oo data from several diferent sources, Indeed (asyou can see
Atom the ist of possible data sources in the Get Data dialog in Figur 1-1) team read mostof the commonly
sable enterprise data soures as wellas many, many others Whatis important to appreciates hat
Power Bi Desktop ppliesa common interface o the at and science of loading data, whatever the source.
So whether you ar examining SQL Serve or an Oracle database, an XML fle or a text fl, a web page or
big data source you wl alway be using standardized approach o examining and lading the data, his
makes the Power BI Desktop data experience infinitely simpler—and extremely reassuring means tht you
spend es time worrying about technical aspects of datasources and youre reto ocs on the data sell

Note The Navigator Data Preview ls brilla data discovery too Without having to lad any data, you
can ake a Quick ook at the data source and any data tha it contains tat can probably be loaded by Power Bt
Desktop. You can then decide it is worth loading so that you donot wast time on a data lad that coud be
‘superfuous to your needs.

Modifying Data

{Once you have one or more queries In Power BI Desktop that can conneetto data sources and bring the data
mo this environment. you an star thinking about the new sep transforming the datas that its ready
for se. Depending on the numberof datasources that you are handling and the exentof any modifications
‘ha are required ths could vary rom the simple o the complex. To give a process some structure, ads
that you ry o break dwn any steps into the following main threads:

‘Shape the dataset This coves ftering out recon to reduce he size ofthe dataset,
sell as emosing ny exrancous columns. may aso involve adding columns
{hat you crete by spliting existing columns, creating calculated columns, o even
Joining queres.

Cleanse and med he dat: This lso known as data transformation (he Tin
TL). emcompasses the process of converting text data o uppercase and lowercase,
aswellas (forinsance) removing nonprinting characters Rounding numbers and
rating date parts rom dat data are also possible (among many aer eventual
transformations),

Forthe moment however ts only Important o understand hat Power BI Deskiop an do al ofthis if
you need itt, Transforming data explained in detail Chapters 6 though

The Power BI Desktop Window

‘Before we go any further, would lke explain the Power BI Desktop window, since itis something that you
vw user inthis chapter rom this point onward The Power BI Desktop window contains he elements
‘ha are oulined in Figure 1-15

15

‘ETERS = ROUCO To FONEREN DESK

|

Figure 1-15, The Power BI Desktop
you can see he Power B Desktop screen is simple and unclutered. The various elements that it

contains are explained in Table |.

Table 1-1. Power B Desktop Options

Option. Description

Power BI Ribbon ‘Thiscontins the principal opsonsthat are avalableto you when
developing dashboards with Power BI Desktop.

View Type ‘These three icons letyou fp between Dashboard view (where you create
dashboard and reports), Daa view (where ou ad calculations, and
Relationships view (where you join data from diferent sources)

Dashboard Canvas ‘This the main ara, where you add visualizations and design your
dashboards,

‘Visualizationpane ‘Thisarea ofthe applications specficto each ype of visualization and ess

you set ihespecihe atrbutes ofeach eementona dashboard als allows
‘ou 10 ter dashboards, pages, and individual visualizations. You can a
format visualizations using this pane,

elds ist ee you can se al the available elds rom the source dat that ou can
‘seo build your visualizations.
‘VisualizaonPalete | This area contains all the currently available types of isualization that you

canada toa dashboard,

Page elector “These are tabs that et ou svt from page to page Ina report

16

‘Uren = TRODUTIONTO ROWER BESTOP

Power BI Desktop ke most Microsoft applications has several valable ribbons. These are
‘explained inthe course ofthis book.

Your First Visualizations

‘With your data safely place inside Power Bt Desktop, you can now begin to create the tables chars maps,
and other elements th: ou want to add w a dashboard, which ou ean us to presen your Bst insighs into
Brian Brish Cars. As hs a fist “taster” exerise, Lam not looking a expaiing all hat ean be done
using Power BI Desktop. ll wantto do sto show you how easy tito crete dashboards in minutes rather
‘han hous Indeed only hope dat his ist simple dashboard wil leave you hungry toleam more—and so
tomove onto the et ofthis book,
Before rating few simple visualizations, et me clarify some of the terms that you wil meet when
working with Power BE Desktops
+ Visualizations: ls known as visuals these ae the individual presentation elements
that you create based on theunderying data. A vsualean bea able, char, a gauge,
‘map, or many things indeed. use these terms interchangeably I his book,

+ Dashboard A Power BI Desktop dashboards collection of visualizations ona
single page. Indeed, tend o use the terms page and dashboard interchangeably.

‘+ Report Ths sa collection of pages (or dashboards) In single le, ll using the same
dataset

Displaying Available Fields
‘One ofthe frst dings to do make sure that you can see the data that you willbe working wit in
dashboards and report Iso ok atthe ht ofthe Power BI Desktop Repor view, you sea vertical pane
‘wth he abel Fed at the top This the Feds pane (or Feld Lis). is rom her hatyo access al te
dano use in your ialzaons and dashboards

For dhe moment, however all ht yon ses probably the name ofthe dataset hat ou imported
previous the BaseDat dataset rom Excel Do the olowingto see al he fields ar his able contains:

1. Clickthe small rangle tothe eft ofthe table name. The able wil expand to
reveal al the avaliable cis hat contains Alternatively, the field an already
Visible, they vil disppear fom view, leaving only the data table name vis
Youean see whatth loos ike in Figure 1-1.

"

‘ETERS = ROUCO To FONEREN DESK

Figure 1-16. The Power BI Desktop Fields lit

‘ou can se hat some ofthe fields havea sigma (1) co to her. This indicates that he data inthe
Pics numeric. A you progress through his Book, you wi se that her are oer icons that Power
Desktop uses ola diferent ypes of Bid.

‘Adding a Matrix of Sales per Country by Year

Ivismow ie to dra onthe blankeanvas thats your fs dashboard. To begin, les start with a simple
mat of sales per country foreach yea that Bilan Bish Cars us been wading.

1. Inthe Visualizations pane ic the matrix ion, as highlighted in Figure 1-17
A blank mail appear onthe dashboard canvas

|} Matrix Icon

Figure 1-17, The mars con nthe Visuallzatons pane

2 Leaving the fresh created mate selected, lick he check boxto the lft the
“Cour Name eld in the Feds is. The x of countries where carshave been
sold will appears the left-hand column of the mari.

1

‘Uren = TRODUTIONTO ROWER BESTOP

3. Dragthe Reportng¥ear eld imo the Visualizadons pane over the Columns eds
area hiss called he fled wel, Figure 1-15 shows howto do this This adds the
‘mode years a column headers the mati.

orar

Figure 1-18. Adding the Columns fields to a mari

4. Leaving the matrix selected lick the check boxto the et ofthe SalPrice eld
in the Fels list. The aggregated sale price forall vehicles sod by country nd by
rara appear inthe mati

5 Dragtbe comer handle ofthe matrix to resizes that ers mo spare
whitespace inside the matrix sel. wil look ke Figure 19.

‘ETERS = ROUCO To FONEREN DESK

zu
zum

zus
73
un
Er)

L =

Figure 119, Amatrixofsles per county

‘would be hard to make bis any simple Within seconds you have created matrixof sales by year
and country and he ttals have been added automatically Ofcourse there are many way of extending and
developing a mates in Power BI Desktop and you can discover hem allin Chapter 15 But or now, kis
time vo pres on and adda char wo your edging dashboard

Note Inti short exercise, you saw that you can bath select es fom the Fels str drag ther to
‘he ed well to add them 10a selected visual, An alternative isto rag a id rom the Fld tonto the
visualization Ae

Adding a Column Chart of Delivery Charge by Model

Now that you have seen how easy sto create a matixin Power BI Desktop, the time has come to add some

visual impacto your analysis. Ts time, you will sete avalable data t display the total delivery charge
foreach modelof ear sold

1. Clickan empryareaofthe dashboard canvas to unselect any visualizations.

2. Dragthe Modelfel onto an empty area of the dashboard canvas. Power BI
Desktop automatically rete table displying al the vehicle models old.

3. Dragthe DelversCharge ed from the Fields pane onto th able that you
Justereated, Power B Desktop wil calculate the total DeliveryCharge foreach
avalble make. The table wl look ke Figure 1-20,

2

‘Uren = TRODUTIONTO ROWER BESTOP

Rape
See Sree
Soe Seas
Ses

4

Figure 1:20, A able of aggregated deliver charge per make

44 Leaving the able selected, click he clustered column charticon inthe
Visualizations pane. This Ihe second ion onthelefton the upper row of the
Selection of visualizations Power BI Desktop wil switch the ableto a chart.

5. Dragthe comer handle ofthe chart resize itso thaall he makes are visible on
‘he bottom axis. The chart wil look Figure 1

e _

Liu

de = 4

Figure 1-21. A column chart of delivery charge by model

‘ETERS = ROUCO To FONEREN DESK

"qual simple dont you think Yet, belle me, the fun has only just begun. While you will seelos
more abouthow o create and enhance chars in Chapters 1 and 1, you can always my few basic weak
now. For sta, you select the char and then ik any othe other carting eons inthe Visualizations
ane, you can change the type ofchartinstantaneously. Moreover you ae not sur which icon does what,
the al you have odo ls hover the mouse pointer over an con Inthe Visualizations pane to display a 00
that wil guide you Fre

Adding a Map of Labor Cost by Country

"Tables and chars are ll very wel, but nothing beats a good pleture when It comes to llusrainga pot or
highlighting an insight So, as we havea dataset hat includes information for range of counties, why not
display some of ouranalyis aka map?

1. Clickany empry par ofthe dashboard cas tounselect any visualizations

2. Clickthe fled map icon in the Visuallzations pane, You can see this icon in
Figure 1-22

Filled Map
ton E

Figure 1-22. Adding a fled map visualization toa dashboard

3. Levingthe empty map visualization selected, clik check boxto the et of
‘the County Name Beldin the elds ist. This wil display a map of he word.

4 Leaving the map selected, drag the LaborCos fil ont the map. This wll
‘lglg any countries where there are abor costs latin to vehicles sd.

5. Dragthe colored European counties tothe center ofthe map.

6. Using the mouse wheel, 200m in tothe colored European counties. The ished
map wil ook ike Figure 12

2

DENMARK. copenhagen

North Sea 3 PE

Mey of coy

PE a
= aot
= =

Figure 1:28. Amapoflaborcast hy county

‘This ime, and in ony afew clicks, ou have used your data to crete a map tht cleat lastre the
cography of your sales, Once agan, his only rapid overview ofall hat Power I Desktop can do when
‘Reomesto displaying mapping data You wll lear more About eretingand modifsing maps in Poster BI
Desktop in Chapter 19.

‘Adding a Card Showing the Total Cost of Spare Parts

Sometimes you do not want o show alarge amount data but quite the opposite. You want to highlight
single igure 1 give prominence on the dashboard Power BI Desktop has a really eetve way of
doing just ths. e consists of adding visualizations elle cards, which ar what you Will now add your
‘dashboard.

1. Clickthe dashboard canvas to unselect ny visualizations.

2. Giekthecardicom in he Visualzations pane as shown in Figure 121.

‘ETERS = ROUCO To FONEREN DESK

©} Cardicon

Figure 1-24, ding card visualizan t adashboard

3. Leaving the lightly clunky) empi card visualization selected click the check
Porto theft the Sparoart eld in the Fels list. This plays the spare
arts totali the source data.

4. Dragthe comer handle ofthe mario resize so hat there lso spare
‘whitespace inside the matrix ef lo ke Figure 125,

495K

igure 1-25. Acard showing the total ost of pare parts

‚That sallyouhave todo, Three or four clicks and you have a dear visualization ofa ey met foryour
adience. This snot the ony way that you can crate this particular visualization, ut you hae to wait for
(Chapter 510 get alte detal on adding cars to Power BI Desktop dashboards.

Adding a Slicer by Make

‘As final weak, you will add some interactivity tothe dashboard hat youare building. You will adda slicer
(aninteractveseecion too! that wil let your any ser ofthis dashhoand ter by any ral car models
Sold. Heres how you ea do this:

1 Dragthe Make eldto a blank area onthe dashboard canvas Power Bl Desktop
viles alistof vehicle models

2. Clickthesicer con nthe Visualizations pane, as shown in Figure 1-2.

Slicer icon —[

Figure 126, Adding aserto a dashboard

2

‘Uren = TRODUTIONTO ROWER BESTOP

3. Dragthe comer handle ofthe slicer to resize lo that here fs no spare
whitespace inside the slice rw Look Figure 127.

L _ 4
Figure 1-27, A dcr on the model of vehicle

ou can now test the slicer by selecting or deselecting any car model that sisted in he slice The
‘othe visualizations onthe dashboard will instantly be updated to reflect the choice of models. You wl soon
et art look at how his ler can be used to ler data

Arranging the Dashboard

‘Now that you have created. few visuals tistime o coordinate them on the page so that youcan delivers
‘meaningful dashboard that adds power o your insights

‘Moving a Visualization
Moving visualizacion isimpessvely easy:
1. Clickthe visualizacion that youwant to move.
2 Dragthe visualization elsewhere on the dashboard canvas.

Resizing a Visualization
Resiinga visualization slo extremely easy
1. Glickthe visualizacion that youwant to size

2. Move the mouse polmerover any ofthe comer or ide handles ofthe
visualzadon. The pointer wll become a double-headed arrow.

3. Dragthe edge ofthe visualization to Increase or decreas Is current ze.

‘ETERS = ROUCO To FONEREN DESK

Mera ide efor, your dashboard could ook ike he one in Figure 120

495K

Figure 1-28, The final version ofyour frst dashboard

‘Sohere you have your fst Power BI dashboard. have admit hat ls fist staba ses.
busines intelligence was not concentrating overmuch onthe aethetics he output. | prefered o lt you.
appreciate the speed and simplicity with which you have created an entire dashboard, fom scratch and with
no prior knowledge ofthe wool at you have used.

How long did it take you to ball this dashboard, do you think? Fften minutes? Thirty minutes?
Indeed, however longi took, you have also leamed the bases of dashboarding with Power B Desktop. You
an now build on this knowledge as you progres through his book.

In fact, extendinga dashboard by adding further visualizations isso Inle tats 00 easy o miss
‘he sale points of what you have seen so a. So, 1 resume, what you have Just learned hat

‘+ Youcan place any visualization anywhere on the dashboard canvas.
+ Youcan esizean element quickly and easly.
+ You can convert any typeof visualization to anyother pe in single ei

Interactivity in Dashboards

Building a dashboard was only the start. as far as Power Bl Desktop ls concerned. Fora Power BI dashboard
Isnever setin stone In fact quite the oppose true, because every dashboard ta you creates Instantly
and intuitively interactive This means that you can use 1 highlight sallem points and del down to expose
the ey insighs that your analysis has led you to.

25

‘Uren = TRODUTIONTO ROWER BESTOP

ven simple dashboard Ike the one that you Jus created is immediately Interactive. Asan example,
suppose that you wantto use this dashboard to display data or onl couple ofthe makes that he company
as sold. folowing explains howto dol
1. Inthe slicer (om the tp right ofthe dashboard), Cul-cick Bentley and Rolls
Royce. The dashboard wi instantly update t show only ata or these car
‘models, as shown in Figure 1-2.

210K

hi he
Win
Blinc

fin

igure 129, Interactive flteringa dashboard using a ser

Inthe slice clickany element ice o clear th
Inthe map click Franc. The dashboard wil instantly update to show only dat

forthe selected county as shown in Figure 1-30

2 er.
a

a

GER = TRODICHE TO OER BEST

ak

ul,

Figure 1-30. Imeractivelyflteing a dashboard using a map

Now ts yourturn Rather than explain her all hat you can doo fer and view your dat, suggest
that you try clicking part ofthe map oF the column char and see what happens! In any case al he detal
concerning les and slicers is explained in Chapters 20 and 21

Formatting Reports

Power Bi Desktop allows youto create reports with multiple suas In record ie. Quo he box you can.
produce sys vel presentation in afew clicks Yet with a ie ar, you can enhance the standard
Power BI Desktop formatting and ru leave your audience impressed with your presentation ll.

"This section wilimroduceyouto à few ofthe techniques that you can apply o transform the look and
fee of your reports Aswas the case withthe previous sections in ths chape, the alm here noto give you
an exhaustive our ofall the formating possibles that Power BI Desktop has oler (Chapters through
2 do this). Instead wantonly to show you how easly and intel you can take the allure of your
reporting tothe next level. Fortunately Power B Desktop les you format near al visuals in similar ways.
Soyou only have to eam a few base techniques tenance all your reports in record me.

you want wy formatting report bu have notbultthe report tha you have sen solar in this
chapter, you ean always load the Power Bl Desktop fle named C:\PowerBiDesktopSamples\CHON\

CHO Example pb ssuming, ofcourse, that yo have downloaded the sample data. as described in
‘Appendix.

20

GER = TRODUCTENT OER SKI

‘The Format Pane
Al ormatingis defined inthe Format Pane. You such 1 he Format Pane as follows:
1 Sele

xing visual

2. Clickthe "pate roller” icon inthe Visualizations pane. This isthe Format icon.
Youcan se thin Figure 131.

m

Format Icon
Figure 1-31. The Formaticon
‘licking the Format con wil display the Format Pane containing the formating options that are

sable forthe ype of vial that you have selected. The range of avalabl formatting options wil vary or
cach ype of visual.

Note You can only format multiple suas at he same time to selecto visuals are ofthe same ype.

Borders
‘Neal al visual allow you toad border To enhance a visual with border

1 Seletthe visual hato want o format wil selet the card inthe report that
youhave bul inthis chapteD.

(lckthe Format con
Clckthe Borde button to add a border.
Expand the Border section ofthe Format Pane.

‘ck the color palette and select color fr the borde. The Format Pane wil
ook ke Figure 132,

ren

2

GER = TRODICHE TO OER BEST

2

"499998

Figure 132. The Format Pane for card visuals

“ati al you have to do Th borders nov added tothe selected visual Fortunately this technique
will workinthe same way for mos Power BI Desktop report visuals ehatever they may be.

Background Color

Some visuals look better witha diferent background colo let distinguish slicers rom other visuals, o
Les ad a diferent background color tothe licer in he sample report.

1 Select he visual tha youwantto format (I ilselect the slicer inthe reportihat
ou have butin this chapter)

2. Clickthe Format icon unles the Format Panes aleady displayed
3. Expand he Background section ofthe Format Pan,

lek the Background buton to adda background color. The Format pane wl
“spay "Background On” and the background bution will now bea fled ee.

5. Clickthecolorpalete and select color forthe backround color The Format
Pane wl look ike Figure 1.

30

GER = TRODUCTENT OER SKI

Figure 1:33. The Format Panor slicers

you an see, there are other background color options. wil explain these in Chapter 15,

Titles

Certain visuals need tes, ether to make a point orto diferent
is easy

specific isl. Addingormodifing à

1 Select the visual whose tte you want to modif (Lil ect chartin the
report that you have bulk in this chapter)

2. Clickthe Format icon unless the Format Panes already displayed.

Expand the Tie section ofthe Format Pane

“Ensure thatthe Tite button son. This should already be the case for chan
(Change the te ext Delivery Charges.

sor»

{lckthe color palet nd select olor for he ont colo,

a

GER = TRODICHE TO OER BEST

7.. Dragthe sider fr the Text Size option tothe right increase the fon ae. The
Format Pane vil lok ke Figure 1-1.

Figure 1-34. The Format Pane or char visuals

As you canse acharthas many more valable formating options than the ther visuals that you saw
sofas These options willbe explained in more depth in Chapter 1

Table Gridlines
‘Tables and matrices canbe enhanced with your choice ofgrdlines. Adding these easy as

4. Select he visual that you want to format (lec the able inthe reportar
you have builtin this chapter).

‘Click the Format icon, unless the Format Pane already displayed,
Expand the Gri section ofthe Format Pane.
‘Sitch on the vertical and horizontal ri,

ren

Select an Outine Color rom the Outline Color alee. The Format Pane wil ook
ke Figure 135

32

GER = TRODUCTENT OER SKI

ie mg

igure 135, The Format Pane for table visuals

Yet again thee are many more options that you can adjust to add extremely precis formating to
‘ables. These techniques are explained in Chapter 1,

Data Colors

Asa final quick example of how formating an be applied to your visuals, suppose thatyou want to change
‘he colo ofthe data representation inthe map that youcreated. To do this:

1. Selecthe map tharyou wantto formar.
(ckthe Format con
Expand the Data colors section ofthe Format Pane.

hen

lckthe color palette nd scect a color forthe borde. The Format Pane will
Look ke Figure 1.

ES

GER = TRODICHE TO OER BEST

À Data colors

Default color

Y Background

N tock spect

Figure 1-96. The Format Panefor maps

Now that you have rweaked the presentation of your starter report should look something like the one
shown In Figure 137

4

‘Uren = TRODUTIONTO ROWER BESTOP

495K

Figure 1-37. The orginal report within formatting added

Admin, dese are only a ew quick modications to inwoduce you to some ofthe formatting
possibles that are eurent avalable In Power BI Desktop. There smuch, much mor that you can do
Once ou ar at ease with Power BI Desktop and have understood te basi approach to formating visuals
‘With lite practice you should be able o produce high-quality reports and dashboards that enable your
nalysesto leap out at the audience and convey the information that you want to deliver you want take
‘look atthe final formatted report, you can open the Power BI Desktop fle CAPowerBiDeskzopSamples\
CHOINCHOI Example Formated phi.

Note Deleting a visual sas simple as selecting and pressing ne Delete key. Removing a Visual wl not
affect the underyng data in any way.

Creating and Modifying Reports

So far in ths chapter, we hav treated th Power BI Desktop fle asifitconssedof ona singe page. In.
practice you are key to need to base several pages of analysis and information on the same underying
“aras Consequemt, Power Bl Desktop makes I easy to add copy, and delete he pages in our origina!
lso that you can create complex data stories hat al use the same dataset.

(as presume I probably the case for many Power Bl users) you are use 1 using Exe then you will,
‘ely ind the way that pages are handled in self-service Bl incredibly simple because in Power BI Desktop,
‘ach pages very similar tan Exel worksheet. To make matters claret look at Figur 1-3, where youcan
See the page abs of Power BI Desktop.

‘ETERS = ROUCO To FONEREN DESK

Page Scroll Delete Page Icon

a a
DEE | + New Page Button
==>

Pages
Pure 1-98. The Power Bl esto page ab
Adding Pages

‘when you open a new Power BI Desktop Al, always default having single page, thoughtfully named
Page You can adda new page as follows:

Inthe Home ribbon, click the New Page button. A new blank page named Page n
willbe added the existing collection of pages inthe report.

‘Tip _As an alternative tothe New Page button you can always cick the smal pis-sign tab atthe bottom of
‘he screen as seen in Figure 1-38.

Renaming Pages
Youcan rename pages as follows

1. Double-click herb othe page that you want to rename, The exiting name wil
be highlighted

2. Enteranewnameforthe page

Press Enter lickinside the dashboard canvas forthe page orclickanother tab to
confie your changes

Deleting Pages
Ita page no longer any set you, then youcan delete tf couse

4. Hover the mouse pointer over the ab or the page that you wantto delete.
Asmall cross appears atthe op right ofthe pagename as you can
seein Figure 1,

36

‘Uren = TRODUTIONTO ROWER BESTOP

2. Clickthecros.A warning log wl appear as sh in Figure 1-0.

Delete this page

Figure 139, The page delete dialog

3. Click Delete. The page willbe deleted and al vsualson the page are removed
rom the fle

Moving Pages
arte sequencing of te pages in yourrepo, do the linge
1 kth

tab corresponding tothe page hat you want to move.
2. Dragthe page ab ettor right toa new positon inthe setof pages.

Duplicating Pages
a page contains ase ofelements that you wanto reuse (may bea template page containing log and

the background fora series of pages in a report fr instance), then you can make duplicates of pages, as
follows:

1. Hoverthe mouse pointer over the tab for he page that you want to dele,
2. Righ-dlckthetab. A pop-up menu will appear.
3. Select Duplicate Page

An Kenta copy ofthe page wll appear o the right of ny exiting pages There also a Duplicate
Page option nthe popup meni forthe New Page burton you peter.

Scrolling Through Collections of Pages
yourrepor comatns dozen of pags hen can get very wearigto ta hough the seta pages one at
“e. Instead you can ell he page acral buttons (se Figur 1-1) srl ough he set pages na
omer Bestop te

a

GER = TRODICHE TO OER BEST

Conclusion

‘Welcome to Power BI Desktop. In a short chapter you have seen justhow amazingly imple and intl

{sto seis ree sel service business Intelligence tol rom Microsoft You have seen how to load data from

an external source. This chapter has also given youan dea ofthe wealth of potential sources of at that

Power BI Desktop ean handle You saw how to take data and use et create ables, charts maps, and slicers

An an interactive dashboard that you can now share with co-workers and friends you wanto, Once you

ave created yourreports you can then enhance them visually through a wide range of formatting options.
‘eta is merely catched the surface of the vas potential ofthis amazing application s you

wl discover as you progress through his book, you are on he cup of discovering avertable weastre

‘wove of analytical posts and stunning suallaions that wil help you deve your data analysis and

resentation ils to the nex lve

CHAPTER 2

Discovering and Loading
File-Based Data with Power
Bl Desktop

‘Before you can present any analysis or insight, you need data. Your sources could be in many places and
An manÿ formats, Nonetheless, you need to acces them, look at them, select them, and quite posi
restructure them or lean tem up to ome exe. You may lo need join many separate datasources
before you shape the data into a coherent model that you can us asthe foundation for your dashboards and
reports The amazing things that youcan do al ofthis using Power B Desktop withoutneeding any other
tools or utes.

Discovering loading, cleaning, and modifing source data is one othe areas where Power BI Desktop
realy shines. allows you © accomplish the following

+ Data discovery: Find and connect oa myriad of datasources containing potentially
seul data. Tis canbe rom both publi and private dat sources This the subject
of Chapters through 5.

+ Data loading: Select the data ou have examined and load ¡into Power BI Desktop
for shaping, You save this bien Chapter

+ Data modificaron: Modif the structure ofeach dataset hat you have imported hen
‘ler and clean the data ise (we will ok at this dual in Chapters through)

+ Data shaping: Join datasets create clear united, and accessible data model You
willean how odo thisin Chapter 10.

Although have outlined these four steps a they are completly separate and sequential, the realy
stat they often blend imo a singe proces Indeed her could be many occasions when you wil examine
the data fer thas been loaded into Power BI Desktop—or clean datasets or you lad them. The core
objetivo wil however, always remain the same: find some daa and then load it nto Power BI Desktop
‘where youcan tweak lean, and shape

‘This process could be described simplsticall as Fst, catch your data” Inthe word of data
warehousing, the specials cal ETL whichis shor for Bac, Transform, and Loa. Desitethe
reassuring confidence that he acronym brings this process rarely a smooth, logical progression through a
‘dearcut series of steps. The elit often far messe han that You may often nd yourself importing some
‘ata, cleaning it imporing some more data from another sour, combining the second dataset with the fs
‘one, removing some ows and columns, and then repeating many ol these operations several mes vee

Adam spin 2018 s
A Asp Pr Power BI Desp. SoLo 0.007781

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

Inthis and the following few chapters wl ry to show you how the process can workin practice using
Power BI Desktop. hope that this will make the various steps that comprise an ETL. proces clare. All

{am asingis ha you remain aware tha the range prions that Power BI Desktop includes make ta
‘mulfaceted and wemendousy capable oo. The science so know which pions use. The artist know
then to use them,

The Power BI Desktop Query Editor

‘This chapter extends the data load process ha you saw bre in Chapter Inthe previous chapter you
loaded data direct nto Power Bi Deskiop—or ore precisely nto the Power BI Desktop data model, In
this chapte you exend this approach with an additional step. Yu wl for some datasources, see how to
load daa into the Power BI Desktop Query Editor before adding woth data model. This “detours the
partofthe process hat allows you to leans and transform the data before itis added to de data model.
Ofcourse, your daras perfect then you can at straight no the data model and start bling reports.
Indeed fyou are connecting o deansed and structured corporate dat, you may want to Jump right to
(Chapter \0andleam how create a data model. However four data needs any adjustment a al, then the
Power BI Desktop Query Editor wil key soon become a trusted too, Consequently itis probably worth
reading Chapters > through9 that describe how to load data from a range of possible sources and then
Shape, modif, and structure your dat so that becomes a lear source of new and valuable insight.

"a ee part of you journey hough the data mashup proces, this chapter wil show ou how to
find and load data from a varity of fe-based sources. These Kinds of data are typical thse that you can
either locate on a shared network drive download fom te Internet, receive as ane-mal attachment, or
‘copy to your computers local rive, The ls that ae sein the examples inthis chapter ae avaliable on
the Apres website. Ifyou have followed the download instructions in Appendix, then these Als will bein.
the C\PowerBIDesktopSamples\CHL older.

Data Sources

In the firstchapter you saw how quickly and easily you can load data nto Power DI esop and create
stunning dashboards Is now time to ake a wider look atthe types Ale based dat hat Power Bl Desktop
can Ingest and manipulate

the sheer wealth of possible datasources can seem overwhelming at ist, Power Bl Desktop groups
potential daa sources into the following categories

‘+ le ints Excel ls, CSV (comma-separated values) les, text ils ISON fle,
and XML les Power BI Desktop can even load entre folders aloes.

+ Database: A comprehensive collection of relational databases that are eurent in
(he workplace andi the cloud, including among others) MS Access SQL Serve,
and Oracle. The ul ist of hose available when this book went to press ven in the
folowing chaper.

“+ Azure Thisopton lets you se an immense range of ata pes thats hosted in
the Microsoft Cloud. This covers dat format from SQL Server trough o big
sources. You can see how fewf these are used with Power B Desktop in Chapter

‘+ Onlin services-These sources range from SharePointliss to SalesForce, Dynamics
36510 Facebook-and many, many others. Some ofthese are examined in Chapter 5.

“+ Other À considerable and ever growing range of data sources rom Facebook to
“Microsoft Exchange. Some ofthese wil be touched on in the course ofthe four
chapters that cover accessing data in Power BI Desktop.

40

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

‘Thelstof possible data sourcesis changing ll he time and you need tobe aware that you have ook
atthe version of Power BI Desktop that youare usingifyou want an exhaustive ist ofthe all avaiable data
Sources that you can use. Indeed, expect that several mare wil have ben added by the time that you read
this book.

You canals ls the contents of folders on any valable local dik, network share, or even nthe loud
and hen leverage tis to import several fils a one. Similar (you have the necessary permissions), you
‘nist he databases and data available onthe database servers you conecto. This way, Power B Desktop
‘in provide no nly the data, but also he metadata data about data that an help You to ah quick
look at potential sources of data and only choose those ha you mall need.

‘Unfortunatly, de sheer range of datasources rom which Powe BI Desktop can read data auch that
we do not have pice na few chapters to examine the minutiae of everyone. Consequenti, we wil ake a
rapid tour of same of the most frequently used data sources inthis and the nest few chapters Fortunately,
‘mostof the data sources that Power BI Desktop can read are usedin similar way. This because the
ower BI Desktop Interface does a wonderful jo of making the arcane connection detal as unobtrsie
as posible So even you ae faced wil datasource has not described in these chapters you will
‘nonetheless se a variety of techniques that canbe applied to virally any ofthe datasources that Power BL
Desktop can connect.

ote The st of data sources nat Power Bi Desktop can access Is growing al the time. Consequent,
en you read this bok ou wl probably find even more sources than those described in is and the net
three chapters.

File Sources

Sending files across networks and over the Inernetor via e-mail has become second nature to most ofus
long asthe es that you hae obtained conform to some fthe widely recognized standards currently in
‘ef which yo wil ara more aer), you should hae litle difculy loading them into Power BI Desktop.

“ele sources that Power BI Desktop can current read and from which can load data are given
‘ble

‘Rable2-1. Fle Sources

Fie Sours Comments

Beet Allows you to read Microsoft Eee ls (versions 97102016) and load workshees,
named anges and ables.

csv Les you load tex fies that conformo the CSV (comma-separated values) format.

xa Allows you to load dat from XML les,

Tet ‘Lets you load tex es using varier of separators

folder Lars you load the information about al the es folder

SharePointfolder _Allowsyoutlisthefilesin a SharePoint folder.

Aecessdatabase Let you connecttoa Microsoft Aces fil on your network and lad queries and
tables.

ES Allows yout load deta from ISON les

a

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

ote | raie tht Power Bl Desktop considers MS Access o bea database and nota“ data ye.
‘Wile completely agree with tis lssicaton | peer nonetheless to teat Access as ft were file-based
‘datasource, given that athe data resides in à single fle that canbe coped and mal, and not Ina.
‘database ona distant serve. For this reason, we wil lok MS Access inthis chapter, and not he next one
at das with corporate datasources,

Loading Data

Itistime o star looking atthe healing aspect of Power BI Desisopand howyou can set 10 Load data
roma vary of diferent sources will bein on the bunny slopes with a simple example of ering”
data roma web page. Then given the plethora of available data sources and to give the process aeleaer
Structure, we wil oad data rom several ofthe ubiquitous file-based datasources ar are found in most
‘workplaces These datasources are he bass ofthe data thatyou willeam o tweak and "mashup" ln
‘Chapters trough & This data coud also become the bass of many ofthe dashboards that you wllereate
In Chapters 1102. These sources ar as follows

+ ow
+ Tet
+ ML
+ Bee
> tenes
“on

‘ou wil see how to load multiple ex or CSV le a once. To conclue the chapter you will se howto
store ony the deals about the les in folder rather than he les themselves

CSV Files

‘The scenario sas follows: you have been give a CSV ile containing alist of data You now want oad this
Into Power Bt Desktop so that you cam ook atthe data and consider what needs tobe done (anything) to
make useable

rs, you need an ida othe data that you want to load you open the source fl CA
PowerBiDeskiopSamples\CH2\Countes.csv with text dior, such as Notepad, you cn view ts content
‘Thisiswhatyou can seein Figure 2 1.

Dt

Aigure2-1. The contents of the Countries fle
2

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

“The following steps explain what you have to dot load the contents ofthis le Into Power BI Desktop:
1. Open Power BI Deskzopand close the splash screen
2. Inthe Power Bi Desktop Home ribbon, lick e Get Data button (and not the
small langle that displays menu options).
3. GickFleontheleft You see something like Figure 2 2 (the Gt Data dialog)

=
‘Figure 2.2, Thecontents of he Countess fle

4 GickTexvCSV on herightofihedialog.
5. ClickConnect The Open dal will appear

6. Navigateto the older containing the file that you want to load and select it
(CAPowerB1DesktopSample\CH02\ Counties in this example).

{ck Open. dialog wil display the inal contents ofthe fle, as shown
in Figum 2,

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

Figure2-3. The Power BI Desktop file dalog

8 ClicktheEaltbutton. The Power BI Desktop Query window appears: contains
a sample ofthe contents ofthe CSV leo possibly he emi lefts not 0

large. You can see this in Figure 2

Figure 2-4, The Power Bl Desktop Query window with the content ofa CSV fle loaded

a

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

9. Glick the Close & Apply butonin the Power

skp Query window (youcan

se hisatthe top lettin Figure 2-2). The Power BI Desktop Query Editor il
‘love and return the focus to the Power Bl Desktop window; where ou can see
thatthe Countries dataset appears in the Feds ston the right of the Screen, as
shown in Figure 25.

Figure. The Power BI Deskiop Query window with a fe loaded.

And that for the moment that You have loaded the file into Power BI Desktop in a matter fa fw
‘leks and itis ead for use in dashboard and reports, necessary you an expand the Counties dataset
“anden se the elds that this dataset contains just as they were inthe dialog shown in Figure 23

In later chapters, you wl earn how 1 shape his data For the moment, however les continue looking
atsome other file-based datasources,

What Is a CSV File?

‘Before we move onto ther Me types, there area few comments need to make about CSV Bes. There
technical specification of what a “true CSV les but! won't boro you with that What more, many
programs that generate CSV ils do notalways follow the definition exact, What matters hat Power BI
Desktop can hand exes that

‘+ Haves sv etension (uses is by default 0 apply

+ Usea comma to separate the elements in a row. This 00 isa default hat can be
overridden y selecting delimiter rom those nthe dialog shown in Figure 2.

et kind of processing

45

curren

SCORING AD LOAD FLE ASE DAA TH OR BL DEI

End with a in feed, carriage return, rine feed/carriageretum,

Can, optional, contain double quotes ro encapsulate fields. These wil be stripped
out as part ofthe data load process. here are double quotes they donot have o
appear or every eld nor even for every recordin el hat can have occasionally
Inconsistent double quotes

‘Can contain “regalar” records tat is rows that donot haw every element found
Ina standard record However, the Best row (whether or not contains tiles) must
cover every element found lle remaining records in thelist Put simply any
‘other record can be shorter than the frstone but cannot be longer.

Do notcontin anything other than the dat sel te fle contains header runs
‘orfooter rows that are not par ofthe data, then Power BI Desktop cannotload
the dataset without further work. Ther are workarounds o ts al 100 Arquer
problem; ones given in Chapter.

ote Anoter way of accessing CSV files to lick Gt Data > Fle and select Ten/CSV in he Get Data

alos.

Text Files

you followed the proces fo loading a CSV fle inthe previous section, then youl ind that loading a tot
Ai virally dential This I not surprising. Both are text les and both should coma a ingle is of at,

‘Thefolowing ae the core diferences:

texte can have something other than a comma to separate the elements in alist.
‘ou ean socie delimiter when defining the oad ep.

Atte should normally have he extension (though this to, canbe
ver),

text fe must be perfect formed: that every record (row) must have he same
‘numberof elements as every other record.

text fe too, mus not contain anything other than the dataset fou want a ates
‘ata load the ist ime.

Afatext file encounters dies, should impor the data asa single column that
you can then ry and pl up into multiple columns, as described In Chapter.

Her, hen, show to load a text lento Power Bt Desktop:

6

In the Power BI Desktop ibbon, elle Get Data ® Text/CSV. The Open dialog all
De displayed.

Navigate 10 the folder eontaningihe file and selec the le
(C:\PowerBtDesizopSamples\H02\Counteyistx in this example).

(lick Open. A dialog wil display the inital contents fe Me You can, ofcourse,
double-ick the Mle name rather than cick Open. The dialog should look ke the
‘oe in Figure 2 6.

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

CountryListos

Figure. The content ofa text fle ready fr loading into Power BI Desktop

4. Giekthe Cancel button (because atera quicklook atthe contents ofthe fle you
Rave decided tha you donot really need i)

ote As text-based les (which incide CSV les) are such a frequent source of data, you wil nary
always soe the Tex/CSV option drect accesible in he popup menu that you access by liking the small
‘langle in the Get Data button inthe Home ribbon. his option i not visible, you can instead select Get
Data > File and select TOXUCSY, a you dd previous

‘Where Power Desktop is really lever i that can make a very educated guess as to how the ext le
structures thats can nearly always guess the fl separator (the character that iolate each element
Ana ist from he other element). And so not only wil break thelist into columns butit wil also void.
{poring the column separator ft doesnot guess corecy then don’t despale You wl ear how o
‘correct thisin Chapter

‘ooking atthe content of fle and then deciding ot to use is partand parcel ofthe data discovery
process that you wil ind youself using when you wrk with Power BI Desktop. The point o this exercise
{0 show you how easy tito ance inside potential datasources and then decide whether to Import them
mo the data model or not. Moreover an be easier to see the firs few rows of large text or CSV les directly
{nthe Load dalog of Power BI Desktop than ts open the whole Bein text do.

Tip Althe risk of stating he abus, you can press Enter to accep default choice Ina dalog and press
Esc to cancel out othe dilo.

a

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

‘Text and CSV Options

‘You can seein Figure 2-6 that there are few options mallble that you can tweak when loading text or CSV
le Most ofthe time Power I Desktop wil guess the correct stings fr you. Hosreer there could be times
nyou vil need to adjust these parameters lightly. The potential options tha you can modify are

+ BleOrigin
+ Delimiter
+ Data Type Detection

File Origin

“This option deinesthe character encoding in which te ek or. Dirt char sets can handle

erg ranges characters uch accents Normal is formation corra interpretedby Power
Fi Desiop and youshouldonly need to selecta diferent characte sl org) on very rare cron,

Delimiter

Power BI Desktop will uy and gues the special characte that is used in a ext or CSV feo separate the

“columns of data Should you wish w verid the chosen delimiter, you have de hole of:
© Colon
+ Comma
+ quals
+ Semicolon
+ Space
+ Rb character

You can also decide to entra custom delimiter sucha the pipe () character, or even spect tha every
field has a Bed wide,

Data Type Detection

Power BI Desktop will make an educated guess at th data encoding that ls used in text or CSV Ml. By
default save time, il only read heist 200 record. However yow can choose rom anyofthe olowing
three options:

“+ Rea the ist 200008
“+ Rea the entre file
+ Nodatarype detection

ote Be wamed that reading a large fe ints entirety can tke quite a while. However, without accurate
‘datatype detection, you sk seeing some weird characters inthe data that you oad.

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

XML Files

ML, or Extensible Markup Language, a standard means of sending dat between IT systems.
‘Consequent you key wil load an XML fle one day. Although an XML fs Jste, tis texthat has
‘been formatted ina very specifi way, a you can se Ilyouever open an XML Be ina text editor suchas
‘Notepad. Do the fllowingvoloadan XML Ble:

.

Inthe Power BI Desktop ron, click th smal rangle onthe Get Data buon,
and then ick Mor inthe menu that appears. New, in the Get Data dlalog sleet
Fle and XML

2. ClickConnect The Open dog will appear
3. Navigateto the older containing thee and select he fle
(C\PowerBIDesktopSamples\CH02\ColoursTable m, inthis example)
4. CickOpen- The Navigator dialog willopen.
5. Glckthe Colours datsetinthelethand pane of the Navigator dialog. The
‘contents ofthis partof the XML Ale willbe displayed on height ofthe Navigator
log a shown in Figure?
Navigator

Figure27. The Navigator diag before loading an XML fle

6

(he check boro the eftofthe Colors dataset the lek The Load and Eat
buttons wll be enabled.

(lick the Eat buton. The Power Bt Desktop Data window wll display the
‘contents ofthe XML fle,

{ck the Close and Apply button inthe Power BI Desktop Data window: You wll
se thatthe Colors dataset appears inthe Fedslist ono right the screen.

9

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

‘Te actual internal format ofan XML fle can get extremely complex Sometimes an XML fle wil
contain only one dataset; sometimes il contain many separate datasets On other occasions, tl
contain one dataset whose records contain nested levels of data that you need to handle by expanding
ierarchy ofelements. You wills how the Navigator dialog handles nested hierarchies of data in the
folowing chapter inthe comen of database sources.

ote Certain types of data source alow you to lad multiple sets of data simutanoushy XML es (uni
CSV and tx ls) can contain multiple independent dataset. You can load several datasets imutanccusiy by
_selocting the check box to the left f each dataset hat you want o load fromthe XML fl.

Excel Files
‘You are probably already a major Excel wer and have many, many spreadsheets full of data that you want o
rationalize and use fr analysis and presentation in Power BI Desktop. S let see how to load te contents
ofan Excel fle,
1. Inthe Power BI Deskopsbbon, click the small langle at the bottom ofthe Get
Data button and then cick Excel. The Open dialog will appear.
2. Navigateto the directory containing the le that you want to look at
(CPowerBiDesktopSamples\CH02, in this example).
3. Seletthe source Ml (InvolcesAndinvolcelinessis, inthis example) and lick
‘OK. The Navigator dialog wi appear, showing the worksheets tables and ranges
Inthe workbook fle as shown in Figure 2.

Navigator

No items selected for preview

Figure28. The Navigator dialog before loading data from an Exel workbook

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

4. Clickone ofthe datasets sted on the ef ofthe Navigator dialog. The top few
‘os ofthe selected spreadsheet will appear onthe ight othe daig to show
your ata the chosen dataset looks ke

5. Clickthecheck boresto the eof the Invoices and InolceLines datasets on
eich.

6 Clk Load. The selected worksheets wil be loaded into the Power Desktop
data model and will appearin the Feds is in the Report window.

you can se from thissimple example, having Power BI Desktop read Excel data really not dificult
‘ou could hae eited this data in Power BI Desktop Query Editor before loading, but asthe data seemed
dean and ready tse I preferred to load i seaght into Power BI Deskop (or rather the Power BI Desktop
‘ua model). As wel you saw that Power BI Desktop can Toad multiple datasets atthe same dime from a
single data source. However you might sl e wondering about couple ofthingsthatyousaw during ths
Process, o here are some aseipatory comments
“The Navigator dialog displays

Worksheets invoices and InoiceLines in Figure 23)

+ Named ranges (volceRangein Figure 2:0)

+ Named bles (Tabet in Figure)

ach ofthese elements is represented by a diferent icon in he Navigator dato, Sometimes these can,
‘nefleet be duplicate references othe same dts, so you Should realy use te most precise datasource
(hat you can. For instance, Ladvise using named abe ora range name rather than a worksheet source, as
the ater could easy end up containing “noise” data (that data rom outside the tows and columns that
interest you) which would make the load process more complex than i really needs tobe. Indeed unless
worksheets prepared and structured, ready or loadinginto Power B Desktop, you could end up with
‘uperduous data in your data model

[Note Power BI Desktop wil ist nd use data connections to extemal datasources (sch as SOL Server,
‘rate, or SOL Serer Analysis Service) in a source Excel workbook ithe data conection Is active and has
retumed data tothe workbook Once a ink to Power Bl Desktop has been estabished, you can et the
ata abe sin ho source Excel workbook—and loa the data over the data connection nthe source
workbook into Power Bt Desktop.

Power BI Desktop will not into account any data ters on an cel data table, but wl load all he
ata that sin the source table. Consequently, youl have t reapply any fers (of which yw lean more
Inchapter)in Power Bl Deskiop ifyou want o subse the source data,

Importing Excel and Power View Items

Power BI Desktop isnot the fst Incamation of Power BI: the data model thatituses hasbeen around for
some years now. So, you may already bean accomplished Power View expert using Power View for Excel—or
ou may have advanced data models that you ave built wing Power Pivot ln Excel that you want to trader
Into Power BI Desktop.

sl

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

Fortunately, the tam a Microsoft has thought ofthis, andthe result that you can transfer all your
effort rom Excel (Power View dashboard Power Pivot data models and DAX metrics) into Power BI
Desktop with remarkable eis. Here show:

1. Openanew, blank Power Bt Deskop Ble,

2 Inthe File menu, seectImport> Excel Workbook Contents as shown in
Figure 2.

D revetment

2 mm >

On
D coum

Figure2-9. Importing existing Power View or Power Pivot ems rom Excel

& The Windows Open dialog appear, rom which ou can selectan existing Excel
‘Ale containing Power View of Power Pivot items In this example, you can use the
‘ile CarSalesFor Power xx from the sample files for this chapter.

4 CiickOpen.

5. Power B Desktop vil importany compatible items and display the import
screen (as shown In Figure 210) during the Import process.

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

Import Excel workbook contents.

Figure 2-10. Importing Power View and Power Pivot elements rom Excel

6. GickStar Power I Desktop wl begin to oad and conver data and elements
rom Excel. need, you could se further specific questions. Then the import will,
continue, showing he progress log hat You seein igure 2

Import Excel workbook contents

Figure2-11. The import progres log

7. Once the impor process has sucessful Anshed, Power BI Desktop wil display
‘the summary dialog hat you seein Figure 2-12.

Import Excel workbook contents
Y vet competes

Figure 2-12. The import summary dialog
ss

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

8. Click lose. The temsbecomea Power BI Desktop report

As this book went to press there were a few aspects of some Power View visualizations that were
not imported perfectly imo the Power BI Desktop data model. However as this technology current
developing at a rapid pace, you could well ind tat these minor limiaions have been resolved by th ime
that you read this book. In any case, ads you to consul the Power B web st for update deals on
ny remaining limitations concerning the conversion of Excel objets to Power BI Desktop reports

Microsoft Access Databases

Another well:used data repository tht proliferates in many corporations today I Microsoft Access. Iris
power desktop relational database and can contain hundreds of bes, each containing milions of
records So we need ose how t load data rom tis particular source. Moreover Power Bl Desktop canbe
particular useful when handling Access data because allows you to see the contents of Access databases
‘without even having install Acces sel

1 Inthe Power BI Desktop ribbon, ick Get Data > More > Database and select
‘Access Database inte Get Data log.

2 Click Connect and navgateto the MS Access database containing the data that
you want toad (C:\PowerBIDesktopSamples\CH02\ClientsDatabaseacedin
{his example).

3. Selec Access file and click OK. The Navigator dialog appears is lle
‘ables and queries inthe Access database.

4. Check the checkbox forthe Cents dataset: This display the content ofthe
table, as ou can seein Figure? 1.

Navigator

sé CRE
Figure2-13. The Navigator dialog before loading data rom an Access database

5. Click Load The Power B Desktop window opens and displays the table in the
lid tin the Report window.

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP
you look sey at the let of the Navigator dalogin Figure2-1,youcan see that itlsplys evo
Atlerent cons for Access objects
+ Atablefor Access data tables
+ Twosmallwindows for Acces queries
‘This can help yout understand the ype of data thatyou are looking at inside the Acces database,

Note Power BI Desktop cannot se inked tables in Access, nl imparta tables tables that are actualy
inthe Access database. It can, however read queries oversd upon nate, inked, or imported data,

JSON Files

‘More and more data snow being exchanged in a reaivly ne format called ISON. This stands for
JavaScript Object Notation, and is considered an ficient and lightweight way of transfering potentially
large amounts of data.

‘Now, lle Power BI Desktop can connect SON datas, these are not always instanty
compréhensible. So be warned that while ths section willteachyouhow to connect to SON data using
Power BI Desktop you wil hive to walt for Chapter to ee how his connection can be tweaked to convert
mo meaningfl information,

To connect a SON le:

1 Inthe Power BI Desktop bon, click Get Data > Fle and select SON inthe
listo fle sources on the right ofthe Get Data dialog, The dialog should ook
something ike the one showin Figure +1.

Figure214. Esablshinga JSON connection

2. GlckConnectand navigate tothe folder coaining the ISON Ml that you want
toad (CA PowerBIDesitopSamplesiCHOzAColor son, inthis example), You
‘cam see this in Figure?

ss

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

-20
a

igure2-15. Opening a SON le

3. Click Open. The dalog wll dose and you wi return the Power BI Desktop
‘window

4. Clickihe Apply hangesbutton atthe top ofthe Power BI Desktop window.

‘You will not yet see the data in Power B Desktop. However you wll have established a connection
to the JSON Ale that you can later convertnto he underlying data. You wl lear the next steps tha are
required to load ISON data in Chapter

Loading Multiple Files from a Directory

On many occasions you could And yourself faced with a ser ofidentical les thathave tobe loaded to make
up complet dataset. They could bo a fe or each day data, forinsance, exported from an oder system.
‘Asn one wants to load 365s individually the Power BI Desktop development team has come up wits an
answer this ind of challenge. Provided that you gather al the source Mes int a ingle directory, you can
loadallthe files atonce.

ote This approach requires that al he files In the source oder share an identical format I tis is not
‘he case, you vl probably not succeed in loading the data.

1. Openanew Power BI Desktop le

2. Click Get Data > Fle, and select Folder ftom the options on the right ofthe
ialog The Get Data dialog should look like he one shown in Figure 216,

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

HER

igure2:16. The Ge Data dialog used to select multiple les om older

3 Clkk Connect The Folder dialog willappenr.

4. Click the Browse buton and navigate othe folder containing several dental
structured text les (CAPowerBIDesktopSamples\CH02\MullpeldenticalPles,
In his example). The Folder dialog wl look ke the one shown in Figure 2-17

Folde

igure2.17, Selecting a older

5. COX: The contents ofthe folder wil be displayed as youcan se in Figure 2:18.

CA\PowerBiDesktopSamples\CHO2Mutipleldentiafiles

‘Figure 2-18. The contents ofthe selected older

s7

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA
6. Click Combine > Combine and Load. The Combine Fes dialog
vil appear

7. Selectone ofthe source ls to serve asa model forthe structure of ll the lso
load. The daiog should look ike the one shown in Figure 2 19.

Combine Files
noes =

Figure 219 The Combine Fes dialog
8. ClckOK. The data fom al he les in the folder il be loaded int Power BL
Desktop.

you carry out this operation, you wil see that an ex column willbe added tothe Fils it
‘containing the name ofthe source efor each record.

‘Note This technique presumes thatthe ls inthe source folder wil share an identical format. you
‘choose, you can check the Skip File With rors check ox instep 7. This wil exude any fs that do not
Share the same format as the fl that you specfid as being he example fle.

‘The Combine Fes dialog gives you a few options that are similar to those that ou saw previously when
loading text/CSV files. They include

+ Speciving he delimiter (separator for Mel nh dataset
+ Speciving how much, Many, ofthe mode fle Is used to detect he fe structure
‘+ Defining he character set (the encoding) forthe source Mes

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

Loading the Contents of a Folder

‘Rather than loadingall the le in a folder: you can choose to connect wa folder and loa only the essential
information about the avaiable ls. You can use his connection o load the fe themselves later, should
ou anto. To do this:

1. Openanew Power BI Desktop le.

2. Click Get Data > Fle and selec Folder fom the options onthe right ofthe
dialog,

3. Click Connect TheFolderdialogwillappear.

"Navigate te oder containing several dental structured textes
(CAPowerDesktopSample\CH02\Mulipletdentiales, in his example).

(lick OK. The contents ofthe folder willbe displayed.

(ck Load. series of predefined elds will appear in the Fes Ist as shown in
Figure 2 20.

>

>.

Figure 20, Dat fo folder contents in Power BI Desktop

‘You can now use this information lherto display the comtens ofthe older in Power BI Desktop or
(and this is where tings get more fun to select the Als in the older and lod any oallof them, You willbe
seing technique Chapter.

The Navigator Dialog

“Tne more you work with Power B Desktop, the more you will use the Navigator dialog. Sotseems
appropriate to explain atthis early Juntur some ofthe wicks and techniques hat you can apply to make
our easier when delving into datasources.

‘Let start bang a loser ook at the avaiable options. These ar highlighted in Figure 221

se

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

pes
men { Dons ok “ne
ae = a
cm =
heute Tate - ° =- = .

Figure2-21. The Navigator dialog.

‘The Navigator dialog is essential In two parts

+ Ontheleft The hierarchy ofavalabl datasources. These can consist ofa single
dataset or multiple dataset, possibly organized into one or many folders.

+ Onthe ight: preview ofthe datain the selected clement.

Searching for Datasets

‘There wilnevtably be cases where the datasource hat youare connecting to wil contain hundreds of
datasets. This especialy re for databases. Fortunately, Power BI Desktop les yow filter the datasets that
are displayed extremely easy

1. Inthe Navigator dialog, clickinsie the Search bo.

2. Enter part ofa dataset name that you wantto lola.

3. Click the magnifying glass con at the rghtof the Search box. The liso datasets

‘wll e tere show only datasets containing the text that youentered. You
an see this in igure 222

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

Figure 222. Dataset searchin he Navigator dialog

Once you have previewed and selected the datasets that you want to use, imply lick the cross atthe
rightof the Search box. Navigator wil lear he filter and display all the datasets inthe datasource

Display Options

{ekg Display Options wil show a popup menu wth wo options:
+ Onlysteced items
+ Enable data previews

Only Selected tems

Selecting this option wil prevent any datasets that you have not selected fom appearing inthe data
source pane,

Enable Data Previews

Selecting this option will show a small subset ofthe data aallable in the selected dataset. You could choose
‘to disable data previews te connection tothe source data son.

Refresh

you need, you can refrsh ther or both ofthe following:
+ Thesource data
+ Thedaw preview

‘Source Data Refresh

‘ching the preview button under the search bar wl rfresh de source data inthe source di

a

APT = OSCOYERNG AO LOADNG LAS DAA TH ROHR EDESA

Data Preview Refresh

(ching the preview buton on the top ight ofthe Navigator alg vil refresh the preview data visible on
herb.

Select Related Tables

(ching he Select Related Tables button sony valid for database sources, such as Microsoft Access or
(rade fr instance. Ifthe source database has been designed correctly o include joins between tables, then
this option wil automatically slet all tables that ae linked to any tables that you have already selected.

Adding Your Own Data

Al the data you need may note always avaiable You might find yourself needing to adda it of products à
‘group of people, o indeed, any kindof data tothe datasets that you have loaded ino Power BI Desktop.

"The development team a Microsoft has recognized this eed, andes a simple solution: you can
eat your own ables of data complete he callecion of datasesin a Power Bl in-memory data model.
“Then youcan enter any era data that you need, on the By

1 Inthe Power BI Desktop Home ribbon, click Enter Data. The Create Table dialog
vil appear

2. Clicktheasteriskto te right of Column! o add column.
3. Emterdedatathatyou ned. The dag wi ok Ike the one shown In Figur

Figure 228, The Create able dialog

4. Entera name forthe table inthe Name fed tthe bottom ofthe dialog.
5. ClickLoadto load she daa im the Power Bt in-memory data made.

ing facies in the Create Table dialog are extremely simplistic. You can delete, cut, copy, and paste
data and columns, butthatisabout all that you can do. However, thisopton can, nonetheless, be extremely
‘sel when you need toad some last-minute datatoa model

62

OPER à OSCOVERNGAND LAND FLE USED DAA HTH OER DESKTOP

ote This an extremely simple process that s designed for small amounts of dat. you need mare
than usta hand of rows and columns, you coud be beter served by creating the data in Excel and then
Icading it into Power 8 Desktop.

Conclusion

{inthis chapter you hase sen how thls powerful addon tothe Microsoft business intligncetolset,
Power BI Desktop can help you find and load data from a varletyofFe based datasources. These sources
an be Access Excel, CSV XML, ISON, or ext fes they could come from enti folders of dentally
‘structured tex/CSV les.

Tam sure tha you can sea pattern emerging inthe course ofthis chapter Indeed, this pater wal
continue as jou progress to loading tables from relation databases in Chapter. The process is nearly
always

1. Know the ype of source data that you want look
2. Finde letales ou acces the data.
3. Examine the data nd select the elements hat you want o load

You have seen tha Power Bl Desktop will let you se a sample ofthe contents ofthe data sources that
can read without needing any other application This makes asuperb tool for peeking into datasources
and decidingil fle actualy contains the data that you ned. Indeed, Power BI Desktop’ Navigator can
‘help you ter muldple daases in XML les or Access databases, preview each dataset, and only select the
‘nes that you ant load. OF course, i ean also load dozens of datasets a once hey all are sored in the

"However file-based dat sources are only a mal part ofthe picture, Power BI Desktop can als load
«ata from a wide range ofrelaional databases and data warehouses, We wil ake ook at some ofthese in
thenertchapter

ss

CHAPTER 3

Loading Data from Databases and
Data Warehouses

Much ofthe words corporate data currently residesin relational databases, data warehouses, ad data
‘warehouse applances Power BI Desktop can connec to many (i not mos) ofthe worlds eading databases
“and data warehouses. No only that, but can also connect io many o the lesser-known or more niche data
Sources that are currently available, This chapter wl show you how o extract data fom several ofthese
‘ita sources to power your analytes sing Power I Desktop. Indeed, you wil discover tha once you have
learned howto ennecttoone or two databases, you have learned how © use neal all of therm, thanks to
the standardized approach that Power BI Desktop brings o data extraction.

‘Once again, in this chapter willbe sing set o example ls that ou can find on the
Apres web se. you hate followed he instructions in Append, then these ils wil be nthe
‘CAPowerBIDeskiopSamples\H03 folder. Be avare however that no al the example inthis chapter use
sample daa that valable onthe Apress web st. In some cases suchas Oracle and FileMaker Pro,
wilt you load your own sample data or use the sample data tha can be installed withthe databases
remeros

Relational Databases

Being able to access he data stored in relational databases is essential for much today business
intelligence. As enterprise grade relational databases til hold much ofthe worlds dat, yo real need to
‘now howto tap into the vast mine information that they contain. The bad news stat here ae many,
many databases out here each with is own intricacies and qui, The good news that once you have
learned load data from one of ther, youcan reasonably expect beable to use any of them

Tithe wal world, connecting to corporate data could require you o have a Logo name and possibly
password hat wit you connec (unless the database can recognize your Windows login). imagine”
‘hat you wil also require permissions to read the tables and views tha contain the dat. o the techniques
¿described here ar probably the easy bit The hard pr is convincing the guardias of corporate data that
you actually ned the data and you shouldbe allowed ose.

“The databases that Power BI Desktop can currently connect to, and can preview and lod data from, are
sven in Table

‘Adam Asia 2018 ss
A Asp Pro Poe Bestop

OPER LOAD DEA FROM EASES AND DAA WARS

Table2-1. Database Sources

Database, Comments

SQLServer database Let you connecttoa Microsoft SQL Server on-premises database and
Import records om all the data tables and views that you ar authorized ©

Access database Letsyou connecttoa Microsoft Acces leon your network and load
queries and ables,

SQL Server Anais Letsyou connecttoa SQL Server Analysis Services (S5AS) data warehouse.

Services database "This can be either an online analytical processing (OLAP) cube ora
Inmemory tabular data warehouse

Onde database Lets you connectto an Oracle database and import records from all the
data tables and views that you are authorized acces.

IBM DB2database Let you connectto an IBM DB2 database and import record fom all the
data tables and views that you are authorized w acces.

IBM Informix database Lets ou conneettoan IBM Informix database and Import record fem all
the datatables and views that you are authorized to acess.

WWM Neterza Let you connecttoan IBM Netezza data warehouse appliance and import
records rom al the data tables that ou ae authorized 1 acess.

MySQL database Let you connectto a MYSQL database and impor records from all e data

es and views that you are authorized to acces.

PosgreSQL database Let you connectto a PostgreSQL database and import records tom al he
dat tables and views that you are authorized 0 access,

Sybase database Less you connecttoa Sybase database and impor records from ale data
tables and views that you ae authorized to access.

‘Teradata database Let you connectto a Teradata database and impor records from all the
data tables and views that you are authorized to access.

SAPHANA database Let you connecttoa SAP HANA in-memory database and impor records
From al theobjectshutyou have permission to acess.

‘Amazon Redshit Less you connectto an Amazon Redshift database and import records from
all he data tables and views that you ar authorized access.

Imp Let you connecttoan Impala database and import records from all the

at tables and views that you are authorized to access

ote As the ist of database and daa warehouse sources that you can connect rom Power El Desktop
‘continues o evo, his Ist could see several new tems bythe ime that you read this book.

As wel as connections forspecfic databases, Power B Desktop contains generic connectors that can
help you to read data rom databases that ae not specially in he lis of avalable databases. These generic
connectors are explained in Table 2

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

‘Tabte3-2, Generic Database Access

EN] Comments

‘ODBC datasource Lats you connect over Open Database Connect to a
database or datasource

OLE DBdatasource Lats you connect over Object Linking and Embedding

Database to a database or datasource

Be warned hatthese generic connector will not work with any database However hey should work
‘wth a database for which You have procure, installed, and configured a valid ODBC or OLE DB déve.

Note Although Power B Desktop castes Microsoft Access as a ltr database | prefer o handle it
asa le-based source. For his reason, MS Access data was discussed in he previous chapter

SQL Server

Her ll use the Microsoft enterprise elaional database SQL Server—as an example o show you
"howto oad data froma database into Power Bl Desktop. The fst advantage ofthis setups that you
probably do not need o install ny software to enable access to SQL Server although tis isnt always the
‘ise, sota through with your IT department). second advantage thatthe techniques are pretty
Similar to those used and applied by Oracle, DB, and dhe ther databases to which Power BI Desktop can
connect Furthermore, you can lod multiple tables or views fom a database at once To see his in ation
(Gnd presuming thatyou have created the database CaralesData as described in Appendix), take the
following steps:

1. Openanew Power BI Desktop application

2. Inthe Power Bt Desktop ribbon, click the smal angle ah boom ofthe
Get Data button and hen lick SQL Server The SQL Server Database dialog,
il appear

3. Enerthe server name in the Servr textbox. This wi bete name of your SQL
Server or one ofthe SQL Server resources used by our organizaron.

4 Enterthe database name; fyou are using the sample data, be
CarSaes Data.

5. GiektheImportbutton. The dialog wil look ike Figure.

67

OPER LOAD DEA FROM EASES AND DAA WARS

Figure. The Microsoft SQL Server Database dialog

6. ClickOK. The SQLServer Database dialog wil appear Assuming that you are
Authorized use your Windows login to connectto the database, leave “Use my
‘crrentcredenl” selected, as shown in Figure 32

Figure3.2. Thecredentas Database dialog

7. Click Connect. While the data isbeing loaded, Power BI Desktop wil display the
Load dialog and show the oad progres fr each selected table-You cansee is
in Figure 3.

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

‘igure. The Load dialog dspaying data load progress

8. Power BI Desktop wi connectt the server and display the Navigator dialog,
contaningall he tables and viewsin the database that you have permission
to see on the server you selected. In some case, you could see a dilo seyíng
thatthe data source does not support encryption Ifyou feel happy with an
‘unencrypted connection, then dick the OK button for this dialog,

9. Clickthecheck boxes forthe Clients, Colors, Countries, Invoices InvoiceLines
and Stocktables The data forthe mos recently selected dataset appears on the
Fight ofthe Navigator dialog. as shown in Figure 3.

OPER LOAD DEA FROM EASES AND DAA WARS

Figure 3-4. The Navigator dialog when slecting multiple tems

10. Clicktoad,

1. The Power Desktop window wil open and display te tables that you selected
Inthe Held ist inthe Report window when you dick OK.

‘Since si very smilarto the way in which ou loaded data from Access inthe previous chapter
imagine thatyou are getting the hang of how to use database sources by now. Once again the Navigator
logis a simple and cent way to select he datasets hat you wantto use in your reports and
dashboards.

“Wen selecting mute tables or views, you wl ny eve ses the content ofa single data source in
‘he Navigator og. However, ou can preview the contents ofan ofthe selected data sources (o even any
‘hat are not selected simply by clicking the table or view name, Mis wil not fete choice of selected tables
and views that you want to ad into Power Bl Desktop.

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

ote You can enter the server IP address instead of he server name it you prefer. there are several SOL
‘Server instances on the same server, ou wil need to ad a backslash and te instance name. This kin of
(ad information can be obtained rom corporate database administrator

Automatically Loading Related Tables
oral dabas ae nent vaa ice uc composed may inenepeden ab
Inked, youl fequndy ne oder bis ob led youre
Knving wich bls scie ot ahray egy. Power BI Desp tw help you by automatica
dengue tht ext inte ss database een table o va you can apy lt the
‘Slectonsot abies have been designed o work oper
‘Do efallowingtsee ened pup fae:
1. Conneciothe source database as described nthe previous section.
2. Inthe Nortel cick able contain atathatyou need
2. Gite “sareimed tbe” burn.
nytables har int ine database a seed. You en deselect any ables that youd not wan,
«foot More Imporandy you ca che nares l De sica blero lomo.

ote Sometimes you have to select several tbls in tum and click "Select related tables” o ensure that
Power 8 Desktop wi select ll te tables that are necessary to underpin your analysis.

Database Options

‘The world ofrelational databases fortunately or unfortunately lite more complex than the world of
‘les or MS Acces Consequently, there are few comments 0 make about using databases as a data source;
special, howto connect them.

‘Rt let ver the intl connection tothe server. The options are explained in able 3

Table33. Database Connection Options
Option Comments

Server You cannot browse to And the server and you need to type or paste the server name.
the server has an instance name, youneed to enter the server and the instance
Your IT department will be able supply this fou are working ina corporate

environment.

Database you know the database, thenyou can enter (or paste) there. This resets the
‘numberof valable ables in the Navigator dalogand makes nding the correct
table r view easier

SQLstatement __Youcamentera valid snippet of ESQL that tums data from the database,

a

‘PERS LOAD DA FROM DADAS AO DAAMAREOUSES

‘These options probably require litle mor explanation So lets lookateach one in turn

Server Connection

Ivisfundamental shat you know the exact connection sting fo the database hat you want to connect,
“This could be the following.

+ Thedatabase server name.
‘+ Thedatabase server name backslash and an instance name (fiher is one)

+ Thedatabase server IP address.

+ The database server IP address backslash, and an instance name itherisone)-

+ ifthe SQL Server instances using a custom port, you mustend the server name with
“comuna followed by the por number This inevitably, a question fr corporate
DBAS

‘+ Myouare running singe SQL Server instance on your own PC, then youcan use
‘he name oethastorelertotheserve

Note A database instance Isa separate SOL Server servi running alongside others on the same physical
‘or vital server You wil always need bth the server and this instance name (there sone) to scoesstly
‘connect. You can als speciy a timeout period if you wish,

‘Most SQL Server instances hast many, many databases, Sometimes these can number inthe hundreds.
Sometimes, inevitably, you cannotremember which database you wantto connect o. Fortunately, Poser BI
Desktop can et you browse the databases on a server To do ti, do the following:
1. Inthe Power BI Desktop sbbon, click the small langle at the bottom ofthe Get
ata buton and then clek SQLServer The SQL Seve Database dog ll appear.
2. Entertheservernamein the Server text box and lick OK. Do not entera
database name, The Navigator window opens and display al the avaliable
databases as shown in Figure 3-5, Of course the actual contents depend onthe
Server hat you ate conneetngta

n

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

‘Figures. The Navigator dag when selecting databases

‘You can se rom Figure. that you click the small angle tothe eft ofa database, then youre
able 1 see al the tables and views that are accessible to youn this database. Although thiscan mean an
‘overabundance of possible choles when looking forthe tables) or view(s) hat you want, rs nonetheless
‘onvenient way of reminding you othe name of dataset that you require.

Tip The actu databases that you wil be able to see an a corporate server wi depend onthe
‘permissions that you hae been gen. you cannot se a database, then you wil have to tak tothe database
administrator to sort out any permissions sue,

Searching for Databases, Tables, and Views in Navigator

Afyou ar overwhelmedby the sheer volume oftablef) and view(s) hat appear in ie ef panel ofthe
Navigator dialog, then you can se Navigators bull search alt help you to arrow down the set of
Potential datasources.

‘Searching for Databases
Toisolate speci databases do the following:

1. Carryout steps Land 2in he earlier "SQL Server” section 1 connect à SQL
Serverinsancewithoutspectvings database,

‘PERS LOAD DA FROM DADAS AO DAAMAREOUSES

In the Search box othe Nasigaor dal, enter afew characters that you know
ar contalned nthe name ofthe table o view that you ae looking for. Entering,

for example, data on my server ives the result that you seein Figure $6.

Figure 3-6 Using Search with Navigator to find databases

Searching for Tables

you ae searching fr tables, do the following:
1

2

Expand any databases hat you want search for specific table.

Inthe Searchboxof the Navigator dialog entra few characters that you know
ar contained inthe name ofthe table or view that you ar looking for Bering
for ample, ell onimy server gvestheresul that you se in Figure 3

a

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

‘Figure 3-7. Using Search with Navigator to find tables

‘When searching for objects, you can enter the win upperease orlowerease, and the xt ean appear
anywhere in Ihe names ofthe ables or views not just at the star of the name. With every character that
you pe, thelist of potential matches ges shorter and sharer-Onee you have found the dataset that you are
looking for simply proced as described earlier load the data into Power Bl Desktop.

your search does not return the subse of ables in any views that you were expecting all you have
to dois lick the cross at he right ofthe Search box. This cancels the search and delay al the avaliable
‘ables, as el as lars the Search box.

‘you are not onvincedthatyou are seing all she tables and views that are in he database, then
‘lek the small icon tthe bottom right ofthe Search box (it looks ke a small page wih no green rear
arrow). This isthe Refresh burton, which ereshes the connection othe database and displays all the
‘ables and views that you have permission to see. Finally itis worth noting that fering database ables is
very similar to searching for Exel object a technique that you sin the previous chapter

Note SOL Server databases can aso be accessed using the DiretOuery option. This technique is.
lane in the net chapter

Database Security

Remember that databases are designed tobe extremely secur. Consequendy,yowenly se servers,
databases, tables and views you are authorized o access them. You might have to talk to your IT
‘department o ensure that you have the required permissions otherwise the abe that you are looking for
‘nul bein the database, but remain invisible to you.

=

‘PERS LOAD DA FROM DADAS AO DAAMAREOUSES

lp you experience a connection error when fist attempting to connect to SQL Server, simply click he
Et button o rtum tothe Microsoft SOL Database dialog and corec any mistakes. This avoid having lo
sta over

Using a SQL Statement

theres a downside to sing a relational database such as SQLServer asa data source, is thatthe sheer
amount of data that he database stores „even ina single ble—can be dauntngly huge. Fortunately all the
resources ofSQL Server an be ured to fier the data thats used by Power BI Desktop before you even load
the data, Tis way, you do not have to load entre tables of data atthe isk of downingin information before
you have even started o anal i
“Te ollowing are SQL Servertechniques that you can use to extends partnership benseen SQL.
Server and Power Bl Desktop:
+ SQLSHLECTSsmements
‘+ Stored procedures
+ Tblevalued fonctions
"These are, admittedly fay technical solutions. Indeed, you are nota database specialist you could
vel require the services of your FT department tose these options to access data inthe server Nonetheless,
itis worth tkinga quickloakatthese techniques in case they are useful one day.
‘Any ofthese options an be applied from te SQL Server Database dialog. Here isan example ofhow to
fier dat rom a database table using a SELECT statement
1 Inthe Power BI Desktop ribbon, click the small angle at he bottom ofthe
Get Data baton and then click SQLServer. The SQL Server Database dialog
vill appear,
2. Entertheservername and the database.
3. Clickthe tangle tothe lef of SQL Statement (optional). This opens box where
you can entera SQL command.
4. Enterthe SQL command thatyou wantto apply In this case ti SELECT
Clientiame, Tom, Region FROM Data. Clients ORDER BY CLientI0.The
log wil look ke Figure.

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

SQL Server database

Figure 3-8. Using SQL 0 selec database data

5. ClckOK. A sample ofthe corresponding datas dsplayed ina alg ike the one
shown in Figure 39.

ADAMO: CarSalesData

igure3:9. Database data selected using the SQL Statement option

6. Clik Load or Edito cominue with the data load process Altematively, you can
ik Cancel and tra different data load.

‘PERS LOAD DA FROM DADAS AO DAAMAREOUSES

TIP When entering custom SOL (or when using stored procedures, si explained inthe follwing section)
you shou, preferably, spect the database name in step 3.1 you donot give the database name, you wil have
o we three-part notation in your SOL quer. Tats you must a the database name and a period before the
Schema and table name of every able name used nthe query

‘Stored Procedures in SQL Server
“The same principles apply when using stored procedures of functions o return dat rom SQL Server You
‘wll always use the SQL Statement option to ener the command hat wil return he data. st remember that
Ko «ll SQL Server stored procedure or function, you would entr the following elements into the Microsoft
SQL Database dialog:

+ Server <yourservername>

+ Databases che database name>

+ SQL Statement: EXECUTE (or EXEC) center the schema (ere son, followed by
period) and the stored procedure name, followed by any parameters
“is way ether you or your department can create complex and secure waysto allow data fom the
corporate databases tobe read into Power Bl Desktop from enterprise database
“To see this in practice, you can use SQL Serve stored procedure thats included in the sample
SQL Server database to return only asubset ofthe avlabl dat. The stored procedure called
pr Display KCilenData, and you apply tl
1. Inthe Power BI Deskiop ribbon, click the small angle atthe bottom of the Get
Data button and then click SQL Server The SQLServer Database dialog Will
appear
2. Entertheservername and the database,
3. Clickthetrangle tothe lef of SQL Statement (optional) Thisopens abox where
you can entera SQL command.
4. Enterthe SQL command thatyou wantto apply. In his cae, is EXECUTE dbo.
Pr_DisplayUkclentData. The dialog wil look ike Figure 10.

”

cuerens

LONG DAT FRM DATABASES AN DATA MARQUES

SOL Server database

Figure 3-1, Using SQL to select database data

$5. GlckOK. A sample ofthe corresponding daras dsplayed ina dialog ike te one

shown in Figure 3-11

OPER LOAD DEA FROM EASES AND DAA WARS

ADAMO3\SQLSERVER2O16: CarSalesData
aces
Lo
gres. Database dat see wing the SQL Sutton
&. CleckLondor att come the data lad process Alternately, yoncan

ik Cancel and stat diferent dats oad

‘Te data that sretumedin this example sonly a subset of the valable data that has been selected by
the sored procedure. You need to be aware hat stored procedures can perform a multude ftasks onthe
‘source data. These can include selecting sorting. and cleansing the data.

ote A SOL statement or stored procedure wil ony return data asa single table Admite, this able
‘ould contain dat rom several tables or views but tering the source data vl prevent Power Bl Desktop trom
lung data rom several tables as separate queries. Consequenty you coud have to create mulipe queries
rather than a singe load query o get data rom a coherent set of tables inthe datasource.

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

Oracle Databases

‘Ther are many, many database vendors active in the corporate marketplace today. Arguably the most
dominant of them is Oracle So while Ihave used Microsoft data sources to begin the Journey io an
‘understanding of how to use databases wth Power BI Desktop, would be remiss ofme not explain how
10 acess databases rom other supplies.

So now the de to show you just how open-minded Power I Desktop really Is des or lmityou
Lo Micrsof dat sources far fom it Indeed, itis every bi as easy to se database from other vendons as the
source of your analytical reports. Asan example ofthis le’ take alookat loading Orace data into Power BL
Deskıop

Tnstaling and configuring an Oracle database sa montas Consequenty, Tam not providing an
Oracle sample database, but wllleave you ether to discover a corporate database that You can connect
or prefray consultthe many excellent resources avala that do an excelent jb of explaining howto
setup your own Oracle database and install the sample data that savallble.

‘Be aware that connecting to Orace wil require installing Oracle en software onthe computer where
ou are running Power BI Desktop. This too, canbe complex to set up. So you might need some help fom a
‘orporateresouceifyou are planning use race data Power Bl Desktop.

Should you befeling raw, vou ca use the following URL to find the Oracle client softwar. For 32-bit
versions of Power BI Desktop, use the following ink 0 download and install the 2-bt Oracle clint:

wu oracle con/technetnork/ topics /dotnet/ut f1soFt-086879. ht

For 64-bit versions of Power BI Desktop, use the following ink o download and install the 64-bit
Oraci client:

wuoracle,con/technetwork/ database /windos downloads ndex-090165-htm]

you need to check which version of Power B Desktop you are using (3 bit or 6 i), lick
le» Help > About You will see a dialog that tells you which version you are using.

So, assuming that you have an Orace database valable (and that youknow the server name or SID.
aswellasa valid username and password) he following steps show how you can load data from this
particular source into Power BI Desktop. willbe sing standard Oracle sample data that soften installed
‘vith sample databases inthis example

1. Openanew Power BI Desktop application,
2 Inthe Power BI Desktop ribbon clik Database,

3. Inthe Get Data dialog, click Oracle database onthe ight The dialog will
Look ke gure 12

a

OPER LOAD DEA FROM EASES AND DAA WARS

Get Data

Figure 9-12. Connecting an Oracle database

4. Clek Connect The Oracle Database dialog will appear

5. Entero servername in the Server text box. This willbe the name of your Oracle
serverorone of the Oracle Server resources used by your organization.

6 Clik e Importbunon. The dialog wil look ke Figure 3 1

‘Figure S-18. The Oracle Database dialog

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

7. CickOX. The Oracle Database secure dialog wil appear Assuming tha you
are not authorized to use your Window login connect the database ick
Database onthe left ofthe dialog,

8. Enterthe username and password thatallow you to log int Oracle, shown
in Figure 3-14,

Figure 3-14, The Orale Database security dialog

9. Clik Connect Power BI Desktop will connect to th server and display the
"Navigator dialog containing al the tables and views inthe database hat you
have permission to see onthe server you selected. I some caes you cul se
“llo saying that the dat source does not support eneryrion. you fel happy
With anunencrypted connection, then cick the OK button for thls dialog,

10. Expand the HR folder This a standard Oracle sample schema hat eould be
installed on your Orde instance. no, you wilhave 1 choose another schema,
(kt check bores forthe tables that interest you. The data forthe most
recent selected data appears onthe ight ofthe Navigator dialog as shown
In Fgure 3-15

ss

OPER LOAD DEA FROM EASES AND DAA WARS

— a
Figure3-15. The Navigator dag using Oracle data

11, Click oad. The Power B Desktop endow ll open and display he table that
you selected the Fels is inthe Report window when you cick OK. Youcan
‘ee the data Power BI Desktop in Figure 316

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

pure 3.16, Oracle data loaded into Power Bt Desktop

you have already followed the example eater inthis chapter to load data from SQLServer you wll
probably appreciate how much the wo techniques have in common. Indeed, one ofthe great advantages of
‘sing Power BI Desktop is thatoading dat rom diferent data sources follows large similar approach
and uses many of these steps and dialogs. This is especial ue of databases, where the steps are
ray dencal-whatever the database

‘Ofcourse, no two databases are alle. Consequently, you connect to an Oracle instance (or server)
‘butcannot choose a database ar you can in SQL Server (or Ste, or instance). Similar where Orade
as Schemas segregate and organizo data bles, SQLServerhas databases Nonetheless, Ihe Power BI
Desktop Navigator vil always organize data nto a lerarchyoffoldersothatyou can Visualiz he data

E whatever the underying database.

Note Oracle databases can also be accessed using Ih OirectOvery open. This technique is explained in
the ex chapter

Other Relational Databases

ble 2-1 atthe sartofthis chapter contains the ist of relational databases tht Power BI Desktop could
‘connec tos this book went 1 pres. imagine tha the st has grown since this book was published,
However the good news that you probably do not need much more information o connect to any othe
databases tht ar avalable fr you to use as data sources. Simply put. you know howto connect tone of
‘them, you can probably conned to any olthem.

[3

‘PERS LOAD DA FROM DADAS AO DAAMAREOUSES

So am not gong fl out reams ofpages with virally dental explanations of how to get data from
a dozen or more relational databases. Instead sugges that you simply tr to connect using the techniques
that you hae learned inthis chapter or Oracle and SQL Server.

"Be wamed, though thatto connect to arelational database you wil ineiably need to know the
following del:

+ Tnesenername
+ Adatabasename(posbly)
+ Avalidusername (depending on the secur ar has been implemented)

‘+ Avalid password forthe user that you are connecting as (his 10, will depend on the
security in place)

However, you have these element hen nothing should top you rom using range of corporate
datasources a the bass for your analysis with Power Bl Desktop. You il, of cours, needallthe necessary
permission © access he database and he data hat contains.

Ris also worth knowing hat connecting 0 DBZ, MySQL, PostgreSQL, Sybase IBM Informix, IBM.
‘Neteza, SAPHANA, or Teradata can require not only hat the database administrator has given you the
necessary permissions, but also tht connection software (known as divers or providers) has been installed
on your PC. Given the “corporat” natur ofthe requirements may help you tak directo your IT
department to ge this setup in your enterprise landscape

‘One way 10 find out fe software as required o enable connection o specific database has
ben installed sto select he database rom the list avallable in the Get Data log the drives have not
een installed, ou will see a warning similar to he one in Figure

PostgreSQL database

igure3-17. The mising river ale

‘licking the “Learn more link wl take ou o the download page or the missing drivers. Be warned,
however hat configuring data providers can, In some cases, require ie specialist knowledge as well as
access rights on he computer where the drivers have to bo installed

Microsoft SQL Server Analysis Services Data Sources

An Analysis Services database fa data warehouse technology that can contain vastamounts of data that has.
een optimized to enable decision making SSAS eubes{ these databases are also called) are composed of
fas (measures or values) and dimensions (descriptive atbutes) your enterprise uses Analysis Services
databases, you can access them by doing the following steps:

ss

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

1. Inthe Power Bt Desktop ebon click Get Data > More» Database and sleet
SQL Server Analysis Services Database inthe Get Data dialog,

2. ClickConnect The SQL Server Analysis Services Database dialog will appear

"Enter the Analysis Services server name and the database (or cube”) name, I
you know it you are using the sample data from the Apress st or ths book,
{he databases CarSalesOLAP; otherwise, you have to specly our own SSAS
database name. In any case youwill need to use the name ofyour own SAS
server. The dialog wil look like Figure 18.

Figure 3-18. Connecting oan SSAS (multidimensional) database

4 CKOK this is theese ime that you are connecting tothe cube, then the
Access SQL-Server Analysis Service dialog wil appear so that you can define the
(reden hat you are using to connect the Analysis Services database as
‘shown in Figure 3-19.

igure 3.19, SQL Server Analysis Services Credential dialog

5. Acxeptoraltrthe credentals and lick Connect. The Navigator dalog
vil appear

a

‘PERS LOAD DA FROM DADAS AO DAAMAREOUSES

6. Expand the older inthe eft pane ofthe dilo, This wa, you can seal the fact.
tables and dimensions contained in he data warehouse,

7. Selective fat tables dimensions reten only the dimension elements and
mesures that you want to lad. The dialog wil look something ike Figure 3-20.

Navigator

Figure s-20, Seletinganribuesand measures from an SSAScube

8. ClickLoad. The Power BI Desktop window ll open and display the measures
and areibues that you selected the Field ist inthe Report window.

Note you di not enter the cube (database) name in sep , ten the Navigator cog wil spay al the
úavalabl cubes onthe SSAS serve.

SSAScubes are potently huge. They can contain dozens of dimensions, many facttabes and erally
hundreds of measures and auributes Understanding muldimensional cubes and how dey workis beyond
the scope ofthis book. Nonetheless is important understand that fr Power BI Desktop a cube is just
another data source. This means that you canbe extremely selective ast the cube elements Eat ou Toad
Into Power Bt Desktop, and onload the elements that you ned fr our analysis You can oad entire
dimensions or jst afew atributos Jus ke you can load whole fact ables or jst selection of measures

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

‘ote You can fr the data tats ade rom an SSAS cube by expanding the MDX or DAK query
(option item inthe SOL Server Anais Services Database dog. Ten you can ener an MOX query inthe
ox that appears before clcking OK Be warmed that SSAS cubes use queries wien In MOK—a specialist
language that is considered not ways easy to lam. The good news i at an Analysis Services expert has
‘st up a cube caecty, you can see SSAS display folders in Power8t Desktop Query which I another ter that
People use to describe the Query Eto)

Analysis Services Cube Tools

Analysis Series datasources allow youto tweak the selection ofsourc elements ina way hats not
‘llable with other datasources. Essential, you have wo extra options:

+ Add tems
+ Collapse Columns

Add items.

‘When usingan SSAS data source you can ar an me add any tribus or measures ha you cer forgot
‘orthoughtthatyouwould not need when seing up the inal connection.

1. In Power BI Desiop, lick the Et Queres b

n-The Power BI Desktop Query

Editor window wil be displayed. Assuming shat theres only one quer, the
“Manage ribbon wil appear as shown in Figure 3-2
‘query tha youhave previously established.

these, lick the SSAS

= = < are az

Figure 3:21. Cube Tools

‘PERS LOAD DA FROM DADAS AO DAAMAREOUSES

2. Inthe Manage ribbon, lick he Add tems button. The Add tems dialog will
‘appear as shown in Figure 22.

Add Items

Garis mi

Css

ul sucre

ti contre

tif Te Dc

il Dune Dune

St Spee Pos

u arco

til Css cet
De CUT

Figure 3-22. The Add tems dialog
3. Expand any measure groups and select al he measures and atributo has you
want oad,
4 cickOK.

5. Inthe Power BI Desktop Query Editor activate the Home ibbon and dik Close
and Appy

Any changes hat you made are reflected inthe data and the selected measuresand attributes are added
a new columns athe ight of the dataset.

Note Power Bl Desktop Query will not detect any now measures and attributes that you add ar ready
Inthe dataset Sot you add an element a second tine, wil appear vico in the query

9

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

Collapse Columns
Do the following o remove any columns hat you no longer require fom the data source (which can
aceeerate daa efrest),
1. Inthe Query pane, click the SSAS query that you have previously established
(The sample dita fortis connection willbe displayed in he center ofthe Query
Window his yet another term forthe Query Editor window) The Manage
ribbon will appear.
2 Inthe Manage ribbon clik the Collapse Columnsbutton,
‘The columns are removed rom the connection 1 he SAS cube and, consequently, rom the Fields
atthe right ofthe Power BI Desktop window. Tey are also removed fom any visualizations that use ther.

Note Removing columns from Power BI Desktop Query can have a serious domino effect on reports and
shboark, Consequenty, you need tobe very careful when removing them.

SSAS Tabular Data Warehouses

‘The previous section showed you hour o connect ro a SQL Server Analysis Service cube However there are
now two types oF SQL Server Analisis Services data warehouses

+ The“tadional” cube
+ TheTnewer tabular warehouse

A more and more data warehouse (at eas th ones that are based on Microsoft technologies) are
being bul using the newer, tabular technology ti probably worth your while to see hour quik and easly
you ean use these datasources wth Power Bl Desktop Indeed he steps hat you flv o connect ether
‘ofthese data warehouse sources aro vitally identical: However, as Power Bl srapdly becoming the oa of
choice to query tabular data warehouses, ts certainly worth afew minutes to cam how to connect to SSAS
Tabula (a lis often called, for sor)
1. Inthe Power BL Desktop ribbon lick Get Data > More > Database and sleet
SQL Server Analysis Services Database inthe Get Data dialog,

2. Clek Connect The SQL Server Analysis Services Database dialog wll appear.

3 Enterthe Analysis Services server name and the tabular database name (we
don’t tend weal hese cubes), ou low i Ifyou are using he sample data
fromthe Apres ste for this book the database is CarSalesTabular, otherwise,
you have to specify your wm tabular database name. In any case you will need to
‘se the name of your own SSAS server

4. Cickimport

5. The dialogwil look ike Figure 25

‘PERS LOAD DA FROM DADAS AO DAAMAREOUSES

SQL Server Analysis

Figure3:23. Connecting o an SAS (multidimensional) database

6. Clkk OK. ft the irsttime thar you are connecting tothe tabular warehouse,
‘then the Acces SQL Server Analysis Service dialog wall appearso that you can
define the credentals that youare using to connect tothe Analysis Services
database, where you wil have to acceptor alter the credentials and click
“Connect The Navigator dialog will appear.

7. Expandihe fldersin the lt pane of the dialog, This way you ca see al the
‘ables contained inthe data warehouse. These may or may not—be structured
‘sfactsand dimensions as was the case with a“dassle”SSAS data warehouse,

8 Select the ables tha you want to load. The dg willooksometingliksFgure 3-24.

Figure 9-24 Seletingatribues and measures roman SAS tabular datasource
2

OPER = LORONG DEA FROM ONAEASES AND DATA WAREHOUSES

9. Click Load. The Power BI Desktop window wil open and display the measures
and tribus that you selected in the eds lst in the Report window.

‘ip. Youcan ite the data thats loaded rom an SSAS tabuar database by expanding the MOK or DAX
‘query opor item nthe SOL Server Analysis Services Database log. Then you can enter a DAX query
Inte box that appears before cickng OK. SSAS tabular databases use queres writen in DAX, whichis the
language the Power ef uses (and which you wil stato lam In Chapters 11 though 13)

Import or Connect Live

Sofarin this chapter have suggested hat you use the Import option when sourcing data from Microsoft

SQL Server databases and data warehouses. This ls because the alternative, Connect Live which als,

Anoun as Direct Quen), such an important pat o Power BI Desktop that have prefered to make K the

subject of shoe separate chapter. You wl discover how o use this fa eaching and impressive technique.
the nex chapter

ODBC Sources

you have seen inthis chapter andthe preceding one, Power BI Desktop can connect toa wide range of
data sources However, thee willahrays be database applications fr which theres no spciicconneetor
builtin Power B Desktop.

‘This is where genero solution called Open Database Connectivity (or ODBC) comes ino play. ODBC
Asa standard way to connect to data sources, most of which are databases or structured ke databases
‘Simply put fan ODBC driver exists for the application that ou want connect, then you can load data
rom tno Power BI Desktop.

‘Hundreds of ODBC divers ave bee writen. Some ae fly available, others require you to purchase
“license. They exist ora wie spectrum of applications ranging fom those found on most PCsto niche
products.

‘Although ODBC is designed as.a standard way of accessing ta In applications each ODBC drivers
slighty diferent rom every other ODBC driver Consequently, you might have 1 spend a ie time learning
{he quirks ofthe interface for the drive that comes with the aplication that you want o connec o.

Tn this section we wll use FileMaker Pro asa datasource. This products a desktp and server database
sytem that has been around for quit sometime. However, there ls currenly no specific Power BI Desktop
‘onnector frit. The good news is that FileMaker Pro does have an ODBC diver we wll use ODBC to
‘onnec to FileMaker Pro rom Power Bl Desktop

have w add that am not expecting youto install copy (even fit sony wal copy) of FileMaker
‘Pro and is companion ODBC dvr to carry ou this exerie. What do want to expla, however, show
sou ean use ODECto connect toa wide ange of datasources where an ODBC driver isavalable So fel
fre to download and install leaker ro and its ODBC drverf you wish, but you wil have to refer to the
FileMaker Pro documentation for an explanation of how o do this.

Assuming that you have an ODBC-complant data source anda working ODBC deter for this data
source, here show to load data Into Power BI Desktop using ODBC:

1. Run te ODBC Data Source Adminstrator app. This s normally in the older
CCA\ProgramData\ Microsoft Windows Start Menu Programs Administrative
Tool. Be sure to use the Gti ern four using 64-bit Power BI Desktop
orthe 32-biversion you are using 32-bit Power BI Deskop.

ss

OPER LOAD DEA FROM EASES AND DAA WARS

2. Clickthe sytem DSW tb, Youshould see thedilog shown in Figure 325.

(ums frm MON Din Ta, Cor A

we
=

5 es an

Figure3:25. The ODBC DataSource Adminisrator

3 Click Add. You wil see thelist ofallcurrenty installed ODBC drivers on your
‘computer This should look something ike the dialog shown in Figure 3 26.

mn osé

CN En...

CES] os
Figure S-26, The sof nsalled ODBC drivers

4... Selectihe appropriate ODBC driver corresponding tthe

source thatyou

‘want connect (FileMaker ODBCin this example), you cannot ee the

‘ODBC diver you need w insal—orrelnsall—the dice

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

5. Glick Finish. The configuration dialog forthe specific ODBC diver that you have
Selected ill appear Ifyou are using FileMaker Pro the dialog wl ook
Île Figure 2.

efit ct mea cae a cn

er ét da

ocre ar cn, been
ODBC for
FileMaker

Figure 327. The FileMaker Pro ODBC configuration asistan?

6. Clk Next andentera name and a description fr this particular ODBC
‘connection. This should look something like the dialog shown in Figure 321.

Ft Cetin x

=

come

Opscfor ens
FileMaker

E [en] me |

igure3.28. Naming the ODBC connection or FileMaker Pro

9

LOAD DATA FROM DATABASES AND OX MARIUS

‘lick Nex and enter localhost asthe hostname you are using a FeMaker ial
Version on our local computer Otherwise, enter the IP address ofthe FleMaker
‘server You should see the log shown ln Figure 5 2.

opt?
er apre o

igen ret

ODBC for
FileMaker

E] Ea
Figure3:29. Specifying he hos for he ODBC data

8. Clik Next and select the database in FileMaker Pro at you want t connect,
‘You wil see he dilog shown in Figure 30 (you are nor using FleMaker
Pro—remember hat hese dogs cn vary dependingon the spel ODBC diver).

ec
Dress

Ina
Comet gr

CR
ODBC for a
FileMaker =

si [m] m |

Figure 3:30. Specifying the database fr he ODBC data
96

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

9. GlckNex. The ODBC configuration dalg wll sume the specifications forthe
conection. This could look somethlnglik the one shown in Figure 3 1.

Fate Cention x

ODBC for
FileMaker

ed eee
eure, The 0D8C connection comen

10. ClickDone. Youwil recur tothe ODBC Data Source Administrator where
‘you il see the System DSN thatyou just created. The ODBC DataSource
‘Adminstrator dialog should lok something lik the one shown in Figure 32.

in CN OB Sen Tas Co A
en tac
=

Qo

Figure 3:32, The ODBC Data Source Administrator dialog with an ODBC driver configured

7

OPER LOAD DEA FROM EASES AND DAA WARS

1. ClickOK. This wil dose the ODBC Data Source Administrator dialog.
12. Launch Power Bt Desktop.

18 ClickGet Data > Other

18. Select ODAC from the avalable data sources on the ight.

16, ClickConnect. The From ODBC dialog wll appear

1%. Expand heist favallable DSNs. The From ODEC dialog ilooksomething
like the one in Figure 3-3.

From ODBC

aan a)

igure3:33. The Power BI Desktop From ODBC dalg to ecct an ODBC data source

17. Select the DSW that you created previous) (ilcMakerPorPowerBl inthis
campo).

18. ClickOK. The Credential dialog wil appear

18 Choose Windows integrated security or click Database on the left and enter
‘the username thathas permissions to connect using the ODBC diver. The
‘Credentls dialog wil ook something ike the one in Figure 334.

Figure 224. The ODBC driver security dialog

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES
20. CliekConnect You will se the data thas valable in the ODBC data sourein
the Navigator window.

21. Selecthe tables thatyou want toad into Power BI Desktop Voucan se the
data contained inthe selected able in Figure

ma
Figure335, The Navigator dale when using an ODBC source dialog

22. ClickLoad toad the

from the ODBC source into Power BI Desktop.

relie that this process may sem a itl laborious at fis Yet you have to remember that wil in
all probability, only setup the ODBC connection once. Mer that you can use tt connect tothe source data
soften as you want

You need tobe aware that each and every ODBC drivers diferent So the appearance ofthe dialogs
{instep to 10 wl vary slighty with each diferent ODBC driver that you configure. The key elements wil,
noneteles always be the same. Theyare

+ Namethe DSW,
+ Specify the hostcomputer forthe data.
+ Define the data reposhory (or database).

‘Theres much more that could be writen about creating and using ODBC connections to load data into.
Power BI Desktop—orindeed into any numberof destination applications. However Iwill have to refer you
to he wea ofavallable resources bot in print and online you need to learn more about this particular
technology. good starting points the Microsoft documentation that explains the ierence between
‘System, Use and Fle DSN and describes many ofthe key elements hat you might need 10 know.

Note FleMaker Pro must be open andor runing for an ODBC connection to work Other ODBC sources
Gould have thir own speci quis.

Asa final, Lean only urge you to procure all he relevant documentation for he ODBC driver that you
Aneto use with Powe B Desktop. Indeed, you are using an enterprise data source tha uses ODBC
drivers, you may hive corporate resources who can configure ODBC for ou.

9

OPER LOAD DEA FROM EASES AND DAA WARS

OLE DB Data Sources

OLE DB (stor or Object Linkin and bedding Database) technical what snow asa application
Programming terface (AP), Les technical ta technique or connecingto daba sources na
manner
FO ina somewhat sima fashion 10 ODBC, you can use OLE DB to connect to data sources (which are
ote dens hough can be ter sores ofa). Indec, you may fn that LEDO rae
yo connect ma database ven another method ext
So whatever the we you dor OLEDS, its weil word geting to knw bow works this
example wil use OLE DB to connect SQL Server andthe sample database that you A an at he star of
cap

1. Open anew Power BI Desktop application,
2 Inthe Power BI Desktop ribbon, lick he Get Data burton.
3. Clik Other onthe lft then click OLE DB on the ight. The dialog wil lok ike

rares
Get Data
= om
oie i
pr ee
= À mm
am
ee
[eau

‘Figure S-36. Starting an OLE DB comecton

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

4. Glick Connect. The From OLE DB dialog wl appear should look ke Figure 3

From OLE 08

Figure3:37. The From OLE DB dial

5. you havea ful working connection string enterit in he Connection Sting
texbor.

6 you do nothavea connection sting cick the Bull button. The OLE DB Data
Link Properties dalg wl be displayed, as shown in Figure 3-5.

Er x

oe nn 10 nom Damon Some 0608 Pa
et ote oes Denen Spe LED
RG Poe em
A Geo men 2
Nua OLE 08 Post Sons us

LE Pare DoS

jer m]

| ee)
Figure 338. The OLE DB Data Link Properties aies

7. Selectthe OLE DB data provider that you want to use, this example wi be
“Microsoft OLE DB Provider or SQL Server.

m

OPER LOAD DEA FROM EASES AND DAA WARS
8. ClickNext-The Connection properties pane ofthe OLE DB Data Link Properties
og vil appear

9. Selectan available SQL Server (or entr ts name) from the “Selector entra
servername” popup.

10. Selec the ype ofsecurty, and enter username and password ifyou have
selected tous a specific username Instend of using Windows NT Integrated
security.

12 Select the source database fom the “Select database on the server” popup.
‘The dalog will look somethinglie the one shown in Figure

ECTS x

‘rete es ret

E

igure3-39. The Connection properties ofthe OLE DB Data Link Properties dialog

12. Clickthe Test Connection button to ensure thatthe connection val You
should see the message in Figure 10

Es)

Figure3-40. The est connection let

102

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

18 GlekOK Power BI Desktop wil build the connection string and insert into the
From OLE DB dialog as shown In Figure 51

From OLE DB

igureS-41. The From OLEDB dialog witha valid connection string

14 GekOK: The Navigator window willbe displayed as shown in Figur 3-2.

Figure 3-42. The Navigator window for an OLE DB connection

15. ClickL.oad wo load the data into Power BI Desktop

Note His Isa st connchon to an OLE DB source, you maybe asked fora username and password, as
vas the case wth arr examples in his chapter

103

OPER LOAD DEA FROM EASES AND DAA WARS

‘ou need tobe aware tha an OLE DB connection require thatthe OLE DB driver (or “providers
Installed on the computer where youare running Power B Desktop. However wht ral interesting
is atan OLE DB connection can be reduced 1 simple connection sting So fou need to share the
connection wi other users, you can simply e-mail he connection string hen in many cases Your
colleagues can hen simply paste the connection string int the From OLE DB dialog in Power BI Desktop. In.
‘other words (and using ths example as a model) you can simply send the folowing texto aco-worker

provider=SQLOLEDS.3sinitia} catalogeCarSalesbata;data source=ADMON\ SOL SERVER2016

‘They can use his string to connect oa speci database by pasting tint the From OLE DB daog

‘There ate other advantages to using OLE DB connections oo. Specialy, you (oryour IT department)
(an provide a high level of configuraionin the connection string to speedup or ohenwise amelioat the
access the data, This could be by specifying a mierored server that isto be used for reporting torelieve the
pressure on a main server for instance. this leve the technical ramieations wi depend on dhe OLE DB
data source aswell asthe driver used, and consequent are outside the scope ofthis book.

Modifying Connections

you arewosking ina seuctured development envronment-or evn you ae testing dashboards ona

dataset that's ether an old version o possibly on a non Ive server-you could want at some polnto switch

froma curren datasource to another source. Power B Desktop let you do this. However, switching data

sources will ony work the structure ofthe source andthe destination data ar dental Practical, this

‘means that the server and database canbe named diferent, but the tables and feds must have the same
see his in action you cando the allowing:

1 Inanesising Power BI Deskop fl, the small angle atthe bottom right of
‘the Balt Queries butonin he Home ribbon, and select DataSource Seins. The
Data Source Setings dialog wil be displayed. Figure 3-43 shows you ths dialog
{for te SQL Server connection tat you saw at the start of this chapter,

Figure 9-42, The DataSource Settings dialog

2. Clicktheconnection that you wantto modif.

100

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

3. Clik ChangeSouree The dialog that originally allowed you to specify the data
source (in this example thatthe SQLServer Database connection dialog) will
appear You can se this in Figure 3-14.

SQL Server database

igure3-4. The connection dialog,

Specify diferent serverand/or database sie datasource.
{ckOK. You wi tum tothe Daa Source Settings dialog.
Ce Close

{lckthe apply Changes button tha appears under he Power BI Desktop ibbon
atthe top ofthe seen,

non»

ote sis rt me that you ao establishing a connection t his new server and database, you
wil have to spect the credentials to use.

Assuming that the new datasource contains he same able names and structures, the existing data wll
replace withthe data fom the new source that you specified

have stress again that this technique wil only work the underying database metadata s denial
across the wo servers Ifthe data structures are not the same, youwil se an error dialog similar otha,
shown in Figure 115

105

OPER LOAD DEA FROM EASES AND DAA WARS

Apply query changes

Acoso

Figure 3-45. Modifying a database connection

cases like this, you may well have to rebuild anew Power BI Desktop le using the new datasource,
“This could mean re-creating or copying any data mashupsand formulas (aswell as actual vauallzations)
from the od versontothe new fl,

Changing Permissions

Ivisalt0 frequent when working with databases and data warehouses to encounter permissions problems.
Ia could be that you set upa connection t a database which required you to change the password at alter
‘date. Meanwhile the password stored in Power BI Desktop isthe old version. So when you ry to update your
dashboard, you hit Blocker.
Fortunately (assuming at least, hatyou low the new password) you can update your stored
1 Desktop. Asan example let's suppose that you want to update Your Oracle password.

1. Inanesising Power BI Desktop fl, lickthe small angle atthe boom right of
‘the Balt Queries butonin he Home ribbon, and select Data Source Setings.

2 Click Global Permissions. The Data Source Sing dialog willbe displayed.
Figure (0 shows you this dsl or he current Power BI Desktop connections.
‘onmy PC.

106

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

Data source setings

9 ont
+ om

Figure 3-46, Permissions for current connections

3. Clickthe name ofthe connection that you want to change (pdborel in this example).

4. Giekthe Balt Permissions button. The Et Permissions dialog willbe displayed,
2 shown in Figure 3-7.

Edit Permissions
ara

Credits

CET ow

Privacy Level

tive Database Queres

107

OPER LOAD DEA FROM EASES AND DAA WARS
lick Ei. The connection permisions dialog wil be displayed shown earierin
Figure 3 ln the section that explain howto connect to an Oracle database)
Modif the password (or even the user name fthisis required)

lic Save, This returns yout the di Permissions dialog.

‘lick OK. You wll return the Data Soure Stings dialog.

lick Close to return to Power Bl Desktop

‘ou can now refresh the

eons

using the new permissions that you have ustentered.

Refreshing Data from Databases and Data Warehouses

Loading data from databases and data warehouses only means hat a snapshot ofthe source daras copied
into Power Bt Desktop. the source data s update, extended, or deleted, then you need to get the atest
‘version ofthe data You want your analyses to elect the current tae ofihe data

Essential you have wo options w do this:

Refresh llth source dat from all he data sources that vou have defined.
“+ Refresh one or more ables individually.

Refreshing the Entire Data in the Power BI Desktop In-Memory Model

‘Theres only one way tbe certain that all your datas upto date Refreshing the entre data may take longer,
ut you willbe sure that your Power Bl esop Ble contains the latest valable data rm al he sources
that you have connected.
"o carey out complete refresh:
1. Inthe Home ribbon, clickthe Refresh button. The Refresh alo will appear
‘showing all he daa sources that are currently being refreshed. This dialog will
lock like the ome Figure.

Refresh

cars
‘resting connection in mode

resting connection in med

E=

Figure3-48. The Rfrsh dialog

‘TERS LOAD OAT FROM DATAEASESANO DAAMAREOUSES

ote A fl data refresh can take quite a wil if he source datas voluminous or the network
comecton is stow.

Refreshing an Individual Table

you are certain hat only ane or more abs need wo be refreshed in your Power BI Desktop data model,
then you can choose to refresh tables individual. To do his:

1. Inthe Fils pane right-click the able that you want 0 refresh,
2. SelectRefresh Dat inthe contest mena. This ilustated in Figure 3-10.

Fetes ts
ese Quey

Figure 3-49, Refeshinga single table

‘The efes dialog wll appear (possibly only bre) and the existing data for this table vil be replaced
vith de atest data.

Conclusion

this chapter you have sen how to connect Power Bl Desktop to some ofthe plethora of databases and
data warehouses that curren exis Moreover you have sen that Powe BI Desktop comes equipped "out
‘ofthe box” with connections to mostof the databases that curently ex na corporate environment.

Power BI Desktop does notimityoutoa predefined se ofavallble datasources. Provided that your
source database comes complete with one of the generic data providers-ODBC or OLE DB-then Power BI
Desktop can, in all probabil, acess these sourcestoo.

Moreoer corporate data sources can evolve and change. You lso saw how Power Bl Deskopallows
sou to update the permissions that you originally specied fra connection withthe tes access detal.
You even saw how to switch between data sources and update the data both selectively and globally

Despite her usfulnes storing and structuring large quantles information, corporate databases
«an presentone small drawack theme that can take t load the data fom the database into Power
BI Desktop in-memory data model. The development ream at Micros lei aware of this potential
shortcoming. The team has come up with a solution, called DireciQuer (or Connect Live), which you can
cover how to implement in the nex chapter

109

CHAPTER 4

DirectQuery and Connect Live

“The previous chapter showed you howto access data from a ange of database and data warehouse sources.
‘This process sboth simple and efficient as you saw. However there is one stage inthe proces of fetching
st that an taka itl ime, especialy ou are dealing wth large dataset This the “load” phase
‘where the data from the source system i transfered ino the Power B Desktop in-memory model and
compressed.

"The Power developers have clearly looked hard a ths question, and have come up with potentially
far reaching solution: connect direct tothe datasource and avoid having to download the data, This
technique sealed DirecQuer ris wort noting that Microsoft now calls the direct data connection to
SQL Server Analysis Series "Connect Live” However, wll eonsder this tobe, nevertheless, part ofthe
DireerQueryechnlogial approach.

Inthischapter we wll ake a look a when you can use DireciQueryin Power BI Desktop, and what the
‘advantages (and, ofcourse, any drauhacks) are to using these data connection methods.

DirectQuery and Connect Live

To begin with, you need to know that DirectQuer (and Connect Live) curren only works with afew ofthe
lab data sources that Power BI Desktop cameonnectto.Atthe ime of writing thes are

+ SQL Server Database
+ SQL Server Analysis Service (clasle”SSAS and tabular)

+ Oracle Database

+ Tendata Database

+ SAPHANA

+ AnuresQL Database

+ Anure SQL Data Warehouse

DirecsQueryis different from the more traditional dts load methods forthe following reasons:

+ Youdonot load the data into Powe BI Desktop. Instead, you use the data rec
from the database server.

+ Because you are notloading acopy ofthe data nto Power Bl Desktop, you cannot
or fine, You need tbe able o connect to the source database or data
are house 1 use the data.

‘Adam Asia 2018 m
A Asp Pro Power Bestop

DRECTQUER AD comeCT UE

“The connection tothe source database or data warehouse—and the consequent
allabliyofthe data for analysis usually extremely fst ifnot instantaneous,

‘Te dat Is fetched specifically forthe requirements ofeach new visual hat
you erate,

follows that you do not ned to refresh the dat source ever the data is updated
{nthe source database or data warehouse. The data thats avaiable in Powe Bt
Desktop is always the latest version ofthe data.

‘ou haw allthe data that isin the source database or data warehouse available
Dataisretreshed every time you apply aslicerora filter

You cannot connect o any other datasource fyou are wing DirecQuery to connect
10 database or data warehouse (without loading the data into the in-memory
model). So the datasource ha to contain all the data that you need or your analy
Ifyou want o use Direc Query.

Microsoft SQL Server Data

Asa est example of DireeiQuery.at work, will use a Microsoft SQL Server database as the data source
‘The stepsareas follows:

1
2

Open anew Power BI Desktop application,

In the Power BI Desktop sibbon, click the smal langle at the bottom of the
{Ger Data burton and shen click SQL Server The SQL Server Database dialog
vi appear,

Enter the server name inthe Server text box. This willbe the name of your SQL.
Serverorone ofthe SQL Server resources used by your organization.

Enter the database name; you are using the sample do, willbe
CarsalesData,

Select the DiectQuery bution. The dilog wil lok ike Figure 1

SQL Server database

Figure 1. The Microsoft SQL Server Database dialog

12

CATE = RECTORY AND COIE UE

6 ClckOK. The Credendals dialog will appear Define the typeof redenials hat
‘you want use, asyou id in Chapter.

7. Clik Connect Power BI Deskop will connect to the serve and display the
Navigator dialog containing al the table and views inthe database int you
have permission ose on the server ou selected. In some cases you could se a
dialog saying that te data source does not support encrypon. you fel happy
‘with anunencrypted connection, hen click the OK button or ts dialog

8. Clik e check boxes forthe Clients, Colors, Countries, Invoices InvoiceLines,
and Stocktables The data forthe mos recently selected dat appears on the
Fight ofthe Navigator dialog

9 Click Load. Power BI Desktop wil display thelist of source tables for whlch ts
establhing connection. You can se this in Figure 42.

Create connections

Figure 2. Creating DirsciQuery connections

18. ThePower Bl Desktop window willopen and display the bes hat you selected
{nthe Fels list inthe Report window: You can eo this in Figure 4

us

GER 4» DIRECTOR AND COMES WE

Figure 43. Power Bl Desktop using Direct Connection

‘This isso similar to the proces tha you saw a the ist section ofthe previous chapter tha you can be
forgiven fr asking “So whats the difference" Well there area few diferences bu they ar so subtle as tobe
nearly invisible:

‘+ Tnerewas no dataload phase. When you clicked Load instep, the Power BL
Desktop window appeared almost instantaneously. This shy the dialog that
appeared brit in step 9 sys “Create Connections” instead "Load Data”

+ TheDataicon Is no longer avallale atthe top let othe Power BI Desktop window.

‘+ When you create Power BI Desktop visuals dey tke longer to populate with data
anddispay

+ Tobeslght technical what Power BI Desktop has done her sto query only the
‘metadata rom the source sytem. As metadata s nothing more than the description
ofthe data and the data structures (or "data about data) ih process extemely
‘api a very ite information is sent back rom the serverto Power BI Desktop. This
Ash establishing a DirctQuery connections fast, ina.

By deciding o use DirerQuey, you have adopted a diferent logic to how the daras sored. Instead
of copying al the selected source dats into Power BI Desktop, you are leaving the

Server In this example) and only importing te data that describes the d

necdingall the dat In Power B Desktop before you an do any analysis you can acess ony the data that
‘you need, as and when you need it Nevertheless, you ean use this data sa bass fo the data mashup and
modeling that are described in Chaptes 0-3 and Chapter 11.50 you can add calculations and tee dra
(ih afew imitations, alas) just as you cani you have oaded al the source ata into Power BI Desktop
before you begin your analysis.

na

CATE = RECTORY AND COIE UE

In deciding to use DieetQuery you hae, in essence, accepted trade-o Using DirectQuery implies that

‘Yous gain ime through nt loading the data nt the Power BI Desktop
in-memory model

‘© You query the datasource everytime that youcreat, modi, or fer a Power
[Desktop visual—but ony forthe subset ofthe data that required forthe specific
‘sual that youre creating or modiying.

‘+ When you refresh the dra, you wll no reload al the data into memory ais the case
when importing data, Power B Desktop wil only query the database forthe dara
‘hat actual required wo display the visuals that you have created.

+ Sleing data can take longer when using Direc Query because le Power I
Desktop visual are e-queried

+ Some ofthe data mashup techniques hat you wil discover in Chapters 5-3 cannot
bbe tsed, [ll highlight some ofthese when relevant he appropriate chapter

However fyou havea largely clean and caherent set source data such as data from a corporate
at warehouse, where the “hear iting” required © make the data reliable and useable has already been
red out—then DireciQuery can realy accelerate your data analysis. And you ae absolutely certain o be
Seeing the current data, swell.

Note You can equal wel use a T-SOL query ra SOL Server stored procedure o return data over a
DrectOvery conection, Simply expand ne Advanced Options section ofthe conection don step 5

(Gee Figure 4-1) andenter or copy the SOL en to exeete as describa in he previous chapter for leading data
rom SOL Serve

To give balanced picture (and despite ahearelt appreciation of he usefulness of DireiQuery) have
to.admitthat there area few drawbacks to ls connection type that you have tobe aware of

+ Youcannot (forthe momentat eat specify stored procedure in the Advanced
Options asthe datasource. Power BI Desktop will evenualyetum anerrr you
attempt this. potential workaround isto export that data fom the stored procedure
in SQL Server ad ten connect Power Bl Desktop tothe table tat contains the
results rom the stored procedure. Ifyou need this dat 1 be kept reasonably up
to date, youcanalwaysset up a SQL Agent job to rerun the stored procedure at
regular intervals. This technique might require assistance fom the DBAS at your
organization.

+ Youcanonly rerum a maximum of one milion records o Power BI Desktop when
using DiectQuery. Fortunately hs threshold sa row mit and not alimiton the
Source data So, Ifyou are aggregating abllon recor datasource but only returning
999,99 summary records then he query willwork.

+ Allthe source ables used in the Power BI Desktop fle must come roma single
‘source database you add second SQL Server connection Power BI Desktop will
‘ich 10 a elas” data load connection forthe existing DirectQuery connection.
In other words you can only have a single database connection you want use
DirecQuer.

us

GER 4» DIRECTOR AND COMES WE

‘+ Relationship ering (you wil see this in Chapter 10) vil only workin a single
direction.

‘+ Really complex DAX queries (you can Lea about DAX In Chapters 1-1) simply
il ot work when using DiectQuery. The only solution to force complex queres to
ak to te back loading te source data ito the in-memory ata model.

+ Setecing File > Options and Setings > Options > Direct Query, hen “Allow
"unrestricted measures in DirectQuery mode” wl prevent Power BI Desktop
applying bull limitations to DAX expressions However this can make some
queries extremely slow, asthe conversion from DAX to SQL isnot always offen.

‘+ Time tmetgence (explained in Chapter 13) snot valable with DirecQuery.

‘Toend on a poste now, DireeQuery does come wit the following advantages:

+ Reports erated using irectQuery can of couse, be published to the Power BL
Service that you wil meet in Chapter 23.

+ The 1GBlimiton the dataset az in Power BI Desktop does not apply Die Query
connections.

ote Power Bi Desktop has recent requested the data from the server that required fora
‘suaizaton then wi use the esting data that has been cached to aol placing undve stress onthe source
Server as wel as to enhance the user experience. Consequently, you need to refresh he data you want o be
‘sure that you are locking at the most up-to-date information and you suspect tha he data inthe source has
been updated recent.

SQL Server Analysis Services Dimensional Data

‘Another data source that can use irectQuery (which uses the variant hat Microsoft cal “Connect Lies.
‘the SQL Server Analysis Services dimensional data warehouse “lassie AS’ ass also known. Although
alive connection oa case SSAS dimensional data warehouse very similar o loading dat ram SSAS
Into Power Bl Desktop, thee ae a few differences that might make you prefer this method.

Note Lie Connection ta tabular data warehouse wil oly work you are using SOL Server 2012 PI
CUA o greater. in is case you have to have an Enterprise or Business Intelligence Eaton unes you ae using
SOL Server 2016, in ich case standard econ may be use.

‘Seting up live connection to lassie SSAS requires the following steps:

1. Inthe Power BI Desktop ribbon, ick Get Data > More > Database and select
SQL Server Analysis Services Database inthe Get Data dialog

2. Cllek Connect. The SQL Server Analysis Services Database dialog wil appear.

116

CATE = RECTORY AND COIE UE

3. Emterthe Analyas Services server name and the database (ot cube”) name, if
‘you know i you ar using he sample data from the Apress se for this book,
{he databases CarSalesOLAP; otherwise, you have to specf our own SSAS
database name. In any ease you will need tse the name of your own SAS

4. Selectthe Connect Live baron.

5. GK OK fist ist ime that you are connecting othe cube, then the
Access SQL Server Analysis Service dialog wil appear so that youcan define the
dental hat yo are using to connect the Analysis Services database,

6 _Acceptoralter the credendals and liek Connect. The Navigator dialog wil
appear.

7. Clckthecube tharyou want to connectt from the SSAS database. The log
vw Look something tke Figure 4.

Figure 4-4. Live Connection 0. tabular database

8. CckOK. The Power Bi Desktop window willopen and display the fact tables
and dimensions hat ou selected inthe Feld tint Report window. could
took something ie Figure 45.

ur

GER 4» DIRECTOR AND COMES WE

Figure 45. Power Bl Desktop using Live Connection

‘So,although generally similar tthe process for loading daa from dase SSAS into Power B Desktop
‘that you saw nthe previous chapter, this approach does, nonetheless, manifest some diflerences:

‘+ Tne Relationships con sn longer avallabl on the toplef ofthe Power BI Desktop
window.

+ Youwere not abletoseletthe tables to use rom the tabula data source. However,
the underying structure ofthe Analysis Services cube visible just ast would be

roms, Excel, This ineludes sl of (older hierarchy for data that present
Inthe SSAS cube

So, overall ave connection implies that the source data must be ready to use and corecty structured
foryou to base your Power Bl Desktop analytical reports ont You cannot make changes othe data ore
data structures or add any calculations in Power BI Desktop.

‘There ate a coupe of other points that You might ned to tk ino account you archesatngbenseen
alive connection and loading data into Power BI Desktop:

‘+ None ofthe data mashup posites that you vil earn in Chapters 6-8 are
asbl.

+ Youcannot use MDX 1 secte datathatyou war to us in Power BI Desktop. So
allveconnectontsan“allor nothing” option.

u

+ Certain features In your cube are either not supported fully oF not supported
‘with Live Connections from Power Desktop.

CATE = RECTORY AND COIE UE

Yet once again direct connection brings one crucial facto into the mix, and tati sheer speed. As
SSAS data warehouses ean be huge the fact that you are not loading massive amounts of data into Power

BI Desktopcan save an immense amountof ine. Not only that dat warehouse ha was oo large 0 load
{nto Power Bl Desktop could now become accesible over direct connection. Moreover, yout Power BI
Desktop visuals wll only return the exact data that they need from the SSAS datasource, as was the case or
(he SQL Server database inthe previous section. Al the hand worki carried out by the server, leaving Power
1 Deskop (and you) fee to concentrate on analysis and presentation,

Microsoft SQL Server Analysis Services Tabular Data Sources

Now see how tose a Microsoft SQL Server Anais Services tabular data warehouse asthe datasource
foralive connection. ASQL Server Analysis Services tabular database is another technology tha used for
sata warehousing is different rom the more traditional dimensional data warehouse in har is ete
stored inthe server memory and, consequently is usualy very much faster to use

“To establish alive connection to an SSAS tabular daa source:

1. Inthe Power BI Desktop ribbon click Get Data > More > Database and select
SQL Server Analysis Services Database inthe
(GerData dialog

2. Click Connect The SQLServer Analysis Services Database dialog wil appear.

Enter the Analysis Services server name and the tabular database name (don't
tendo call these cubes) you know it you are using the sample at from the
‘Apress ste or this book, the databases Carale Tabular there, you have

Ko specf your own tabular database name. In ny case you will need ose the
name of your own SSAS server.

4 Selective Connect Liv button The dialog wl ook ike Figure 6,

Figure. Connecting to an SAS (multidimensional) database

5. GK OK is the fie ime that you are connecting othe tabular data
source, hen the Access SQL Server Analysis Service dialog will appear so hat
youcan define the credentials that you are using to connect tothe Analysis.
Services database, where you will ave to acceptor alter the credential and click
Connect. The Navigator dialog wil appear.

us

GER 4» DIRECTOR AND COMES WE

1. Click the perspective (Model in hi example) hat youwich to connect to. The
log wil look something ike Figure 47

Figure 47, Slecting atributes and measure roman SSAS tabular source

7.. ClckOK. The Power BI Desknp window wil open and display the ables that you
‘selected the els in the Report window. You can se his in Figure.

Figure 48. A DictQuery connection to an SSAS tabular data warehouse

120

CATE = RECTORY AND COIE UE

‘This process was fly similar to the DireciQuery connection that you established inthe previous
section, There are, nonetheless, couple of further diferences:

‘© The Relationships icon sno longer valable on the top le ofthe Power BI Desktop
window.

+ Noneofthe dat mashup possibilities chat youwillearmin Chapters 6
available.

+ Yousere not abet select the tables to use from the tabular daa source,

‘These thre points actual imply the even deeper trade-off that you have accepted when you clicked

Connect Live in step 4 What you have agreed1o ls using the data source asthe complete and final model for
the data. You are nec, using Power Bl Desktop as dh frontend forthe data “as”
‘This tade-of however is bursting with postive for you, the data analyst You can now

See alle data In he data warehouse (ratleastthe data tat you are authorized to
se) without a comple process where you have o Select dozens of tables (and risk
‘overookinga few vital sources of data).

‘+ Access the data model as has been designed by an expert with ll the relationships
between th tables defined atsource

+ Gerthe latest ata—whatyow sein Power Bl Desktop th cure aran the
tabular data warehouse. You only need wo refresh Power BI Desktop ifthe datals
fefrshed at source,

‘+ Use predefined hierarchies and calculations from the tabular data warchouse,

+ Aces the data at lighning sped, because the source datas stored nthe host server
‘memory, and not on disk (as ste case with lasse relacional database).

‘This as point isthe one that wally needs emphasizing Notonly are you gaining time rough
otloading copy ofthe data into the Power BI Desktop in-memory data model ou are accesingin-
‘memory data on the server sell avoiding the need for sow searches fr data on disk The overall est
{sunbellvabl fastaccesso huge amounts ofclean,stuctued, and aggregated data Allin all the
‘combination of SQL Server tabular data warehouse and Power Bl Desktop Is designed to make data
‘analysis considerabiy ate and easier.

‘tis worth noting that her is no real Power BI Desktop Query Editor access for alive connection to
an Analysis Services tabular data warehouse Ifyouclck the Edit Queres button al you wi ses the
connection dilo for the Anais Services database

Note When you setup a ve conection o tabular datasource, you camote the data thats loaded
from an SSAS cube by expanding the MOX or DAX query (option) ite inthe SOL Server Analysis Services
database log and entering a DAX query.

DirectQuery with Non-Microsoft Databases

So farin this chapter we have focused on using Microsoft data sourcesto establish DirectQuery
‘nnnectons o the source data Fortunately, Microsoft has extended tis technology to several other
Sources of corporat data

1a

GER 4» DIRECTOR AND COMES WE

qual fortunate isthe fact that connecting to (say) an Oral database or an SAP HANA data
‘warehouse using DiretQuer is every bit as simple as loading the data into Power BI Desktop fom these
(orindeed other database and data warehouse sources S willno waste pages her in re-expaiing he
technique. Ifthe source la database then you selec DirectQuen the source la dat warehouse then
you can select Live Connection. A DieciQuery wil alow you to specify SQL statement select data anda
Live Connection wil display al the objets inthe data warehouse. It really is that simple.

DirectQuery and In-Memory Tables

A recent addion tie Microsoft SQL Server database hasbeen in-memory tables. These are perceived by
tool ike Power B Desktop as standard tables yet ny xistin the server's memory (as opposed to string
the data on disk) This makes accessing data rom in-memory tables extremely at When you dd to this
the at that these ables ean, in many cases als use the compression technology that SSAS tabular data
‘warehouses use then you have prety neal thebestof both words asar as analytes is concerned:

‘+ Data stored in lasse elational structures hat updated insanly

‘+ Optimized structures for analytes (datas stored by column, rather than by row, and
Is both compressed and in-memory)

Her is notte place o expose all tha hl technology can bring to the table. However as sa potential
_game-changer ls wort showing you how you can use the atest versions ofSQL Server (has, 2016 and
Up) as aire source of analytical data. As this essentallyarerun ofthe ina section of this chapter, vel
not show her, again the same screenshots ofthe process.

1. Openanew Power BI Desktop application
2 Inthe Power BI Desktop ribbon, click the small angle atthe bottom af he

Get Data button and then dick SQL Server The SQLServer Database dog wil
appear.

3. Entrtheservername In he Server text box. This willbe he name ofyour SQL
Server or ane ofthe SQL Server resources used by your organizaron

4. Entero database name; fyou are using the sample data, wi be
CaralesMemoryBased,

5. Selectihe DiwerQuerybutton,

6 ClickOK. The Access a SQL Server Database dialog wll appear Define the ype
‘ofcredentals that you want to use as you did in dhe previous chapter

7. Glick Connect. Power BI Desktop will connect the server and display the
Navigator dialog containing al the tables and views inthe database that you have
permision to see on the server ou selected

8. Clickthe checkboxes forthe Clients, Colors, Countries, Invoices, InolceLines,
and Stock bles. The data forthe most recently selected data appears on he
‘ight ofthe Navigator dialog.

9. ClickLoad. The Power Bt Desktop window wilopen and display the tables that
ou selected in the Fels iin the Report window.

‘es, the process is identical toa “normal” DirecQuery connection Youll onl return data when
eating or modifying visuals. Yet here to, the data stored in memory onthe server and al the heavy
lifting carried out by tv server However (and unlike when using a Live Connection ta tabular data
warehouse), you can extend the data model hide or ad further columns, and create complex calculations.

122

CATE = RECTORY AND COIE UE

DirectQuery and Refreshing the Data

‘ou saw howto refresh data from databases and data warehouses inthe previous chapter Ifyou are using
DireciQuer reftehing the data wl kel only be necessary the source data has changed; This is because
‘everytime thatyou create or modify visual or when you Her reports pages or sual (or fyou apply
slicers), te data fo any visual ect by the change is rfrshed,

‘Consequenty, youonly have click the Refresh button you know thatthe source data has
‘been updated. could be, or instance thatthe underying database or data warehouse isreprocessed
‘vernight—or even on an hourly basis. In cases ik these, you should lick the Refresh button once you
‘now that he data has been updated on the server so that you canbe absolutely sure that you ae using the
latest avalable data in your eports

Inthe ease ofa Direct Query connection 10 database, you could wish only to refresh selected tables.
In hs case, a ou have to dois to right-click the table name nthe Fields ist and select Refresh or each
table that you wantto update

Conclusion

‘This short chapter extended your ables o use databases and data warehouses as he datasource for your
Power amas. Yous that you can choose not to load data no the Power BI Desktop in-memory data
‘model butean instead, connec diet t the source data repository. Moreover, you saw thatthe approach
tosetingup a direct connection i virtual denial to the waythatyou setup a standard data load imo
Power Bl Desktop.

DirectQuer allows you t avoid a potentially massive data transe This can save you large amount
‘ofvime and also guarantees that you ae looking at the latest dat, So while ican take a ie longer to
‘design and filter your visuals, you are only using the precise data that you need on each and every ocasion.
‘Sometimes thiscanbe the only feasible way to analyze data rom terabyte-sized data warehouses

‘When combined with n-memory data storage on the server in in-memory tables or tabular data
warehouses), ths technique can make Power BI Desktop Ino a near pertec ant endfor the analysis of
ge and complex corporate datasources.

"However corporate database and data warehouses are not the only sources oflange scale data hat
‘exis More and more data snow sored in he cloud. Accessing cloud and web-based data wll bee
‘subject ofthe nextchapter.

123

CHAPTER 5

Loading Data from the Web and
the Cloud

Inthis chapter we wil ake look ta suse ofthe fast-growing and wide-ranging set of na sources
sable over the Internet that you can use asa source of analytical data for Power Bl Desktop. While the
‘ata sources har you wilsee in the following pages may be extremely diverse, hey al have one thing ia
«common: they re stored ouside te enterprise and are valable using an Internet connection

"The dat sources that are valable are avalabe rom a multe of suppliers Looking aallthe
_wallblesoures would take up an entire book, o Lil show you how to access several ofthe mainstream
‘Services that are currently available. Once you have learned how o acces a few of them, you should beable
o extend the base techniques to acces jus about any the web and cloud services th ancurreny be
‘sed by Power BI Desktop.

Power BI Desktopis now firmly entrenched a a fundamental par ofthe Microsoft universe.

[As PowerB.com (the cloud service that you can use wo store and share dashboards) spat of Microsoft
‘Azure is perhaps inevitable that the Power BI Desktop developers have gone ou of ther way to ensure th
Power Bt has become he analytical tool ofchoce fr solutions that are hosted in Azure-the Microsoft Cloud.
Forthis reason, wil explain quit a few of he core services that host data in MS Azure.

‘Neal all ofthe data connections outlined inthis chapter require acesso specifi online source.
Most ofihese sources are industalstrength—and nos fee However, your enterprise Is not subscriber
to these services, and you wish, nevertheless, 10 experiment wih them could be worth aking a look atthe
fe rial offers available rom many (for all) ofthe service providers whose offerings are outlined inthis
chapter

Web and Cloud Services

‘Before delving into the deta of some ofthe web and cloud services that are avallabl, take an ina
High evel Lock what these really are These data sources include (among many others

+ Web pages
‘Online services, such as Google Analytics Salesforce, or MS Dynamics 365

+ Microsoft Azure, which covers hosing les in Azure Blob services, storing data In.
an Azure SQL Database or storing data in an Azure SQL Data Warehouse (or even
reading big dai Azure HD Insight)

+ Opa, the gener method of accessing data on he Internet

‘Adam Asia 2018 125
A Asp Pro Power Bestop

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

Web Pages

Ityou need 0 collec some data that you can sea a table ina web browser you can use Power Bl Desktop
to connect the URL forthe page in question and hen load alle data from any table onthe page.

Online Services

(Online series acota phrase used to describe data that you can aces sng the Internet. Most of
the online services valable to Power BI Desktop are what are called “platforms!” These are (often huge)
software and data resources that either are only vallable online or wee once housed in corporate systems
ut are now available as servers on the Internet. There are curren dozens of online services that are
valable to connectto using Power BI Desktop. Indeed, the number faallabl services ls growingara
starting pace, Some ofthe more frequent used include those listed in Table 5-1.

Tables-1. Some Online Services Available o Power BI Desktop

Source ‘Comments

Salesforce Objects Leu you acess daran Salesforce,

Salesforce Reports Lets you acessthe prestructued data that under built-in Salesforce
opor

Google analytes Lets you acessthe data managed by Google to track website wa.

Facebook Accesses Facebook data

SharePoint Online ‘Connects tothe Cloud version of Microsoft SharePoint

Microsoft Exchange Online Connects othe Cloud version of Microsoft Exchange

Dynamics 365 Online Connects tothe Cloud version of Microsoft Dynamics 365—the MS CRM
andERP solution.

oats Although OData so technical an online platform, I iscertiniyan

‘online source of data, This is sndandized method forconmecting to
ire data strucuresusing a URL asa starting point.

ote As the number of avale nine services is increasing at an ever-increasing rate, you wl probably
ind many more than those tat have listed hereby the time that this books published. Moreover, there are
‘current number of ane services that are available in beta. This means that you can test them, ut they are
not yet finalized.

Microsoft Azure

Auris the Microsoft Cloud. The Azure data sources that Power Bl Desktop can currently connect, and
can preview and loud data from, are given in Table 5 2

126

Tables, Azure Sources

CUTER LOND DATA FON THE WER ANDTHE 3.000

Source

Comments

‘Microsoft Azure SQL Database

Microsoft Azure SQL data warehouse

Microsoft Azure Markeplace

Microsoft Azure HDInsight

Microsoft Azure Blo Storage
Microsoft Azure Table Storage
Azure HDInsight Spark

Microsoft Azure DocumentDB (now
called CosmosDB)

Microsoft Azure Data Lake Store

Lei you connectio a Microsoft SQL Server cloud: based database
nd import records from allthe data tables and viewsthat you are
authorized w acess.

Lets you connectto Microsoft cloud based, elastic enterprise
daa warehouse,

‘Lets you load data that you are authorized 10 access on the
Microsoft Azur Marketplace. requires a Mleroso Azure
‘Marketplace subseripion,

Reads cloud-based Hadoopfesin the Microsof Azure

Reads from acloud-based unstructured daa stor.
Reads from Microsoft Azure able.

Lets you connectto Micosol parallel: processing framework ln
Microsoft Cloud.

Let you conneetto Microsolés NOSQL database.

Let you connectto Microsoft raw dat cloud sorge

Obviously, more Azure connection optonsare being added Power BI Desktop by Microsoftasthe

Azure oferingis extended.

Web Pages

sais and extremely simple example Jets grab some daa from a web page Since wantto concentrate
‘onthe method rather than the da, vil use a web page tat has nothing to do withthe sample data in he
"book, wil not be using his other than as a simple inreduction to the process loading daa rom web

ages using Power BI Deskop.

“Assuming hat you have launched Power BI Desktop and closed the splash screen.
1. Clickthe small triangle atthe bottom ofthe Get Data butonin the Home ribbon,
2 Select Web rom the menu that appears, as shown in Figures

127

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

ES

FigureS-1. The Get Data menu

3. Enter the following URL (tis a Mierosofthelp page or Power Bl Desktop that
«contains few tables of data)shttp://oF ice snteronoPtscon/e-gb/excel
help/quide-to-the-pover-query-rIbbon-HA103993920.35px am, ofcours,
‘hoping that itis sl valable when you read this book. Ofcourse you havea
‘URL that you wantto ry ut then fed fee! The aig wil ook something ike
Figure 52.

igure5:2. The From Web dialog

‘4 Click OK. The Navigator dialog will appear. Aer fe seconds, during which
Power I Desktop sconectingtothe web page the sof aalable tables of
<atain the web page willbe displayed.

128

CUTER LOND DATA FON THE WER ANDTHE 3.000

5. Glckone ofthe table names onthe eft of the Navigator dialog. The contents of
‘the abe wil appear onthe right ofthe Navigator dialogo show you whatthe
dt in the chosen table looks Ik, a shown in Figure 3.

Navigator

igure5 3. The Navigator dialog previewing the contents ofa able on a web page

6. Selectthe checkbox in the Navigator dialog (chown to thee of

Table din Figure 5

7. Click Load at the bottom ofthe window (or double-click the abe name).
{lckthe Data icon on the top let ofthe Power BI Desktop window to display the

table of dat. should looklike Figure 5-4

120

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

Figure5-4. The Power BI Desktop Query window

“ip Another way of accessing web pages so Get Data > Othe. You can then select Web nthe lit
‘on the ight ofthe Gat Data dao.

‘This simple example showed how you can load dat from a supported datasource and load tino
Power BI Desktop Query

Advanced Web Options

In step sof the previous example, you could ave selected the Advanced buton Had yo done this the
From Web dialog would have expanded tallow you to build complex URLs by adding URL pars You can
See an example of tls In Figure >=

130

CUTER LOND DATA FON THE WER ANDTHE 3.000

From

‘gure. The Advanced options inthe From Web dialog

‘licking he Add Part bution allows you to define multiple URL pars.
necessary, you can also speciy HTTP request header parameters hat wl be used when subi

«the URL These could be required by certain web pages A discussion of hese I outside the scope ofthis
book.

‘Table View or Web View

‘Looking atthe tables hata web page contas notalvays the most natural way finding the righ data.
‘This is because you ae looking the data ables out of context this mean that you cannot se where
they are on the webpage. Atral,ihe Web isa very visual medium.

‘Tohelpyou And the correct data table on a web page, the Query Editor ets you switch between wo.
ews ofthe web source:
+ Web view (which yousawin Figure 5-3)

+ Table view which you can seein Figure 5-6)

in

CAMPERS = LOAD DNA FROMTHE WEB NO THE CLOUD

Guide to the Power Query
Ribbon

Figure 5-6, Web View In the Navigator dialog

‘You alterateberween these ways of visualizing the web page by licking the Table View and Web View
buttons that are toto centerofthe Navigator log, The same web page hat you saw previously looks
lke Figure 5-6 when you switch to Web View.

Salesforce
‘One ofthe pioneers inthe online services space-and now, disputa, ne ofthe leader aleros
Sits perhaps inevitable that Power Desktop wallow you to connect Salesforce andloadany data
tatyou have permsion ove sing Your Sale acount

Tnded, leere sucha wide-ranging and complete sevice that you have wo possible methods af
accessing your da:

+ Objet.

+ Reports

te Salesforce object ar the undesying data tables that contain the information thatyou vant
to css Salesforce reports arte data tha hs been collated rom the data tables Into a more accesible
form ua.

‘Tip you do not have a corporate Salsorce account but want, nevertheless, to see how to use Power Bt
Desktop to connect to Salesore data, you can aways setup ree 30-day il acount. The URL fortis is
195: as. sales force. con/forn/signup/freetrial-sales. sp.

132

‘OUPTERS = LOXONG DATA FROM THE ED ANDTHE CLOUD

Loading Data from Salesforce Objects

Assuming then, that you have a valid Salesforce account, here how you can load data from Salesforce
‘objets into Power BI Desktop:

1. Inthe Power Bt Desktop Home ribbon, clk the Get Data button.

2 Click Online Services onthe ef and then select Salesforce Objects onthe right.
“The Get Data dialog wil look ke Figure 57

Get Data

Contin eres
m D Seen nar
ue EZ Move Orne
Panta Dames)
owe A On mn ee)
¡nine ences I Conner ene tes
omer EI en as

© neo Emi)

tc ce

2 Gone

CE

O cons ou

On oa

Cr

CE

€ nome te) Y

igureS:7. The Get Data dialog for online secs

3. Glek Connect The Salesforce Objects dalog will appear should looklike the
one shown in Figure 8.

CAMPERS = LOAD DNA FROMTHE WEB NO THE CLOUD

FigureS:8. The Salegorce Objects dialog

4. Selectihe Production button and dick OK. The Acces Salesforce login dialog
‘ill appear should look ike the one shown in Figure 50.

Figure:9. The Access Salesforce login dag.

5. Unless youare already signed in, lick Sign in. The Salesforce sign dialog wil,
appear.

{6 EnteryourSalesorc login and password. The dialog should look something ke
‘theane shown in Figure 1.

134

‘OUPTERS = LOXONG DATA FROM THE ED ANDTHE CLOUD

someon

Figure5:10, The Salesforce sign dialog.

th isthe rst time that you are connecting 0 Salesforce fom Power BL
Desktop orifyouhave requested that Salesforce request confirmation each time
‘hat you login). you wl be asked to verify your ident. Te Salesforce Verily
Your Identity dialog will appear as shown in Figure 5.

CAMPERS = LOAD DNA FROMTHE WEB NO THE CLOUD

Verify Your Identity

Figure 5-11, The Salesforce Verify Your Identity dialog

8 Clik eri. Salesforce wil senda verification code othe e-mall account hat
you are usingtolog into Salesforce.

9. Enterthe code inthe Verification Code fleldandclick OK, You wll se the Allow
‘Access dialog a in Figure 5 12.

‘OUPTERS = LOXONG DATA FROM THE ED ANDTHE CLOUD

Figure 5.12. The Salesfore Allow Acces dialog

10. ClickAllow. You will tur wo the Acces Salesforce dialog only now you are
loggedin.Youcan se thin Figure 5-13,

igure -12. The Aces Salesforce dialog

#1. lickConnect. The Navigator will appear, showing the Salesforce objets that you,
have permision to access You can se some ofthe objects that ae avaiable f
youre usinga tral aecouncin Figure 5-1

17

CAMPERS = LOAD DNA FROMTHE WEB NO THE CLOUD

Figure 5-14. The Navigator dag showing Salesforce Objects

12. Select the objets whose datayou wish to load into Power BI Desktop and lick
Load, The data wil be loaded ino Power BI Desktop ready for You io create
dashboards and reports based on you Salesforce dat,

To avoid having to confrm your ent to Salesforce everytime that you create a new sue of Power
BI Desktop reports using Salesforce data, you can check “Remember me" inthe Salesforce sign-in log and
Dont ask again” inthe Salesforce Very Your dent dao.

Salesforce objects contain avast amount of data. However, you ar, m effect, accesing a database
structure. This means that you have to have some understanding o how the underyin data stored,
Should ou visto learn about the way tht Salesforce data istrucured, then I sugges that you star with
the Salesforce documentation current avaliable at ittps://tzailhead.salesforee,con/en/eadiles/
data sodeLing/units/objects_ intro.

Salesforce Reports
Ayo ind you ae simply submerged the mount of dra thats are in Saler ocn,

instead, go direcyto the data that underes standard Salesforce report This wl avoid your having earn
aboutthe underying daa structures. The downsides dat you cannot easily extend these datasets

138

‘OUPTERS = LOXONG DATA FROM THE ED ANDTHE CLOUD

‘To acces Salesforce report data, simply follow the steps outlined inthe previous section However,
Inseadofchoosing Salesforce Objects instep 2 select Salesforce Reports instead. The Navigator dialog wil
{inthis case look something ike the one show in igure 15.

Figure 5-15. The Navigator dialog showing he dat for Salesforce Reports

Fram her yu can select and load she reports data from Salesforce that ou want1o use to create your
‘vn dashboards,

Microsoft Dynamics 365

Another online service that contains much valuable enterprise datas Microsoft Dynamics 368. As you
‘would probably expect, Power Bl Desktop can connect easy to Microsoft online sources such a Dynamic.
Here howto do tis:

Tip ou do not have a corporate Microsoft Dynamics 365 online account but want, nevertheless, o ee
howto use Power Bl Desp to comet to Microsoft Dynamics 365 data, ou can always set upa fee 30-day
il account. The URL fr hiss https: //trials. dynanics.com/Cus torent rgagenent/ChangeSignup/.
Indeed is example is rom using a fee 20-day tal account (at wl kel ave expired Kong before tis book
isin pring,

139

CAMPERS = LOAD DNA FROMTHE WEB NO THE CLOUD

1. Inthe Power BI Desktop Home ribbon, clickthe Get Data burton.

2 ClickOnlin Services onthe ef and then select Dynamics 365 (online)
‘onthe right The Get Data dialog look ke Figure 5-16

Get Data
F Dr a
= ppal
om (Dome
= pepe
us Démrminnet

= eee et

meo antes
as

Cu

O entend ans
Ce

coe

Figure 5-16. The Get Date dialog for Dynamics 365

3. Click Comme. The Dynamics 365 online) alg wil appear

4. Entre URL tht you use 1 connect to Dynamics 365 and add /api/data/v8.2
{atleast this wasthe ase a this book went o pres) ou look ike the one
shown in Figure 5.17. Note however, that his URL wi vary depending on where
you aren the world.

Dynamics 365 (online)

Figure 5-17. The names 365 (online) dialog
10

‘OUPTERS = LOXONG DATA FROM THE ED ANDTHE CLOUD

5. CickOX. The OData Feed dialog wil appear.

6. Select Organizational Account asthe security acess method. The OData Feed
log vl ook like Figure 5-1,

Figure5-18. The OData Feed dialog

7. Gc ign nt signin t your Dynamies 365 account and follow the Microsoft

sign-in process, Once completed, the OData Feed dog wil ook something ike
‘he one Figure 5.1

igureS-19. The OData Fes dialog after sign-in

8. Click Connect The Navigator dialog wll appear showing athe Dynamics
objects that you have permissions to connect to. Youcan se an example of his
in Figure 5-20

1

CAMPERS = LOAD DNA FROMTHE WEB NO THE CLOUD

Navigator

ote instep 6 you saw tat an MS Dynamics 36 connections realy an OData connection Oata is
‘explained in more eta na subsequent section of tis chap.

‘There are a huge numberof Dynamics 365 tables—and his number will vary depending on the
subscription that your organization has taken out. However, you are, In real, accessing a database
Structure, This means that you have t have some understanding of how the underying data stored.
Should you visto learn about Dynamies 365 tables, then I sugges that you start with the Micros online
helpathttps://docs.nicrosoft.con/en-us/dynanics365/oni fied-operations/dev-itpro/data~
entities/data-entivies,

Google Analytics

Assuming that you have aval Google Analyte account up you can use Power Bl Desi to connect
tothe Google Ana data tht you have persons aces. For hs example to work youll eed a
valid and fnconing Google Ana account,

Note To sin up fora Google Ana account that you can set test Power Bl Desktop, got https: //
wen google.com analytics,

12

CUTER LOND DATA FON THE WER ANDTHE 3.000

1. Inthe Power B Desktop Home ribbon, click the Get Data button.

2. Click Online Services onthe ef and then select Google Analytics om height.
The Connecting ta third par service dialog wil lok ike Figure 521,

Connecting to a third-party service

ca bo Re ane atte oc scree
CE

Figure 21, The third party service connector let

3. Giek Continue: The Google Account dialog will appear. This curently looks ke
the mein Figure 5-22.

Figure5:22. The Google Analytics connection dialog

4. Glcksign in. The Google Choose an Account alg willbe displayed. This
‘current looks ike the image in Figure 52"

us

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

Google

Choose an account

co

© trama

Figure 8.23. The Google Analyte login dialog

ua

5. Clicktheexsing accountto use for Google Anales. The Google Analytics
Permisions dialog wll appear. This currently looks ke the one in Figur 21

CUTER LOND DATA FON THE WER ANDTHE 3.000

Google

Hi Adam
CES

© vere o

Figures 24, The Google Anais permisions dialog

6 Glick Allow, You wil tur tothe Google Account dialog, bu logged in this time.
You can se thisinFigue 5 2.

can

igure5:25. The Google Analytics dialog en signed in

{ck Connect The Navigator dialog wil appear displaying the d
you can connset in Google Analytics.

us

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

OData Feeds

Data isa short way of referring tothe Open Data Protocol. This protocol loss web lens to publish and
esditresources, identified as URLS The data that you connect t using OData canbe in atabular formar or
Indeed in diferent structures.

‘ODatais something ofa generic method of connecting to web-based dats. Consequently each OData
source could ir rom others tat you may have used previously. Indeed, you have already seen OData
‘when connecting Dynamics 365.

"However there are a mulitudo of OData sources hat are availabe, Some are publi, some are only
accessible you ave appropriate permissions However, the access method wl always be broadly similar
Here, then san example of how to connectto an OData sample source that Microsoft has made frech,
sable:

1. Inthe Power BI Desktop Home ribban, click he smal ranglat the boom of
the GetDatabuton,

2. Select ODat Fred from the ment. The OData Fed dslogwllappeae

3. Entero URL that you are usingto connec tothe ODatasoure. In ths example
[illus a Microsoft sample OData feed that you can find at 9: //50rvicos
‘data oxg/nor tnd /noethwind. sve, The dialog should looklike Figure 5.20.

(data feed

Figure 5:26. The OData Feed dialog

4. ClickOK. The Navigator dialog willbe displayed and wil show the data avaiable
"sing specified URL. An example given in Figure 527.

16

CUTER LOND DATA FON THE WER ANDTHE 3.000

Figure 527, The Navigator dialog singan ODatasource

OData Options

‘The OData Feed dialog (rater let From Web lag) als contains an Advanced button. Selecting his
vil expand the dialog tallow you add one or more URL pars tothe URL Yu can se thin Figur 3-2

OData feed

igure5.28. The OData Feed dialog Advanced options

17

CAMPERS = LOAD DNA FROMTHE WEB NO THE CLOUD

Note URL pats canbe parameterized inthe Power B Desktop Query Editor. wil explain parameterization
in Chapter,

Azure SQL Database

SQL Server does nat only existas an on-premises database Is also aallable as a "Platform asa Service”
(also known as PaaS). Simply put hsles you apply a pay-as-you-go modelo your database requirements
‘where you can fre up a database server inthe cloud ina few minutes and then sale ost your
requirements, rather than buying hardware and software and having to maintain them.

‘Connectingo Microsofts PaaS offering, called Azure SQL Database, truly simple. you have the
decals ofa corporate Azure SQL Database you can use hito connect, you donot, and nonetheless
‘wantto experiment with connecting Powe BI Desktop to Azure SQL Database, you can away request a free
tra account from Microsoft and set up an Azure SQL Database database ina few minutes Ithisis the path
thatyou ar taking then you can ind instructions on how to do this including ending the sample data that
‘youll connect late inthis section) atthe following URL: i tps://docs. microsoft .con/en-gb/azure/
Sql-database/sql-database-get-started-porta.

‘ip Wen you ae creating the Azure SOL database, be sure to define the source tobe Sample This will
ensure thatthe MS sample datas loaded it your test database.

Note it youare setting up an Azure SOL database, make sue that you include frewal ues o alow
connection rom the computer where you ar running Power Bl Desktop tothe zur SOL database.

‘To connec from Power BI Desktop to an Azure SQL database:
1. OpenanewPower BI Desktop application,

2. Inthe Power BI Desktop ribbon, lick the small angle atthe bottom ofthe Get
Data button and then cick More

3. Click Azur ne ist on the lef and then Azure SQL Database on the right. The
{Get Data dialog wl ook like the one in Figure

18

CUTER LOND DATA FON THE WER ANDTHE 3.000

Get Data
» Pen

= pr
Ds Ananas
ase Preece

= sere HE cove tame

= ns

Figure 529, Azure data sources in he Get Data diag

4. Clek Connect The SQLServer Database dialog will appear (ater all an Azure
SQL databaseisa SQL Server database—but inthe cloud).

5. Enerthe Azure SQL Database server name that you obtained om the Microsoft
‘Azure management poral (or that was given you by a corporate DBA). The
SQLServer Database lag wl ook ike the one shown in Figure 5-30.

SQL Server database

FigureS-30. The SQL Server Database dialog for an Azure SQL database connection

us

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

6 ClickOK. The credensls alo wll appear

7.. Click Database onthe leftand enter valid username and password. The
‘credentials dialog vil ook ike the one shown in Figure >.

Figure 531. The SQL Server redentasdalogforan Azure SQL database connection

8. Click Connect. The Navigator dialog will appear showing the database) that
ou have permission to access the Azure SQLServer database This dialog will
Tooke the one shown in Figure 5 you are using the test data supplied by
Microsoft fora default Azure SQL database

150

CUTER LOND DATA FON THE WER ANDTHE 3.000

Figure 5:32. The Navigator dato for an Azure SQL database connection showing sample data

you followed the steps to connect 1 an on-premises SQLServer database in Chapter , hen you are
probably feeling thatthe approach used here ls virally identical, Fortunately, Be Power BI development
‘eam has worked hard to make the two processes as similar as posible. This extends to

+ Ensuring thatthe DataSource setings are stored by Power BI Desktop and can be
"updated just a you can fr anon- premises database connection

+ Allowing youeline to use Direct Query orto impor data nt the Power BI
in-memory data model

+ Using the same Advanced options (wring your own SELECT queres or sing stored
proctlres) thatyou can une wih an on-premises SQL Server

Azure SQL Data Warehouse

Azure has many available platforms to store data, One that particulary well adapted to Power B Desktop
Asthe Azure SQL Data Warehouse, This a tabular data warehouse has Hosted inthe loud.

‘Once again Iwill presume that ness You havea corporate Azure SQL Data Warehouse at hand, you
il be using lal Azure account and that you have provisioned an Azure SQL Data Warehouse using the
Sample data that Microsoft provides. Setting up atest data warehouse ls very similar to preparing a database,
as described atthe URL a he tart ofthe previous section. Here, 10, you need firewall ules tobe setup
‘correctly (although his may not be sity necessary you have previously setup firewall rules fo sy,
‘Azure SQL Database).

as

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

Note Wen ou ae creating the Azure SOL Data Warehouse, be sue to define the source to be Sample
‘This wi ensure tat the MS sample data ls loaded into your test at warehouse and ou wi not be querying
an empty data warehnuse.

1. OpenanewPower BI Desktop application,

2 Inthe Power BI Desktop ribbon, click the small angl tthe bottom ofthe Get
Data buon and then ik More

3. Click Agur in thelist on the lf and then Azure SQL Data Warehouse om the
righ

4. Click Connect. The SQL Server Database dialog will appear

5. Enterthe Azure SQL Data Warehouse server name that you obtained rom the
Microsoft Azure management poral (arıhat was vento you by a corporate DBA).
The SQL Server Database dai wil look ike the one shown in Figure»

sau s

m.
Figure 8-33. The SQL Server Database dialog fo an Azure SQL Data Warehouse conection

6 Selectihe Import button and then cli OK. The credential dialog wil appear.

Click Database onthe left and entera valid username and password
{Click Connect. The Navigator dialog will appear showing the database) that
you have permission to accssin the Azure SQLServer database This dialog will

Tooke the one shown im Figure 5-1 fou are using he test data supplied by
Mie

152

CUTER LOND DATA FON THE WER ANDTHE 3.000

Figure 534. The Navigator dialog for an Azure SQL Data Warehouse connection showing sampledata

8. Setrcthe tables that you ned and lick Load or Edito returnto Power BL
Desktop and begin adding suas your report

Note Do mot be phased by the fact that he ne forthe il where you spect the server and database
says “SOL Sever Database." This wil connect you tothe Azure Data Warehouse correct.

was the ase for on on premises connection, you can choose Live Connection (ven ifthe dialog
«list Direc Query) and can expand the Advanced options eld entra specif DAX query youre
loading dat.

Connecting to SQL Server on an Azure Virtual Machine

More and more databases re now hosted outside a corporat environment cloud services providers.
‘Wi a provider such as Amazon (with RDS for SQLServer) or Microsoft (who offers ral machines or
\VMs—forSQL Server in Azur), you can now se your databases outside the enterprise and acces them
rom virualyanyuhere in he world

Sa to extend the panoply of datasources availble o Power Bl Desktop, we wll now see, bey, how
o comnectto SQL Server on an Azure Virtual Machine. Admitedly,connectingto SQL Serveronan Azure
Viral Machine isriary the same as connecting to SQLServer ina corporat environment. However is
‘worth a short detour explain, bre, howto return daa to Power B Desktop from a SQL Serve instance
Inthecoud,

153

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

Once agai, you do not have a SQL Server instance tha hosted on an Azure Vital Machine in
our corporate environment, then you can always esthi proces sing an Azure tal account. cannot,
however explain here howto setup a SQL Server instance on à VM, as this is outside the scope af this book.
‘There are however many esouresavallabl hat can explain how dot should ou need ther,

‘Note you ar creating your own vital machin, then you cancomect to is in SOL Server
Management tudo using the same connection sting that you use instep 5 below and crate the sample
‘database manual, You can then oad the sample data as described in Append 8. wil ot expan is process
in details lam presuming abasic level of fairy wih Windows Serer and SOL Sever it you are Sting up
your om.

connect © SQLServer on a Virtual Machine:

4. Open anew Power Bl Desktop application,

2. Inthe Power BI Desktop ribbon, click the small angle tthe bottom ofthe Get
Data buon and then cick SQL Server The SQLServer Database dialog wll
appear.

3 Entre fl string that describes ho server inthe Server text bo. ther this wil
be given o you by à corporate DBA or, you are using your own Azure account,
you can ind tin the Azure Management Portal.

4. Enterthe database name; you haw loaded the sample data that accompanies
{this book int a SQLServer instance na VM, i willbe CaralesData The log
willook ke Figure 5 +

Figure 5-35. The Mlerosof SQL Server Database dialog

5. ClickOK. The Access a SQL Server Database dialog wll appear Selec Database
‘asthe security mode andentr the username and password, asshowm in
Figure 56. youar using yourown Azure account, these canbe the username
and password that you specified when setting up the viral machine.

154

CUTER LOND DATA FON THE WER ANDTHE 3.000

igure5.36. The SQL Server Database dialog when connecting a virtual machine

6 you see the encryption suppor dilo lick OK. The Navigator dialog all
appear as shown in Figure 57, tng ll ables that you have permissions to
econ the SQL Server hosted by the vial machine.

igureS:37. The Navigator dialog when connecting 1 a virtual machine

155

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

you can see, the process is virtually identical tothe one that you followed to connect to SQL Severin
Chapter 2. have, nonetheless afew points that need to bring to your attention:

+ You usen Azure VM multar name asthe server name,

+ Aswasthe case when connecting oan on-premises SQL Serverinsance, you can
select database required.

+ Youcan use the server's IP address asthe database name ifthe VM has specified a
Public address.

‘+ security sa bigand separate question. Ina corporate environment, you might
‘beable to use Windows security to connect You will almost certainly have to use
database security for aos VM.

+ sis always the case in Azur, Arewalls must be er up correct.

+ DirerQuery snot avallable when connecting to SQLServeron an Azure Vital
Machine,

Azure Blob Storage

‘The inal Azure data source that I want to introduce yout inthis chapters Azure Bob Storage. To al intents
and purposes you can consider this as fr as Power BI Desktop iseoncemed, asa fle share inthe oud. So
ou needto access data thats stored.as les you can connect o them via Azur Blob Storage.
‘Once again you willneed ether corporate acces to Azure Bio Storage oran Azure il account In
either ease You needto copy the two sample les that rein the folder C:\PowerBiDeskzopSamples\CHOS
(03 container in your Azure Blab Storage. Downloading the sample esis explained Appendix,
‘Once the source datas avallabein Azure Blob Storage, you can carey out te following steps:

‘Open anew Power BI Desktop application,

2. Inthe Power BI Desktop ribbon, click the small angl at the bottom of the Get
Data button and thenclick More.

3. Click Azur in thelist onthe lf and then Azure Blob Storage onthe ight The
Azure Bob Storage connection dialog willbe displayed.

4. Enterthe account name that you are using to connect 0 Azure Blob Storage
‘The Azure Blob Storage dialog wil lok like the one shown in Figure 5
Ifyoua using corporate Azure Blob Storage account, then your system.
‘administrator wll provide this Ina text scenario youcan find this inthe Azure
Management Portal by opening the Storage Accountblade and copying the
Blob Serie Endpoint.

Azure

igure 5:28, The Azur Blob Storage connection dial.
156

CUTER LOND DATA FON THE WER ANDTHE 3.000

5. GlckOK. The Azure Blob Storage Account Key dialog will appear

6. Inthe Azure Management oral, copy an aecountkey. These can be found
In the Azure Management Poralby clicking the Storage Account blade and
then licking Access Keys Ifyou have been sent an account hey by stem
administrator den use that instead.

Paste the account ky Int the Azure Blob Storage Account Key dialog. The dialog
willok ke the onein figure 3.30.

Figure 539, The Azure Blob Storage Account Key dialog

8. Giek Connect The Navigator will appear showing the Ist offiles in the selected
container You can see an example Sin Figure 5 0.

Figure 540, The Navigator dale showing available containers in Azure Blob Storage

9. GckLoad. Te st ofiles stored in Azure vil appear in Power DI Desktop.

157

CAPTOR = LOAD DIA FFOM TUE WED ANDTHE CLOUD

ote ti important one that for ne moment a east, what you have retumed from Azure ist of
ala ls. Chapter 8 explains how to select an load data rom some oa ofthe avale fes Into Power
Bl Desktop, where they can be used as a bass for analytes.

Azure Security

Al coud service providers take security extremely seriou. As ou have sen in this chapter, you wil always
bbe obliged to enter some form of security token and/or specify valid username and password 1 connectto
cloud based data

‘All dhe security information that you entered i stored in he Power B Desktop Storage Settings. This
(an be removed or modified inthe same way that you learned to update or remove database security
Information in Chapter.

Conclusion

Inthischapteryou sw, rey, howto reuieve data that you access using he Internet. This ca rango rom.
table fdataon a web page to a masivo Azure SQL data warehouse. ltematively, perhaps ou need.
tocreatedashiboards based in your Salesore, Google Analytics or MS Dynamics 65 daa. Maybe your
organization has decided to move is datacenters 1 he cloud, and is using SQL Servern Azur or Amazon
Redshift. In any ease, Power BI Desktop can connect and access the data valable in ese series and
repositories can even acess ig data in Hadoop.

‘Given the vast number o online sources, this chapter could only scratch the surface of his huge range
of potential data repositories. However, as Power Bl Desktop isrigrous aboutstandardizng acceso daa,
‘you should beable o apply the approaches you have learned in this chapter o many othe data services,
bothcurrne and future

“This chapterconclides the set of our chapters hat introduced you some ofthe many and varied
dat sources that you can use with Power BI Desktop. In the course of our chapters, you have seen how to
load data rom a selection of te more frequently used avalable sources. The good nevsis har Power BL
Desktop can read data from dozens more sources, The bad news that ould take a whole book to go into
allof them in detail

‘Sol will not be describing any other datasources in this hook. This because now that you have come
to appreciate he core techniques that make up the extremely standardied approach that Power BI Desktop.
takes to loading dats, you can probabiy load ay possible data ype without needing much more information
fom me. Should you need any Spec information on other datasources, the yout bes por ofcals the
Microsoft Power BI website. Thisseurremiyat tp: //support, one,

Now tat you can ind, aces, and load the data you need into Power BI Desktop, itis time to move
con othe nex ep. This means cleansing and restructuring he datasets 0 thi they sul youranalyal
requirements. Handling these challenges isthe subjectofthe next re Chapters.

158

CHAPTER 6

Dealing with Datasets

Inthe previous four chapters you sr some of the ways in which you can And and oad data nto the Power
BI Desktop data model Inevitably, ths isthe rst part ofany proces that you ollow to extract, transform,
and oad data, Veis quite definitely only a first step, Once the datas in Power BI Desktop, you need to
Snow howto adapt itt suit your requirements in a mule of ways. This because nor al data ready
tobeused immediate Quite ote, yu have to do some inal work on the data to make more casi
useable in Power BI Desktop Tweaking source datas general refered to as data transformation, whichis
the subject ofthis chapteras well asthe next two.

‘The range of transformations that Power B Desktop offers is extensive and varie. Learning to apply the
techniques that Power BI Desktop makes avale enables you to tke data asyou find then leanse and
push back in the Power Bl Desktop data mode asa series o coheren and structured data bos. Only
then sit ready tobe used to create compelling dashboards and reports.

sit al 00 easy 0 be ovenchelmed at eat inal by the extent ofthe daa transformation options
bat Power Bi Desktop hasto offer, Ihave grouped the possible modifications int four categories. These
‘ategories are my own, and are merely suggestion to facilitate understanding

+ Data transformation: This includes adding and moving columns and rows,
enaming columns, aswell as leringd

+ Data modification: This covers altering he actual data nthe rows and columns
ofa dataset,

+ Extending datases This encompasses adding further columns, possibly expanding
‘esting columns into more column orrows, and adding calculations.

+ Joiningdaases This involves combining multiple separate dass —possby fom
dierent data sources io à single dataset

‘This chapter inroduersyou tothe core techniques that ou can apply to shape each individual dataset
hat yo have loaded. These transformations include

Renaming removing and reordering columns
+ Removing groups or set ofrows
+ Deduplicaing datasets

+ Soringthe data

+ Escludingrecords y fering the d
+ Groupingrecords

‘Adam Asia 2018 159
A Asp Pro Power Bestop hs 101007978-1-$842.32104.6

ERE = OBUNGYITHONASETS

Inthe nextchapter you lea how to leanse and modify data. In Chapter you see how to subset
columns 1 xtractpar ofthe avaliable data ina column, caleulate columns, merge data fom separate
queries and add further columns containing different ypeso ealculaions, and you lean about pivoting
“and unpivoing data So, you cannot find what youarelooking for inthis chapter her sa good chance
thatthe answer isin the following two chapters

Inthis chapter {il also use asetof example fs that you can find onthe Apress website Ifyou have
followed the instructions in Appendix A then thee les aren the C:\PowerBiDesktopSamples\CH06 older,

Power BI Desktop Queries

In Chapter, you saw how to load source data direct into Power B Desktop and use lt immediately ©
create dashboards Cleary this approach presumes that the data that youare using perfectly structured,
lean, and eror fe. Source data is nearly always correct and ready to use in reports and dashboards when
incomes rom “corporate” datasources such as data warehouses (beldin relational, dimensional ota
databases). This not always the case when you are faced with multiple disparate sources of data hat have
not been preceansed and prepared by an IT department The everyday real that you could have to
anse and transform mucho he source data that you wil use fr your Power BI Desktop dashboards.

"The really good news sat the kind of data transformation dat used o require expensive servers and
Indust strength software snow valable for re. Yes, Power Bl Desktop comes with an awesome ETL.
(Goer, Transform, and Load) tool that ean rival many applications that costhundreds of thousands of
datas,

Power BI Desktop data transformation scarred out using queries. As you sa In Chapter I, you donot
have to modify source data. You can load direct fis ready for use. Ve you need cleanse the data,
you add an intermediate step between connecting the data and loading it nt the Power BI Desktop data
model. Tis intermediate step ues the Power BI Desktop Query Editor to weak the source

So how do you apply quertsto transform your dat? You have two choles:

“+ Load the data fst rom one or more sources, and then ansform tater
+ Eliveach source data element in a query before loading.

Power BI Desktop is extremely forging. It does not ore youto select one othe her method and
then lock you no the consequences your decision. You can load data stand ten rea that it
needs some adjustment, svtchto the Query Edtorand make changes, and then retum to creating your
dashboard, Or you can rst focus on the data and to ett as polished and perfect as possible before you
sur building reports. The choice sntrely upto You.

"To make thispoint les ake lok at both afthese ways of working

ole Also eng pedantic and ldtstiond, | woud ais you to make notes when creating really
‘complex transformations, because goin back fo a soliton and tying to make adjustment later can be alu
‘when they ae nt documented at al

Editing Data After a Data Load

In Chapter, you saw howto load the Excel workbook CarSales lt directly into the Power El Desktop data
model and use ito create a starter dashboard, Now et presume that you want to make some changes o
the data structure ofthe data that you have already loaded Special, you wantto rename the ComPrice
column. The fe a you want to mods CHD6Examplel-pbl< le in the C-\PowerBiDestopSamples\,
CH director

2

Figures:

a

METERS DEANE WT DATASETS

{Launch Power BI Desktop.

Open the sample le C:\PowerBiDesktopSamples\CH06\CHOGExamplel.pbir
Take look at the Field list and note that here is afield named CostPric.

the Power BI Deskiop Home ribbon, cick the Edi Queries button. The Power
BI Desktop Query dir wi open and display the source data asa table. The
‘window wil look ke Figure

1. The Power BL Desktop Query Editor

gd the thle ofthe GostPrie column. The column willbe selected and he
‘le wil apearin elo.

Select Rename rom the context ment,
‘Type VehtcleCost and press Ener The column tle wil change 0 VehleleCos.

Inthe Power BI Desktop Query Editor Home ribbon, dk the Close & Apply
button. The Power BI Desktop Query Editor wil dose and retum you to the
Power BI Desktop window. ehicleCost has replaced CostPric anywhere that it
‘was used inthe dashboard, This is immediatly visible nthe Feds list.

161

ERE = OBUNGYITHONASETS

‘hope that this simple example makes clear that ansforming the source data sa quckand painlss
proces. The technique thatyou appied_renaming a column only one of many dozens of possible
techniques that you ean apply 1 transform your data, However itis not the specifi ansformaton thats
the core lea o takeaway here. What you need to remember har the data that underpins you dashboard
¡salvas present and it ison asingl click away. Atany time, you can "Ri" 1 the data and make changes,
simply y clicking the Edi Queries button nthe Power Desktop window. Any changes that youmake and
confirm wl update your dashboards and repors instantaneously.

Note Atemative you want you can eit the query behind any abe that is visible inthe Report or Data
‘ws simpy by right-clicking in the table and selecting Eat Query fromthe context menu,

Transforming Data Before Loading

(On some occasions you mig prefer o juggle with your data before you lad it. This isa vation on the
approach ar you have used in Chapter | when cretingasimple dashboard, Do de following to transform
‘Your data before appears in she Power BI Desktop window:

1. OpenanewPower BI Desktop window

2. Inthe Power BI Desktop Home ribbon, click the ny angle inthe Get Data
Duron

3 Select Exc inthe menu and open the Excel fle C\PowerBiDeskiopSamples\
(CHOG\CarSalessse.

4. Inthe Navigator window, select the BaseData worksheet
‘lick the Eat button (not the Load button),

6. The Power I Desktop Query Bor will open and display source
dataasa able

»

7. Camry outsteps through from the previous example rename he CosPrce
column

8. Inthe Power BI Desktop Query Editor Home ribbon, cl the Close & Apply
burn. The Power B Desktop Query Eto wil lose and return youto the
Power BI Desktop window. You wil se the Apply Query Changes dialog while
‘the data loaded, ike the one that youean see Figure

Apply query changes

Figure 6-2. The Apply Query Changes dialog

102

OWETER = DENG WMHOATSETS

‘Thieme, you have made a simple modification othe daa before loading the dataset into he Power BI
Desktop data model. The data modification technique was exactly the same. The only diference between
loading the data directly and ang a detour va the Query window was clicking Edit instead of Load in the
‘Navigator dialog, This means that the data was only loaded once you had finished making any modfcaions
othesoure datan the Power Bl Desktop Query Editor.

Query or Load?

Power BI Desktop always ge you the choice ofloading data dred into ts data model ortaking à
constructive detour via Power BI Desktop Query The path tha you follow i entrey upto you and clealy
“depends on each set of circumstances. Nonetheless, you might wantto consider he following base
principles when faced witha nev dashboarding challenge using unfamilia ata:

+ Aeyou convinced thatthe datas ready use? That st clean and wellstuctured?
éso, then ou can te ladingl diet int the Power Bt Deskop daa model

+ Areyou faced with multiple data sources that need tobe combined and molded into
‘coherent structure? If thisisthe case, then you really need transform the data
‘sig Power B Desktop Query

‘+ Does the data come rom an enterprise data warehouse? This could beheld ina.
relational database, a SQL Server Analysis Services cube, even an in-memory
tabular dota waehouse,Athes datasources re nel always the resultof
many hundreds or even thousands ofhoursof work deansing preparing and
‘structuring the data, ou can probably load these straight into the data mode.

+ Does the data need tobe preaggreated and tered? Think Power BI Desktop Query:

+ Areyou kelo need to change the feld names to make the data more manageablet
neouldbe simpler toload the data dirty inte data modeland change them tee.

+ -Areyou faced wit oso lookup tables that need tobe added to a re” data abe?
‘Then Power BI Desktop Query your frend

‘+ Does the data contain many superfucus or erronegus elements? Then use Power BI
Desktop Query to remove these sa ft step.

+ Doesthedatancedto be rationalized and standardized to make easier to handle?
Inthiscase, the patho success ls va Power Bl Desktop Query

+ ste datasource enormous? If isso case, you could save imebyedting the
data rst in he Query Edito: This because the Query Editor only loadsa sample of
‘the data fr youto tweak The ene dataset will ony be loaded when you confirm all
yourmodifications and close the Query Editor.

‘These kinds of questions are onl rough guidelines Yet they can belp point you inthe ight direcion
‘nen you are working with Power BI Desktop. Inevitably, the more tha you work wth this application, the
‘more you will develop the reflexes and inuton that wil help you make the corra decisions Remember,
however that Power BI Desktop is thereto help, and that even a dret loaded dataset based on a query.
So you can always od data and then decide wo tweak the query structure ter you need 1. Alternative;
ding atan a Query window can bea great opportunity to ake a laser look at your data before loading it
Imo the data model and only adds couple of click.

So feel fre to adopt away of working that ou feel happy wit, Power BI Desktop will adaptto yourstyle
asl and almost init letng youth rom data to dashboards so uly that wi key become
second nature,

163

ERE = OBUNGYITHONASETS

‘Te remainder ofthis chapter wil take you through some ofthe core techniques that you need to know
Lo clame and shape your dat, However before geting int all detal, lets take a quick, high level look
the Power BI Desktop Query Editor and the way that itis ld out

The Power BI Desktop Query Editor

‘Allofyour data transformation il tke place in the Power BI Desktop Query Editor. isa separate window
from the one where you create your dashboards and thas slighty diferent layout,
"The Power BI Desktop Query Editor consists of six main elements:

‘+ Thefoursbbons: Home, Transfonm, Add Column, and View

‘+ The Query listpane containing athe queries that have been added 1 a Power BL
Desktop fle

+ The Data window, where you can sea sample ofthe data fora selected query
‘+ The Query Setings pane that contains the ls ofseps used to transform data

‘+ The formula bar above the daa that shows he code (written inthe Power IM
language) tha performs the selected transformation sep

“+ Thestatusbar (athe boom ofthe window) har indicates useful information, such
asthenumberof ows and columns in a query abe, and the date when the dataset
as downloaded

‘Te callout for thes clement are shown in Figure 63.

‘ D amas

gare 6.3. The Power BI Desktop Query Eto explained
164

OWETER = DENG WMHOATSETS

ote Ocasional you vil se othe bons appear in specif circumstances. Yu can see an example of
this for dealing wit ts in Chapters & and 9.

The Applied Steps List
Data transformation is by its very nature a sequential process, So the Query window stores each modification
"stos make wen you arcleasingand shpingsouce data. The various men hat make pa da
‘ranstormation proces ae ited in the App Steps is ofthe Quer Stings pane the Query do

"Tue Power Bi Desktop Query Elo es ot numberthe ups la at rndormaton proces it
cerainly remember each one. They start at the tp ofthe App Sepsis (neatly eh he Soe
Sip) andan enend to don of nda sep tat ce te vol of our data nou los neo
A da modal own, you want coder the Query Edo a ind of "macrorcordez

“Moreover as you ik eachstp in the Applied Step ist the data In the Daa window changes treet
‘heres ofeach transformation ging you complete and vis ral fl te modicatonsthatyou
hve applied othe dataset.

“The Apple Sepals ges a ditinct name othe ep foreach and every data modifican oon
shar jou over nts chapter andthe ne. As can be important understand ect what ech neon
cual sches, ways raw o your ain the sandal name tha Power Desktop Quy
sp.

The Power BI Desktop Query Editor Ribbons
Power BI Desktop Query Editor ses {ne August 2017 version at east four ribbons They are
fundamental o what youlear nthe cours of his chapter. They ares follows

+ TheHomeribbon

+The Transformsibbon

+ The Add Column sibbon

+ TheViewstbbon

am not suggesting fra second that you need to memorize what lle buttons in these ribbons do.

‘What hope that you are ble o use the following ie descriptions othe Query Editor ribbon buttons o
getan den ofthe amazing power o Power BI Desktop in the Bed of data transformation. o fyow have an
{nia dataset dat snot quie as you need, you can take a look tte resources that Power Bi Desktop has
‘0 offer and hove they can help. Once you ind the funcion that does what you are looking or, you ean Jump
to therelevan section for the full details on howto apply

The Home Ribbon

‘Since we willbe making intense use ofthe Power BI Desktop Query Bator Home ribbon to transform dato, It
Isimportanto have an idea of what can do. explain the various options in Figure 6-1 andin Table 1.

165

ERE = OBUNGYITHONASETS

HE
1
3
’

i

it

Wot ot A
E
ö
#

Table6-1. Query Eltor Home Ribbon Options

pion Description

(lose & Apply Finishesthe processing steps; saves and closes the query.

NewSource Let you discover and add anew datasource tothe set of queries

Recent Sources ss alle econ datasources that you have used.

Enter Data Let you add your own specific daa Ina custom table,

Data Source Senings ‘Allows you to manage settings or datasources that you have already
connected to,

Manage Parameters Let you view and modify any parameters defined fr this Power BI
Desktop fle,

Refresh Preview Refreshesthe preview data,

Properties Displays the core query properties.

‘Advanced Bator Displays the N° language dor.

Manage Let you delete, duplicate, or reference a query.

Choose Columns Lea ele lame retain fram athe clans stata nthe

Remove Columns Letsyou remove one or more columns,

Keep Rows Keeps the specified number of rowsat the top ofthe table.

Remove Rows Removesa specified number ofrows rom the top ofthe data able

Sor Sorts the table using the selected column asthe ortkey

Sp Column. Split a column into one or many columns at a specified delimiter or after a

speed number of characters.

Continued)

106

‘able6-1 (continued)

OWETER = DENG WMHOATSETS

Spion. Description

(Group By ‘Groups the table using a speciied set olcolumns and aggregates ny
amer coms fr this grouping.

Datatype Appllesthe chosen datatype to the column.

Use Fist Row As Header Usesthe fist row asthe column ies

Replace Values Cates out a searc-and-replace operation onthe data in a column oF
‘columns. This only affects the complete data in column,

Merge Queries Joinsa second query table tothe current query results and aggregates
Add data rom the second tthe frst.

Append Queries ‘Adds the data from another query o the current query in the current
Power BI Deskop fle.

Combine Files Ads he data rom a series of similarly structured les nto a single able

The Transform Ribbon

‘The Transorm ribbon, as ls name imple, contains a wealth functions that can elpyouto transform
our data The various options contains are explained in Figure 6-5 and Table 6 2.

== er

Figure. The Query Eto Transform ribbon

E]

167

ERE = OBUNGYITHONASETS

Table 6-2. Query Editor Transform Ribbon Options

Option

Description

Group By
Use First Row As Headers
‘Transpose

Reverse Rows

Count Rows
DawaType
Detect Data Type
Rename

Replace Values

ru
Pot Column

UnpivorColumns

Move
Convert List
split Column

Format
Merge Columns

act

Parse
Saiten

Standard

Scene

Groups he table using a specified set o columns; aggregates any numeric
columns or tis grouping.

ses he rst row a the column tiles.
‘Transforms the columns ino rows and the rows into columns.

Displays dhe source data in reverse nde showing the nal ows atthe top of
the window.

Couns the rowsin the table and replaces he data wth he row count
Applies the chosen data type othe column,

Detects the correct datatype to apply o multiple columns.
Renamesa column.

Carries outa search-and-replace operation inside column, replacing
specified value another value.

Coples te data from cells above or below into empty cells in the column.

Createsa new set of columns using the data in the selected column asthe
column ites.

“Takes the values in a setofeolumnsand unpivos the data, creating wo new
columns using the column headers asthe desripve elements

Moves column.

Converse contents of column toas
Splits column into one or many columns ata specified elite or fer à
speeified number ofcharacers

“Modifies the ex format of dat na column (uppercase, lowercase,
capitalization) or removes walling spaces.

‘Takes the data fom several columns and places it in a single column, adding
an optional separator character

Replaces the data in a column using a defined subset ofthe current dats

You can speclfya number ofcharacterstokeep ram the star or end ofthe
column, set range of characters beginning ata specified character or even
list number of characters inthe column.

Creaes an XML or SON document rom the contents ofeach ellin a
column

Retums the Sum, Average, Maximum, Minimum, Median, Standard
Deviation, Count, or Distinct Value Count fr al the values in the column.
Carries out a base mathematica calculation (add, subtract, dive, mul
lntege-dvide, or rtur the remainder) using a value that you specify
applied to ach el in the column.

Carries out a base scenic calelaion (square cube, power ofn, square
‘oot exponent, logarihm, or factorial) foreach cellnthe column.

Cond)

OWETER = DENG WMHOATSETS

‘ube 6-2. (continued)

Option Description

rsonomeny Carre out a base igonometi calculan (Sine, Cosine, Tangent,
reine, AreCosine, or ArcTangent) using a value that you speci applied to
achete column.

Rounding Rounds the values in he column either t he nex integer (up ot down) oF
toaspeciedfacor.

Information Replaces the value inthe column with simple information: 1s Os Even,
‘or Postve/ Negative.

Dae Isolates an element (day, month, yea ee) froma date value in column.

Time Isolates an element (hous, minute second, te) rom adatetime ortime
valuein column,

Duration Caleults the duration from a value that canbe interpreted as a duration in
days hours, minutes, and so or

Expand ‘Adds the (idenialysuctured) data rom another query tothe current
‘query.

Aggregate Calculats the sum or product of numeric columns from another query and
aud the rest tothe current query.

act Values xaractsthe values ofthe contents fa column ass single text value.

Ser uns scripts fom languages such as“

The Add Column Ribbon

‘The Add Column ribbon does a lot more than justad columns. also contain functions to break columns
oven nto multiple columns, and to add columns containing dates and calculations based onexising
‘columns. The various options contas ar explained in Figure and Table 63.

‘Figure6-6. The Query Editor Add Column ribbon

169

ERE = OBUNGYITHONASETS

Table6-3. Query ltr Add Column Ribbon Options

Option Description
Column From Examples Les you use one or more columns as examples to create anew column.
Custom Column ‘Adds anew column using formula to create the column contents.
Invoke Custom Function Applesan“M” language function o ever row.

Conditional Column

Index Column
Duplicate Column
Format

Merge Columns

Eo

Parse
Sates

Sandard

Scene

Trigonomeny

Rounding
Information

Date
Time

Duration

‘Adds anew column that condtionally adds he values rom the selected
column.

‘Adds sequential number in anew column to uniquely idem each ron.
Creates. copy ofthe cumenteolumn.

Mois the tex format of data in column (uppercase, lowercase,
‘apitalizaton) or emovestraling spaces.

‘Takes the data from several columns and places ina single column,
adding an optional separator character.

‘Replaces the data in column usinga defined subset of he curren data.
You canspecifyanumberofcharaers to keep from the surtorend ofthe
column, seta range of characters begining at a specified character oreven
list the numberof characters in the column,

‘Creates an XML orISON document rom the contents ofeach cel
column,

‘Returns the Sum, Average Maximum, Minimum, Median, Standard
eevation, Count, or Distinet Value Count for al the values in he column.
Carries outa basie mathematical ealculation (add, subtract divide,

‘multiply nteger-dvide, orretu the remainder) using value that you
spec applied to each cel inthe column.

Caries out base scent calculation (square, cube, power fn, square
oot, exponent, logarithm, oF factorial) foreach alin the column,

‘Caries outabase igonomene calculation (Sin, Cosine, Tangent,
“AeSine, AreCosin, or ArcTangen) using value that you spech applied to
‘each cal inthe column.

Round the values inthe column eitherto the nextintegee(upor down) or
toaspecitedticor

‘Replaces the valuein the column with simple informadon:1s Odd, Is Even,
orPosive Negative

Isolates an element (day, month year, et.) from a dat value in column,
ISolates an element (hour, minute, second, ic) fom adate/ime ortime
valueinacolumn.

Calculates e duration from a value that con be nterpreedas a dumtonón
(days hous, minuts, and seconds.

mo

TES = ELN WMH DADES

The View Ribbon

"The Viewribbon es you alter somo ofthe Query Edito stings and soe the underying data transformation
code, The various options that contains are explained in th next chapter

Dataset Shaping

So you ae now looking ata data table that you have loaded into Power B Desktop, For argument sake,
Let assume that it ste C\PowerBiDesktopSampls\CHO6\CH0SBxample.pbi fl rom the sample data
(directory, and that you have clicked the Edit Queries burton o display the Power BI Desktop Query Editor
‘What an ou do tothe BaseData dataset thats now visible? Iris time to take look a some ofthe core
techniques that you can apply to shape the intial dataset. These include the following:

+ Renamingcolumos
Reordering columns

+ Removingeolumns

+ Mergingeotumns

+ Removing records
Removing duplicate records
+ Fiteimgthe dataset

have grouped these techniques together as they eco ina size and shape of the daa. Als, ls
generally not only good practice but als easier for you. the data modele ifyou begin by ecludingany rows
“and columns that you do no ned, Laso ind tease to understand dataset ifthe columns aelogiall ad
out and given comprehensible names from the star Alin all his makes working with the data easier the
Jong,

Renaming Columns

Although we rooka quicklookat renaming columns inthe fist pages ofthis chapter let ook this

technique again in more dea Ladmi that renaming columns ot actually modifying the form ofthe data

‘able. However when dealing wih dat, considere ital to have all data clea lentille This implies

‘meaningfulcolumn names being apple to each column, Consequent, consider this modification tobe

fundamental tothe shape ofthe data andalsoas an essential best practice when importing source dat.
Torename column:

(lckinside he column that youwantto rename
lick Transform to activate the Transform ribbon.
(lckthe Rename bution. The column name will be highlighted.

Enter thenew name or edit the existing name.

soon

Press Enter or lick outside the column te.

‘The column will now havea new te. The Apple Steps is onthe ight wll now contain another
clement, Renamed Columns This step wl be highlighted.

Note Asan ltematve to using the Transfom ribbon, you can rightclick he column te and select Rename.

m

CAMPERO over

Reordering Columns

Power BI Desktop wild data as itis defined the datasource, Consequenti the column sequence will
bbe entirely dependent on the source data (or by a SQL query ifyow used source database as described

in Chapter) This column order need not be definir however aná you can reoder the columns that
helps you understand and del withthe data. Do the following o change column order:

1. Clickthe header ofthe column you want to move,

2 Dragthecolum et or righ tts new postion. You wil se the column ide
slide tea through the column tiles as you do this, and ticker gray line wi
indicate where the column willbe placed once you release the mouse button.
Reordered Columns will appear inthe Applied Steps Ist.

Figure 6 shows this operation,

Ro ren

Manage este

Manage terco

igure 6-7. Reordering columns

your query cosas dozens—or even hundreds—of columns you may find that dragging a column
around can be low and laborious. Equal olumns are extremely wide can be dic to "nudge" a
column lef o right Power Bl Desktop can come to your ld in these clrumstances with the Move button in
the Transform ribbon, Clcking this button gives you the menu options that ae outined in Table 6-4.

Table6-4. Move Burton Options

Option Description
Let Moves the eurent selected column w the left fe column on its immediate let
Right Moves he eurent selected column w the ght othe column on its immediate right.
To Beginning Moves the curently selected column tthe left ofallthe columns inthe query.
Toind Moves the currently selected column t the right alle columns inthe query.

m

TES = ELN WMH DADES

‘The Move command also works om a set of columns that you hae selected by Cui-lickng and/or Shit
licking, Indeed, you can move a selection o columns that snot contiguous you need 0

Note You need to select column (oa set of columns before cäcking the Move button you dono, hen
the frst time that you use Move, Power Bl Desktop Quer selects the columns) bu does not move it.

Removing Columns
Sohow do you delete column ore columns Like ths:
1. ense column you wat to delet ori you want delete several
Columns at once Cutie tier ofthe columns that you vea ot.
2. Clekihe Remove Columns buon inthe Home ion. The column) be
deleted nd Removed Column wie nea clement inthe Applied Spelt

‘When working wit imported datasets over which you have had no cont, you may frequent find that
sou only ned a fe columns ofa large ata table, this ste ease, you ill son get tired of Cri
‘numerous columnsto select those you want to remove. Power BI Desktop has an alternative method. Just
selectthe columns ou want to Keep and delete the others. odo this

1. Cari the tes ofthe columns that youwant to keep.

2. Gickthe small range in the Remove Columns button in he Home ribbon
Select Remove Other Columns rom the ment, Al unselected columns wil be
deleted and Removed Other Columns willbe added othe Apple Stops ie.

‘When selecinga coniguous range of columns to remove or kep, you can use the standard Windows
‘Sted technique to selec fom the fst tothe last column inthe block of columns that you want select.

Note _Bothof these options fr remaving columns are also avalble from the context menu, you prefe.
shows Remove (r Remove Colurns, here are several columns selected) when deleting columns, a well
‘as Remove Other Columns I you right-click column te.

Choosing Columns

Afyou prefer nto scroll through a wide dataset yet sil need t select a subset of columnsas the bass for
sour report, then there another way to define the collection leds that you want to use. You can choose
‘he columns that you want to keep (and, by detinion, those that you want exclude Ike ths

1. Open the sample fle CHOGExamplel piri the folder
‘CAPowerBiDesktopSamples\CHOS unless tis already pen,

2 Inthe Home ribbon lick the Eat Queries button. The Query Editor wilopen.
3 Inthe Homo bon ofthe Query Estas cick the Choose Columns hat.

4. Gk (Select ll Columns) to deseletthe entire collection o columns In the
dater.

m

CAMPERO over

8. Selectihecolumns Make, Model, Color and SalePice. The Choose Columns
og wil look ike the one in Figure 6

Choose Columns

Figure6-8. The Choose olumnsdiatog

6. Clik OK. The Query dior wil only display the columns that ou selected.
‘The Choose Columns dal comes witha couple of extra functions that you might nd useful when
choosing the se of columns that you want to work With:
‘+ Youcansorthe column stinalphabeicl order (or indeed, rever tthe original
conde) by licking the Sort con {he small AZ) at the top ight athe Choose
Columns dialog ad selecting te required option.
+ ou can fer the lstof columns hat displayed simply by entring a few characters
{nthe Search Columns eld atthe top ofthe dialog and then pressing Enter.

+ The( Select AllColumns) option swiches between selectingand deselecting alte
columns thelist.

mm

Aspin, Adam - Pro Power BI Desktop-Apress (2018).pdf

About This Presentation

Slide Content

Tags

Categories

Download

Quick Actions

Statistics

Related Slideshows

Aspin, Adam - Pro Power BI Desktop-Apress (2018).pdf

About This Presentation

Slide Content

Slide 1

Slide 2

Slide 3

Slide 4

Slide 5

Slide 8

Slide 18

Slide 19

Slide 22

Slide 23

Slide 24

Slide 26

Slide 27

Slide 28

Slide 29

Slide 30

Slide 31

Slide 32

Slide 33

Slide 34

Slide 35

Slide 36

Slide 37

Slide 38

Slide 39

Slide 40

Slide 41

Slide 42

Slide 43

Slide 44

Slide 45

Slide 46

Slide 47

Slide 48

Slide 49

Slide 50

Slide 51

Slide 52

Slide 53

Slide 54

Slide 55

Slide 56

Slide 57

Slide 58

Slide 59

Slide 60

Slide 61

Slide 62

Slide 63

Slide 64

Slide 65

Slide 66

Slide 67

Slide 68

Slide 69

Slide 70

Slide 71

Slide 72

Slide 73

Slide 74

Slide 75

Slide 76

Slide 77

Slide 78

Slide 79

Slide 80

Slide 81

Slide 82

Slide 83

Slide 84

Slide 85

Slide 86

Slide 87

Slide 88

Slide 89

Slide 90

Slide 91