Pro Data Mashup for Power BI: Powering Up with Power Query and the M Language to Find, Load, and Transform Data

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 Usable Data
		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
	The Power BI Desktop Screen
	The Get Data Dialog
	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
		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/DirectQuery
	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
	DirectQuery Optimization
	Modifying Connections
	Changing Permissions
	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 Synapse Analytics
	Connecting to SQL Server on an Azure Virtual Machine
	Azure Blob Storage
	Azure Databricks
	Azure Security
	Conclusion
Chapter 6: Loading Data from Other Data Sources
	Other Sources
	Power BI Datasets
	Power BI Dataflows
	R Scripts
		R Options
	Python Scripts to Load Data
		Python Options
	Dataverse
	ODBC 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: Power Query
	Power BI Desktop Queries
		Editing Data After a Data Load
		Transforming Data Before Loading
	Transform or Load?
	Power Query
		The Applied Steps List
		Power Query Ribbons
			The Home Ribbon
			The Transform Ribbon
			The Add Column Ribbon
	The View Ribbon
		The Tools Ribbon
		The Help Ribbon
	Viewing a Full Record
	Power Query Context Menus
	Conclusion
Chapter 8: Structuring Data
	Dataset Shaping
		Renaming Columns
		Reordering Columns
		Removing Columns
		Choosing Columns
		Merging Columns
		Going to a Specific Column
	Removing Records
		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
	Excluding Rows Where a Value Is Missing
	Grouping Records
		Simple Groups
		Complex Groups
	Saving Changes in Power Query
	Exiting Power Query
	Conclusion
Chapter 9: Shaping Data
	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: Data Cleansing
	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
	Conclusion
Chapter 11: Data Transformation
	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
		Splitting Columns by Character Switch
	Merging Columns
	Creating Columns from Examples
	Adding Conditional Columns
	Index Columns
	Conclusion
Chapter 12: Complex Data Structures
	Adding Multiple Files from a Source Folder
	Filtering Source Files in a Folder
	Displaying and Filtering File Attributes
	The List Tools Transform Ribbon
	Parsing XML Data from a Column
	Parsing JSON Data from a Column
		Complex JSON Files
	Complex XML Files
	Python and R Scripts
	Using Python Scripts to Modify Data
	Using R Scripts to Modify Data
	Convert a Column to a List
	Query Folding
	Reusing Data Sources
		Pinning a Data Source
	Copying Data from Power Query
	Conclusion
Chapter 13: 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
			Always Allow Parameters
		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 14: 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
	M or DAX?
	Number Functions
	Date Functions
	Time Functions
	Duration Functions
	M Concepts
	M Data Types
	M Values
		Defining Your Own Variables in M
	Writing M Queries
		M Autocomplete
		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
Appendix A: Sample Data
	Sample Data
		Downloading the Sample Data
INdex