O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Power Query for Power BI and Excel

Power Query for Power BI and Excel

by Chris Webb
Publisher: Apress
Release Date: June 2014
ISBN: 9781430266921
View table of contents
Start reading

Book Description

"

Power Query for Power BI and Excel is a book for people who are tired of copying and pasting data into Excel worksheets. Power Query, part of the Microsoft Power BI suite, is a tool that automates the process of getting data into Excel and will save you hours of dull, repetitive, and error-prone work! Power Query makes it easy to extract data from many different data sources, filter that data, aggregate it, clean it and perform calculations on it, finally loading that data into either your worksheet or directly into the new Excel 2013 Data Model used by Power Pivot. This concise, practical book provides a complete guide to Power Query and how to use it to solve all of your Excel data-loading problems.

Power Query for Power BI and Excel goes well beyond the surface of what Power Query can do. The book goes deep into the underlying M language, showing you how to do amazing things that aren't going to be possible from just the GUI interface that is covered in most other books. You'll have full command of the GUI, and you'll be able to drop into the M language to go beyond what the GUI provides. The depth in this book makes it a must-have item for anyone who is pushing Power BI and Excel to their limits in the pursuit of business intelligence from data analysis.

  • Teaches the basics of using Power Query to load data into Excel
  • Helps you solve common, data-related problems with Power Query
  • Shows how to write your own solutions in the powerful M language
"

Table of Contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. About the Author
  8. About the Technical Reviewer
  9. Acknowledgments
  10. Preface
  11. Chapter 1: Introducing Power Query
    1. Power Query and Power BI
      1. Power BI Components
      2. Power Query and Power BI Licensing
      3. Installing Power Query
    2. Power Query Concepts
    3. Power Query Walkthrough
      1. Creating a Simple Query
      2. Editing an Existing Query
      3. Why Use Power Query?
    4. Summary
  12. Chapter 2: Power Query Data Sources
    1. Querying Relational Databases
      1. Connecting to SQL Server
      2. Navigating through a Database in the Query Editor
      3. Connecting to Other Databases
    2. Extracting Data from Files
      1. Working with CSV Files
      2. Working with Text Files
      3. Working with XML Files
      4. Working with JSON Files
      5. Working with Excel Files
    3. Working with Folders and Multiple Files
      1. Working with Data from the Windows File System
      2. Combining Data from Multiple Text Files
    4. Working with Data from the Current Excel Workbook
    5. Working with Data from the Web
      1. Scraping Data from Web Pages
      2. Calling a Web Service
      3. Finding Data Using Power Query Online Search
    6. Using Other Queries as Data Sources
      1. Referencing Entire Queries
      2. Duplicating Queries
      3. Using Individual Values from Queries
    7. OData Data Sources
      1. Working with Generic OData Web Services
      2. Working with Data from Excel Workbooks Stored in SharePoint
      3. Working with Data from SharePoint Lists
      4. Working with Data from the Windows Azure Marketplace
      5. Working with Data from Windows Azure Blob Storage and Table Storage
    8. Working with Data from HDFS and HDInsight
    9. Working with Active Directory Data
    10. Working with Data from Microsoft Exchange
    11. Working with Data from Facebook
    12. Working with Data from SAP BusinessObjects
    13. Reusing Recent Data Sources
    14. Managing Credentials
    15. The Importance of Locale
      1. Setting a Default Locale
      2. CSV Files and Code Pages
    16. Summary
  13. Chapter 3: Transforming Data with Power Query
    1. Queries and Steps
    2. Working with Columns
      1. Naming Columns
      2. Moving Columns
      3. Removing Columns
      4. Splitting Columns
      5. Merging Columns
      6. Setting the Data Type of a Column
      7. Changing Data Types and Locales
    3. Filtering Rows
      1. Filtering Rows Using Auto-Filter
      2. Filtering Rows Using Number, Text, and Date Filters
      3. Filtering Rows by Range
      4. Removing Duplicate Values
      5. Filtering Out Rows with Errors
    4. Sorting a Table
    5. Changing Values in a Table
      1. Replacing Values with Other Values
      2. Text Transforms
      3. Number Transforms
      4. Date/Time/Duration Transforms
      5. Filling Up and Down to Replace Missing Values
    6. Aggregating Values
    7. Unpivoting Columns to Rows
    8. Transposing a Table
    9. Creating Custom Columns
      1. Built-in Custom Columns
      2. Custom Columns with M Calculations
    10. Summary
  14. Chapter 4: Data Destinations
    1. Choosing a Destination for Your Data
    2. Loading Data to the Worksheet
      1. Using the Default Excel Table Output
      2. Loading Data to Your Own Excel Tables
    3. Loading Data to the Excel Data Model
      1. Viewing Tables in the Excel Data Model
      2. Advantages of Using the Excel Data Model
      3. Power Query and Table Relationships
      4. Breaking Changes
    4. Refreshing Queries
      1. Refreshing Queries Manually
      2. Automating Data Refresh
    5. Summary
  15. Chapter 5: Introduction to M
    1. Writing M in the Query Editor
      1. The Formula Bar
      2. The Advanced Editor Window
      3. Creating a Blank Query
    2. M Language Concepts
      1. Expressions, Values, and Let statements
      2. Writing M
    3. Lists, Records, and Tables
      1. Lists
      2. Records
      3. Tables
      4. Selections and Projections
    4. Functions
      1. Defining Functions Inside a Query
      2. each Expressions
      3. Queries As Functions
      4. let Expressions in Function Definitions
      5. Recursive Functions
      6. Functions Imported from Data Sources
    5. Working with Web Services
    6. Query Folding
      1. Monitoring Query Folding in SQL Server
      2. Preventing Query Folding in Code
      3. Other Operations That May Prevent Query Folding
    7. Summary
  16. Chapter 6: Working with Multiple Queries
    1. Using One Query as a Source for Another
      1. Referencing Queries in Code
      2. Creating Parameterized Queries
    2. Working with Data from Different, External Data Sources
      1. Data Privacy Settings
      2. The Formula Firewall
      3. The Fast Combine Option
    3. Appending Data from One Query onto Another
      1. Appending Queries in the User Interface
      2. Appending in M
    4. Merging Two Queries
      1. Merging Queries in the User Interface
      2. Merging in M
    5. Summary
  17. Chapter 7: Power Query and Power BI for Office 365
    1. Sharing and Using Shared Queries in Power Query
      1. Sharing queries
      2. Consuming Shared Queries
      3. Updating Queries That Have Been Shared
    2. Managing Shared Queries in the Power BI Data Catalog
      1. Finding Your My Power BI page
      2. Viewing Shared Queries
      3. Viewing Usage Analytics
      4. Managing data sources
    3. The Data Steward
      1. Who Is the Data Steward?
      2. Certifying Queries
      3. Which Queries Should Be Shared?
      4. Sharing Functions
    4. Power BI for Office 365 Data Refresh
      1. Supported Data Sources
      2. Enabling Scheduled Refresh
    5. Summary
  18. Chapter 8: Power Query Recipes
    1. Calculations
      1. Percentage Share of Grand Total
      2. Percentage Growth in Sales from the Previous Day
      3. Tied Ranks
      4. Counting the Number of Distinct Customers
    2. Table Transformations
      1. Converting a Single-Column Table to a Multiple-Column Table
      2. Finding New, Lost, and Returning Customers
      3. Generating a Date Table
      4. How Long Was a Stock Price Above a Given Value?
    3. Working with Data from the Web
      1. Web-Scraping Weather Forecast Data
      2. Finding the Driving Distance Between Two Locations Using the Bing Maps Route Web Service
    4. Summary
  19. Index