Pro Power Bi Desktop: Self-service Analytics and Data Visualization for the Power User

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Discovering and Loading Data with Power BI Desktop
	The Data Load Process
	Understanding Data Load
		The Navigator Dialog
		Searching for Datasets
		Display Options
			Only Selected Items
			Enable Data Previews
		Refresh
			Source Data Refresh
			Data Preview Refresh
			Select Related Tables
		The Navigator Data Preview
		Modifying Data
	Data Sources
	Conclusion
Chapter 2: Discovering and Loading File-Based Data with Power BI Desktop
	File Sources
	Loading Data from Files
		CSV Files
			What Is a CSV File?
		Text Files
		Text and CSV Options
			File Origin
			Delimiter
			Data Type Detection
		Simple XML Files
		Excel Files
		Importing Excel and Power View Items
		Microsoft Access Databases
	PDF Files
	JSON Files
	Conclusion
Chapter 3: Loading Data from Databases and Data Warehouses
	Relational Databases
	SQL Server
		Automatically Loading Related Tables
		Database Options
			Server Connection
			Searching for Databases, Tables, and Views in Navigator
			Searching for Databases
		Searching for Tables
			Database Security
			Using a SQL Statement
			Stored Procedures in SQL Server
	Oracle Databases
	Other Relational Databases
	Microsoft SQL Server Analysis Services Data Sources
		Analysis Services Cube Tools
			Add Items
			Collapse Columns
	SSAS Tabular Data Warehouses
	Import or Connect Live
	Modifying Connections
	Changing Permissions
	Other Database Connections
	Conclusion
Chapter 4: DirectQuery and Connect Live
	DirectQuery and Connect Live
	Microsoft SQL Server Data
	SQL Server Analysis Services Dimensional Data
	Microsoft SQL Server Analysis Services Tabular Data Sources
	DirectQuery with Non-Microsoft Databases
	DirectQuery and In-Memory Tables
	DirectQuery and Refreshing the Data
	Conclusion
Chapter 5: Loading Data from the Web and the Cloud
	Web and Cloud Services
		Web Pages
		Online Services
		Microsoft Azure
	Web Pages
		Advanced Web Options
		Table View or Web View
	Salesforce
		Loading Data from Salesforce Objects
		Salesforce Reports
	Microsoft Dynamics 365
	Google Analytics
	OData Feeds
		OData Options
	Azure SQL Database
	Azure SQL Data Warehouse (Azure Synapse Analytics)
	Connecting to SQL Server on an Azure Virtual Machine
	Azure Blob Storage
	Azure Security
	Conclusion
Chapter 6: Loading Data from Other Data Sources
	Other Sources
	Power BI Datasets
	Power BI Dataflows
	R Transformations
	Python Transformations
	Common Data Model
	ODBC Sources
	OLE DB Data Sources
	Refreshing Data
		Refreshing the Entire Data in the Power BI Desktop In-Memory Model
		Refreshing an Individual Table
	Adding Your Own Data
	Conclusion
Chapter 7: Structuring Imported Data
	Power BI Desktop Queries
		Editing Data After a Data Load
		Transforming Data Before Loading
	Query or Load?
	The Power BI Desktop Query Editor
		The Applied Steps List
		The Power BI Desktop Query Editor Ribbons
			The Home Ribbon
			The Transform Ribbon
			The Add Column Ribbon
			The View Ribbon
	Dataset Shaping
		Renaming Columns
		Reordering Columns
		Removing Columns
		Choosing Columns
		Merging Columns
		Going to a Specific Column
	Removing Records
		Keeping Rows
			Removing Rows
			Removing Blank Rows
		Removing Duplicate Records
	Sorting Data
		Reversing the Row Order
		Undoing a Sort Operation
	Filtering Data
		Selecting Specific Values
		Finding Elements in the Filter List
		Filtering Text Ranges
		Filtering Numeric Ranges
		Filtering Date and Time Ranges
		Filtering Data
		Applying Advanced Filters
	Grouping Records
		Simple Groups
		Complex Groups
	Saving Changes in the Query Editor
	Exiting the Query Editor
	Conclusion
Chapter 8: Data Transformation and Cleansing
	Viewing a Full Record
	Power BI Desktop Query Editor Context Menus
	Using the First Row as Headers
	Changing Data Type
		Detecting Data Types
		Data Type Indicators
		Switching Data Types
		Data Type Using Locale
	Replacing Values
	Transforming Column Contents
		Text Transformation
		Adding a Prefix or a Suffix
		Removing Leading and Trailing Spaces
		Removing Nonprinting Characters
		Number Transformations
			Calculating Numbers
			Date Transformations
			Time Transformations
			Duration
	Filling Down Empty Cells
	Extracting Part of a Column’s Contents
		Advanced Extract Options
			Text Before and After Delimiter
			Text Between Delimiters
	Duplicating Columns
	Splitting Columns
		Splitting Column by a Delimiter
		Advanced Options for Delimiter Split
		Splitting Columns by Number of Characters
	Merging Columns
	Creating Columns from Examples
	Adding Conditional Columns
	Index Columns
	Conclusion
Chapter 9: Restructuring Data
	The Power BI Desktop Query Editor View Ribbon
	Merging Data
		Adding Data
		Aggregating Data During a Merge Operation
		Types of Join
			Use the Original Column Name As the Prefix
			Search Columns to Expand
		Joining on Multiple Columns
		Fuzzy Matching
		Fuzzy Matching Options
		Merge As New Query
		Preparing Datasets for Joins
		Correct and Incorrect Joins
			The Columns Do Not Map
			The Columns Map, but the Result Is a Massive Table with Duplicate Records
		Examining Joined Data
	Appending Data
		Adding the Contents of One Query to Another
		Appending the Contents of Multiple Queries
	Changing the Data Structure
		Unpivoting Tables
			Unpivot Options
		Pivoting Tables
		Transposing Rows and Columns
	Data Quality Analysis
		Column Quality
		Column Distribution
		Column Profile
		Profiling the Entire Dataset
		Correcting Anomalies
	Data Transformation Approaches
	Conclusion
Chapter 10: Complex Data Loads
	Adding Multiple Files from a Source Folder
	Filtering Source Files in a Folder
	Displaying and Filtering File Attributes
	Removing Header Rows After Multiple File Loads
	Loading and Parsing JSON Files
	The List Tools Transform Ribbon
	Parsing XML Data from a Column
	Parsing JSON Data from a Column
		Complex JSON Files
	Complex XML Files
	Convert a Column to a List
	Reusing Data Sources
		Pinning a Data Source
	Copying Data from Power BI Desktop Query Editor
	Conclusion
Chapter 11: Organizing, Managing, and Parameterizing Queries
	Managing the Transformation Process
		Modifying a Step
		Renaming a Step
		Deleting a Step or a Series of Steps
		Modifying an Existing Step
		Adding a Step
		Altering Process Step Sequencing
		An Approach to Sequencing
		Error Records
		Removing Errors
	Managing Queries
		Organizing Queries
		Grouping Queries
			Creating a New Group
			Renaming Groups
			Adding a Query to a Group
		Duplicating Queries
		Referencing Queries
		Documenting Queries
		Adding a Column As a New Query
		Enabling Data Load
		Enabling Report Refresh
	Pending Changes
	Parameterizing Queries
		Creating a Simple Parameter
		Creating a Set of Parameter Values
		Creating a Query-Based Parameter
		Modifying a Parameter
			Modifying the Structure of a Parameter
		Applying a Parameter When Filtering Records
		Modifying the Current Value of a Parameter
		Applying a Parameter in a Search and Replace
		Applying a Parameter to a Data Source
		Other Uses for Parameters
		Using Parameters in the Data Source Step
		Applying a Parameter to a SQL Query
		Query Icons
	Power BI Templates with Parameters
	Conclusion
Chapter 12: The M Language
	What Is the M Language?
	M and the Power Query Editor
		Modifying the Code for a Step
		M Expressions
	Writing M by Adding Custom Columns
	The Advanced Editor
		Expressions in the Advanced Editor
		The Let Statement
		Modifying M in the Advanced Editor
		Syntax Checking
		Advanced Editor Options
	Basic M Functions
	Text Functions
	Number Functions
	Date Functions
	Time Functions
	Duration Functions
	M Concepts
	M Data Types
	M Values
		Defining Your Own Variables in M
	Writing M Queries
		Lists
		Creating Lists Manually
		Generating Sequences Using Lists
		Accessing Values from a List
		List Functions
		Records
		Tables
	Other Function Areas
	Custom Functions in M
	Adding Comments to M Code
		Single-Line Comments
		Multiline Comments
	Conclusion
Chapter 13: Creating a Data Model
	The Power BI Desktop Data View
	Data View or Query?
	The Power BI Desktop Data View Ribbons
	Managing Power BI Desktop Data
		Manipulating Tables
			Renaming Tables
			Deleting a Table
			Selecting a Field from the List of Available Field Names
		Manipulating Fields
			Renaming a Field
			Deleting Fields
			Moving Fields
			Setting Field Widths
	Preparing Data
	Power BI Desktop Data Types
	Formatting Power BI Desktop Data
		Currency Formats
	Preparing Data for Dashboards
		Categorizing Data
		Applying a Default Summarization
		Defining Sort By Fields
		Hiding Tables and Fields from the User
			Hiding Fields and Tables
			Removing Hidden Fields and Tables from View
	Sorting Data in Power BI Desktop Tables
	Creating and Modifying Groups
		Creating a Group
		Modifying a Group
	Deleting a Group
	Marking a Table as a Date Table
	Designing a Power BI Desktop Data Model
	Data Modeling in the Power BI Desktop Environment
		The Modeling Ribbon
		Data View and Model View
		Model View Display Options
	Creating Relationships
		Creating Relationships Manually
		Creating Relationships Automatically
		Deleting Relationships
		Managing Relationships
		Deactivating Relationships
	Advanced Relationship Options
		Cardinality
			Cardinality Issues
	Managing Relationships Between Tables
		Cross-Filter Direction
		Other Relationship Options
			Make This Relationship Active
			Assume Referential Integrity
		Reimporting Related Tables
	Table and Field Properties
	Adding Hierarchies
	Conclusion
Chapter 14: Table Visuals
	Power BI Desktop Report View
		Switching to Report View
	The Power BI Desktop Window
	View Types
	Working with Tables
		Creating a Basic Table
		Selecting a Table
		Deleting a Table
		Copying a Table
	Changing the Table Size and Position
		Resizing a Table
		Moving a Table
	Changing Column Order
	Renaming Fields
	Removing Columns from a Table
	Table Granularity
	Types of Data
	Searching for Fields
	Enhancing Tables
		Row Totals
		Formatting Numbers in Reports
		Font Sizes in Tables
		Changing Column Widths
			Inhibiting Automatic Adjustment of Column Width and Enabling Word Wrap
		Sorting by Column
	Formatting Tables
	Table Style
	Adding and Formatting Titles
	Modifying the Table Background
	Table Borders
	Row Formatting
	Table Grid
	Column Headers
	Field Formatting
	Formatting Totals
	Conditional Formatting
		Background Color
		Text Color
		Data Bars
		Icons
	Exporting Data from a Visualization
	Conclusion
Chapter 15: Matrix and Card Visuals
	Creating a Matrix
		Creating a Row Matrix
		Adding or Removing Subtotals in a Matrix
		Column Matrix
	Expanding and Drilling Down and Up
		The Data/Drill Ribbon
			The Matrix Navigation Icons
			The Context Menu for Matrix Items
	The +/- Icons for Each Individual Item in a Level
		Displaying Data at the Previous Level
		Displaying Data for a Sublevel
		Drilling Down at Row Level
		Drill Down Using Click-Through
		Drilling Down at Column Level
	Visualize Source Data
	Viewing Records
	Including and Excluding Matrix Elements
	Displaying Multiple Values As Rows
	Formatting a Matrix
		Stepped Layout
			Subtotals
			Placing Subtotals
			Custom Subtotal Settings per Level of Matrix
	Sorting Data in Matrices
	Cards
		Formatting Cards
	Multirow Cards
		Formatting Multirow Cards
		Sorting Multirow Cards
	Switching Between Table Types
	Conclusion
Chapter 16: Charts in Power BI Desktop
	A First Chart
		Creating a First Chart
		Converting a Table into a Chart
		Deleting a Chart
		Basic Chart Modification
	Basic Chart Types
		Column Charts
		Line Charts
		Pie Charts
	Essential Chart Adjustments
		Resizing Charts
		Repositioning Charts
		Sorting Chart Elements
	Donut Charts
	Funnel Charts
	Multiple Data Values in Charts
	100% Stacked Column and Bar Charts
	Scatter Charts
	Bubble Charts
	Waterfall Charts
	Ribbon Charts
	Dual-Axis Charts
		Line and Clustered Column Chart
		Line and Stacked Column Chart
	Data Details
	Drilling into and Expanding Chart Data Hierarchies
		Drill Down
		Expand All Down One Level in the Hierarchy
		Go to the Next Level in the Hierarchy
	Including and Excluding Data Points
	Conclusion
Chapter 17: Formatting Charts in Power BI Desktop
	Multiple Chart Formatting
	Chart Legends
		Legend Display
			Legend Position
			Legend Title
		Chart Title
		Chart Data Labels
		Chart Background
		Data Colors
		Plot Area
		Axis Modification
			Modifying the X Axis
			Modifying the Y Axis
		Chart Borders
		General Attributes
		Chart Aspect Ratio
	Specific Chart Formatting
		Line, Area, and Stacked Area Charts
		Pie and Donut Charts
		Ribbon Charts
		Funnel Charts
		Scatter and Bubble Charts
	Bubble Chart Play Axis
	Chart Analytics
	Scatter Chart Symmetry Shading and Ratio Line
		Symmetry Shading
		Ratio Line
	Conclusion
Chapter 18: Other Types of Visuals
	Tree Maps
		Drill Down and Tree Maps
		Formatting Tree Maps
			Legend
			Data Colors
			Data Labels
			Category Labels
			Title
			Background
			Lock Aspect
			Border
	Gauges
		Formatting Gauges
	KPIs
	R Visuals
		R Options
	Python Visuals
		Python Options
	Decomposition Tree
	Key Influencers
	Conclusion
Chapter 19: Third-Party Visuals
	The Power BI Visuals Gallery
	Loading Custom Visuals from Disk
	Removing Custom Visuals
	A Rapid Overview of a Selection of Custom Visuals
		Aster Plots
		Radar Charts
		Bullet Charts
		Word Clouds
		Streamgraphs
		Tornado Charts
		Chord Charts
		Sankey Diagrams
	Horizontal Bar Chart
	Attribute Slicer
	Histogram Chart
		Correlation Plots
		Countdown Timer
	Infographic Designer
	Custom Slicers
	Conclusion
Chapter 20: Maps in Power BI Desktop
	Working with Bing Maps
	Creating Maps in Power BI Desktop
	Using Geographical Data
		Geographical Data Types
			Define the Data Category in the Data Model
			Add Multiple Levels of Geographical Information
	Drilling Down in Maps
	Adjusting the Map Display in Power BI Desktop
		Positioning the Map Elements
		Zooming In or Out
		Multivalue Series
		Highlighting Map Data
	Filled Maps
	Shape Maps
		Map Keys
		Adding Shape Maps
	Formatting Maps
		Maps
			Data Colors
			Category Labels
			Bubbles
			Map Controls
			Map Styles
		Tooltips
		Shape Maps
			Defining a Color Range for Data
	ArcGIS Maps
		Creating an ArcGIS Map
		Selecting a Basemap Type
		Selecting a Location Type
		Adding a Map Theme
		Choosing a Symbol Type
		Adding Pins
		Adding a Reference Layer
		Adding Infographics
	Conclusion
Chapter 21: Filtering Data
	Filters
	Filter Hierarchy
	Visual-Level Filters
		The Filters Pane
		Collapsing and Expanding the Filters Pane
		Adding Filters
		Applying Filters
		The Select All Filter
		Clearing Filters
		Adding Filters
	Removing Filters
	Filtering Different Data Types
		Filtering Numeric Data
			Range Filter Mode
			Numeric Filter Options
		Filtering Date and Time Data
			Date and Time Filters
			Date Filter Options
			Relative Date Filtering
		Filtering True or False Data
	Advanced Text Filters
		Applying an Advanced Text Filter
		Clearing an Advanced Filter
		Reverting to Basic Filtering
		Text Filter Options
			Top N Filtering
	Specific Visualization-Level Filters
	Multiple Filters
	Page-Level Filters
	Report-Level Filters
	Filter Field Reuse
		Requiring Single Selection
	Using the Filter Hierarchy
	Filtering Tips
		Don’t Filter Too Soon
		Annotate, Annotate, Annotate
		Avoid Complex Filters
	Conclusion
Chapter 22: Using Slicers
	Slicers
		Adding a Slicer
		Applying Slicers
		Clearing a Slicer
		Deleting a Slicer
			Converting a Slicer to Another Visual Type
		Modifying a Slicer
	Date Slicers
	Formatting Slicers
		Slicer Orientation
		Slicer Responsiveness
		Modifying the Outline
		Adjusting Selection Controls
			Adding or Removing the Select All Box
			Enabling Multiselect
			Enabling Single Select
		Setting the Exact Size and X and Y Coordinates of a Slicer
		Formatting the Slicer Header
		Formatting Slicer Items
		Sorting Slicer Elements
		Switching to Dropdown Slicers
		Exporting Slicer Data
	Using Charts As Slicers
		Charts As Slicers (Cross-Filtering)
		Highlighting Chart Data
		Cross-Chart Highlighting
		Highlighting Data in Bubble Charts
	Charts As Complex Slicers
		Column and Bar Charts As Filters
	Specifying Visual Interactions
	What-If Slicers
	Custom Visuals As Slicers
		Timeline Slicer
		Timebrush Slicer
		Chiclet Slicer
		Text Filter
	Choosing the Correct Approach to Interactive Data Selection
	Conclusion
Chapter 23: Enhancing Dashboards
	Formatting Ribbons
		The View Ribbon
		The Insert Ribbon
		The Format Ribbon
	Formatting the Page
	Aligning and Distributing Visuals
		Aligning Visuals
		Distributing Visuals
		Aligning to the Grid
			Displaying the Grid
		Specifying the Exact Position of a Visual
	Adding Text Boxes to Annotate a Report
		Adding a Text Box
		Moving Text Boxes
		Formatting a Text Box
		Adding a Hyperlink
		Removing a Hyperlink
		Deleting Text Boxes
	Modifying the Page Background Color
	Images
		Image Sources
		Adding an Image
		Removing an Image
		Resizing Images
		Formatting Images
		Background Images
			Adding a Background Image
		Some Uses for Independent Images
	Adding Shapes
		Formatting Shapes
			Lines and Fill Color in Shapes
			Shape Rotation
		Removing Shapes
		Standardizing Shapes
	Organizing Visuals on the Page
	Layering Visuals
	Grouping Visuals
	Phone Layout
	Report Themes
	Conclusion
Chapter 24: Advanced Dashboarding Techniques
	Drillthrough
		The Source Page
		The Destination Page
		Applying Drillthrough
		Multiple Levels of Drillthrough
	Drillthrough Across Power BI Files
	Popup Tooltips
	Bookmarks
		Updating a Bookmark
		Defining the Extent of a Bookmark’s Application
		Arranging Bookmarks
	Slideshows Using Bookmarks
	Buttons
		Button Options
		Changing the Button Type
		Button Actions
	Formatting Buttons
		Using Images and Shapes as Buttons
	Applying Slicers Across Multiple Pages
		Setting Slicer Visibility Across Multiple Pages
	Templates
	Conclusion
Appendix A: Sample Data
	Sample Data
		Downloading the Sample Data
		Images
Index