Helion


Szczegóły ebooka

 
Learn Power Query

Learn Power Query


Power Query is a data connection technology that allows you to connect, combine, and refine data from multiple sources to meet your business analysis requirements. With this Power Query book, you'll be empowered to work with a variety of data sources to create interactive reports and dashboards using Excel and Power BI.

You'll start by learning how to access Power Query across different versions of Excel and install the Power BI engine. After you've explored Power Pivot, you'll see why Excel users find it challenging to clean data in Power Pivot and learn how Power Query can help to tackle the problem. The book will show you how to transform data using the Query Editor and write functions in Power Query. A dedicated section will focus on functions such as IF, Index, and Modulo, and creating parameters to alter query paths in a table. You'll also work with dashboards, get to grips with multi-dimensional reporting, and create automated reports. As you advance, you'll cover the M formula language in Power Query, delve into the basic M syntax, and write the M query language with the help of examples such as loading all library functions offline in Excel and Power BI. Finally, the book will demonstrate the difference between M and DAX and show how results are produced in M.

By the end of this book, you'll be ready to create impressive dashboards and multi-dimensional reports in Power Query and turn data into valuable insights.

  • Learn Power Query
  • Why subscribe?
  • Contributors
  • About the authors
  • Acknowledgements
  • About the reviewer
  • Packt is searching for authors like you
  • Preface
    • Who this book is for
    • What this book covers
    • To get the most out of this book
    • Download the example code files
    • Code in Action
    • Download the color images
    • Conventions used
    • Get in touch
    • Reviews
  • Section 1: Overview of Power Pivot and Power Query
  • Chapter 1: Installation and Setup
    • Technical requirements
    • Introducing Power Pivot
      • Power Pivot Office versions and differences
    • Introduction to Power BI
      • Power BI versions and differences
      • Mac/Apple
    • Introduction to Power Query
      • Features of Power Query
      • Power Query Office versions and differences
      • Installing Power Query in Office 2013 and 2010
      • Activating my Power Query/Pivot again
      • Launching Power Query, Power Pivot, and Power BI
      • Launching Power Query within Excel
      • Accessing Power Query from Power BI
    • Summary
  • Chapter 2: Power Pivot Basics, Inadequacies, and Data Management
    • Technical requirements
    • Creating a pivot table
      • Creating a Power Pivot
      • Creating a table in Excel
      • Adding tables to the data model
      • Creating relationships between tables
    • Power Query to the rescue
      • Creating a calculated column
      • Creating a calculated field
      • Creating a Power Pivot table
    • Shortcomings of Power Pivot
      • Problem 1 selecting multiple items
      • Problem 2 Power Pivot preview
      • Problem 3 calculated fields
      • Problem 4 Microsoft Office versions
    • Summary
  • Chapter 3: Introduction to the Power Query Interface
    • Technical requirements
    • The Power Query window and its elements
      • The main ribbon and tabs
      • The navigation pane or the Queries list
      • Data table preview
      • The Query Settings pane
      • Working with APPLIED STEPS
      • Investigating the View settings
      • Using Advanced Editor
    • Creating a basic power query
    • Discovering the Load To options
      • Changing the default custom load settings
      • Loading queries to the worksheet manually
    • Data profiling tips
      • Column profile
      • Column quality
      • Column distribution
    • Summary
  • Chapter 4: Connecting to Various Data Sources Using Get & Transform
    • Technical requirements
    • A brief introduction to databases
    • Connecting from a table or range
    • Connecting data to the web
    • Connecting from a relational database
      • Connecting through Excels Get & Transform tool
      • Connecting through Power BI
    • Understanding custom connections
      • Connecting from Workbook
      • Connecting from a folder
    • Exploring data source settings
      • From Excel
    • Summary
  • Section 2: Power Query Data Transformations
  • Chapter 5: Transforming Power Query Data
    • Technical requirements
    • Turning data with the unpivot and pivot tools
      • Refreshing data
    • Basic column and row tools
      • Removing columns
      • Removing top or bottom rows
      • Using the index column
      • Creating a conditional column with the ifthenelse statement
      • Filtering data using the And/Or conditions
      • Creating single-criteria filters
      • Creating dynamic multiple-criterion filters
      • Removing duplicate rows
      • Replacing null values
      • Working with the header row
      • Splitting columns
    • Merging and appending tools
      • Merging columns using combine
      • Merging text and values into one column
      • Appending (combining) tables
    • Grouping data
    • Working with extraction tools
      • Extracting an age from a date
      • Extracting columns
      • Using the extract column features
    • Summary
  • Chapter 6: Advanced Power Queries and Functions
    • Technical requirements
    • Writing an IF function in Power Query
    • Creating a parameter table for queries
      • Changing the monthly data source
    • Understanding the Index and Modulo functions
      • Beginning with the modulo function
      • Understanding index functions
    • Appending multiple files
    • Appending multiple tabs
    • Summary
  • Chapter 7: Automating Reports in Power Query
    • Technical requirements
    • Understanding the storage modes and dataset types
      • Viewing the Power BI Desktop Storage mode setting
    • Choosing the Import storage mode setting
      • Looking at where Power BI stores data
      • Investigating whether Microsoft SQL Server Analysis Services is running
    • Understanding the Power BI refresh types
      • Learning how to refresh a OneDrive connection
      • Viewing and performing a OneDrive refresh
      • Setting a scheduled refresh
      • Incremental refresh
      • Automatic page refresh
      • Dataflow refresh
    • Summary
  • Chapter 8: Creating Dashboards with Power Query
    • Technical requirements
    • Creating a basic power pivot and PivotChart
    • Using Power BI to collect and connect data
      • Combining files
    • Using Power BI to add data to a data model
    • Selecting data visualization, a dataset, and an appropriate chart
    • Saving, publishing, and sharing a dashboard
    • Sharing a dashboard
      • Best practices
    • Summary
  • Section 3: Learning M
  • Chapter 9: Working with M
    • Technical requirements
    • The beginnings of M
      • Understanding the M syntax and learning how to write M
    • Using #shared to return library functions
      • Text data types
      • Number data types
      • Lists
      • Records
      • Table data types
      • Searching for relevant data
    • Importing a CSV file using M
    • Summary
  • Chapter 10: Examples of M Usage
    • Technical requirements
    • Merging using the concatenate formula
    • Data type conversions
    • Setting up a SQL server
    • Installing SQL Server Management Studio
    • Using parameters
      • Parameterizing a data source
      • Using parameters in the Data view
    • Summary
  • Chapter 11: Creating a Basic Custom Function
    • Technical requirements
    • Creating a function manually using M
      • Changing the file path of the query to a local path
      • Creating the function manually
      • Testing the parameter function
      • Creating a date/time column using three M functions
    • Summary
  • Chapter 12: Differences Between DAX and M
    • Technical requirements
    • Learning about the DAX and M functionality
    • Constructing DAX syntax
      • Constructing DAX formulas in Excel
      • Using IntelliSense
      • Creating a DAX formula
    • Understanding the DAX formula and storage engine
    • Creating a calculated column
    • Creating calculated measures
      • Using quick measures
      • Formulating a DAX measure from scratch
      • Organizing measures
    • Summary
  • Other Books You May Enjoy
    • Leave a review - let other readers know what you think