Get a month of TabletWise Pro for free! Click here to redeem 
TabletWise.com
 

Introduction to PowerQuery

Learn everything in Microsoft Excel faster, easier, and more efficiently with PowerQuery in your toolbox.

Introduction to PowerQuery

Learn everything in Microsoft Excel faster, easier, and more efficiently with PowerQuery in your toolbox.
13
Views
2:55:14
Share the link to this class
Copied
A brief overview of the course content.
This section will focus on making data accessible to the PowerQuery editor from some common data sources. PowerQuery supports many different data connectors allowing for a huge variety of data mixing.
You'll learn how to use Get and Transform "locally" with data in your current Excel file via Excel Data Tables. Note: Excel will tell you that you have "external data connections" whenever you use Get and Transform to connect to data tables within your current file and will trigger external data request warnings based upon your trust center settings.
You'll learn how to connect a Get and Transform query to other Excel workbooks, comma separated value (CSV) files, Access databases, and SQL server databases.
The zip file contains five files. The starting point for the exercise [1-3-exercise-01.xlsx] An Access 2007+ Database [Exercise1_Accdb_DataSource.accdb] An Access 2000-2003 Database [Exercise1_Accdb_DataSource_2003.mdb] A CSV Data Source [Exercise1_CSV_DataSource.csv] An XLSX Data Source [Exercise1_XLSX_DataSource.xlsx]
A video walkthrough for completing the exercise. Due to the nature of the data links to external files, I will not be providing a static solution file for this course as it would not work without the exact correct folder set-up.
Merge and append datasets together. Aggregate data across fields. Expand, contract, pivot, and unpivot data columns.
How to rename, reorganize, and perform other basic data cleanup options such as removing empty data rows or re-ordering columns.
This lesson covers how to append multiple data sources together, sort of like copying datasets on-top of each other. Refer to the 2-3-practice-file-base.xlsx for the practice set associated with this video.
One of the most frequently used formulas in Excel is the VLOOKUP formula. Next on the list is likely the INDEX/MATCH combination for doing the same thing. These functions are great for singular or one-off calculations; however, they frequently get used for large sets of data. In this lesson, we'll look at how to replace many lookup calculations with a simpler and stronger relationship match through the Get & Transform interface.  Refer to 2-4-practice-file-base.xlsx for the practice set associated with this lesson.
Image contained in ZIP file
This lesson walks through the "Group By" command for calculating the sum, counting values, creating averages, and other aggregate calculations. In more recent versions of Excel, the Group By wizard displayed is accessible by selecting the "Advanced" option once the wizard loads. The file used in the video is available for download and reference.
The file used in the Aggregation Commands video for reference.
Frequently, I find a need to construct new reports from a variety of different reports rather than raw data. With the way that reports get laid out in a tabular format, merging and linking data can be challenging and then, in the end, you want your own tabular format for display purposes. This lesson will cover how to pivot and unpivot data to handle that within Get & Transform quickly and easily.  Refer to 2-5-practice-file-base.xlsx for the practice set associated with this lesson.
This file is the starting point for the exercise to complete this chapter. Solution and walkthrough also provided.
This video demonstrates my solution to the exercise. Even if you do not attempt the exercise, I would recommend watching the video to see how the components covered in this lesson can be used together with some thought to build and manipulate reports.
Learn about the different data types in Get and Transform, some details about them, and how to change and interact with them.
This chapter will introduce the variety of data types handled by Get & Transform and discuss how to identify and define a field's contents. These definitions will modify how Get & Transform treats the information within a field and how the fields interact. The Get & Transform interface has a wide variety of functions available for the different data types which we'll discuss how to use and what they do as part of this chapter. This first lesson introduces the concepts of the data types and how to identify and manage them.
This lesson dives into number fields and the available functions for calculating summary statistics and applying arithmetic calculations.
How to create customized column calculations using conditional (if-then-else) statements, arithmetic operations, and built-in functions.
Learn how to add new columns to perform different actions.
This lesson will walk you through using the conditional column wizard to construct basic if-then-else calculations within the query editor.
Microsoft has added Intellitext support to the PowerQuery editor, making custom formula writing far easier than ever before. This course refers to advanced topics as well; however, the Intellitext applies to the Add Custom Column formula box in the following lesson.
This video will introduce you to the basics of creating more complex if statements and incorporating mathematical commands into the if statements for further flexibility.
The prior lesson used custom columns to expand on conditional statements and incorporate basic calculations in order to do more with fewer steps. This lesson will expand on the custom column formulas to introduce additional functions, some easy ways to learn about the functions, and some basics on how to use the Microsoft M Reference Library.
This section consists of one nothing-to-report scenario broken into three distinct steps to align somewhat with the course sections. Each section has a PowerPoint presentation for you to read through to understand the considerations within that step of the process, with each step building upon your own prior work. In case you have trouble at any point in the process, each step will contain the solution to the previous step should you need assistance or guidance. The concluding lesson within this chapter will be my demonstration of how to complete these tasks from start to finish. Data Cleansing and Preparation This portion of the scenario will ask you to link to and prepare your data for use in Get and Transform. The concepts are straight forward and should take you no more than 5 to 10 minutes upon completion of the PowerPoint. You'll need to clean the data up to make it accessible and usable with the target dataset. Merging, Linking, and Grouping Data In this stage, you will expand on the data cleansing aspect from a single day's worth of data to applying it across multiple days worth of data. These datasets will need to be linked to each other as well as to some supporting datasets to be provided. Your task will be to create a single unified dataset from which the analysts can evaluate the profitability of the business to-date. You'll have another PowerPoint to provide you with additional details and instructions. Producing a Report In the third and final scenario, you'll step into the analyst's shoes. You will need to build a simple report for your manager. A template will be provided with the dropdown field and defined as a query for you to use. Your team (you) has already organized the data into a usable format via the prior parts of this exercise. Your report should filter out incomplete jobs and show results only for the specified classification. The report should identify the Total Profitability, the Profit as a Percentage of Income, and the average profit per job. All of these details will be provided with the associated PowerPoint, as well as other useful information. The Solution This section will close out with a video demonstration of my start-to-finish solution for each of the three steps.
The PowerPoint with the details and instructions for the first section.
A data file for you to import and clean-up.
The PowerPoint with the details and instructions for the second section.
20170110.csv, 20170111.csv, 20170112.csv -- Additional data files for use in this section. Job Listing Table.xlsx, Material Requirements Table.xlsx, Supporting Data Tables.xlsx -- Additional data sources for use in this section. 5-1-Solution.xlsx -- My solution to the prior step.
The PowerPoint with the details and instructions for the third and final section.
5-2-Solution.xlsx - My solution to the prior step of this process to assist should you encounter any hurdles with the prior section. BaseTemplate.xlsx - The starting point for building out your report with dropdown set-up and all.
A start-to-finish demonstration for how to complete the exercises presented within the PowerPoints.
A final wrap of what was covered by the course with a brief overview of what was left uncovered.

Incorporate PowerQuery into your toolset to revolutionize how you use Excel.

Microsoft's PowerQuery (Get and Transform in Excel 2016) offers a wide variety of tools ideal for accessing local and external data sets, cleaning and re-framing information into usable structures, working across multiple data tables to perform lookups and combinations, and building informational and powerful reports.

This course is for anyone new to PowerQuery and applicable to any level of Excel user. You will become a capable query creator and editor with no coding or Excel formula knowledge required.

Access the world beyond Excel's boundaries.

Ever copy and paste data from one workbook into another?

What about from a webpage?

Have you ever asked your database administrator for a data report to work with, and gone through a dozen iterations (and multiple weeks) before having something you could adequately use in Excel?

Ever abandoned a project because automating data from an external source seemed too daunting or might have required VBA?

PowerQuery can solve all of these issues. You will build data requests against these alternative sources quickly and easily while incorporating data standardization and cleaning components into that same request.

Replace many tedious and error-prone Excel formula-based approaches.

This course will teach you how to perform effective data analysis that takes a fraction of the time to put together. Replace your vast spreadsheets packed with VLOOKUPs, SUMIFS, SUM, COUNTIFS, and more with PowerQueries that can be created with your mouse via clean and incredibly easy to use interfaces.

These Excel functions provide some function of merging, linking, and aggregating. The PowerQuery tool includes mechanisms for performing these operations that make these functions seem quaint and outdated. Why write three columns of SUMIFS if you can get the same results with a single GROUP BY command without needing to write any formulas at all?

A brief glimpse of PowerQuery's usefulness and capabilities.

This course is designed for all Excel users since PowerQuery is designed for all users - not just power users capable of building incredible Excel worksheets with functions, VBA, and mystical trickery. As such, the course focuses almost exclusively on using the PowerQuery ribbon to perform every action. We do not dive into the Advanced Editor to interact with the M Language at all.

In the last few lessons of the course, you will be introduced to the M Language through the custom column creation tool and introduced to the functions section of Microsoft's Power Query M Language Reference to assist you in building those custom calculations. These calculations are a great place to learn about how to interpret and use the functions in preparation for more advanced applications.

Expand your horizons

This course will prepare you for more advanced PowerQuery courses and can serve as a good starting point for launching into PowerPivot and certain Power BI content. By the end of this course, you should feel comfortable in the PowerQuery Editor. This means that you'll be meaningfully capable of building data acquisition within Power BI by the end of this course, as the Excel PowerQuery Editor is simply the Power BI Query Editor minus a few advanced features.

Requirements

  • You should have Microsoft Excel 2016 installed on your Windows [Mac does not support PowerQuery].
  • You should have Microsoft Excel 2010/2013 with PowerQuery Add-In [Free Microsoft Download] installed on your Windows.
A brief overview of the course content.
This section will focus on making data accessible to the PowerQuery editor from some common data sources. PowerQuery supports many different data connectors allowing for a huge variety of data mixing.
You'll learn how to use Get and Transform "locally" with data in your current Excel file via Excel Data Tables. Note: Excel will tell you that you have "external data connections" whenever you use Get and Transform to connect to data tables within your current file and will trigger external data request warnings based upon your trust center settings.
You'll learn how to connect a Get and Transform query to other Excel workbooks, comma separated value (CSV) files, Access databases, and SQL server databases.
The zip file contains five files. The starting point for the exercise [1-3-exercise-01.xlsx] An Access 2007+ Database [Exercise1_Accdb_DataSource.accdb] An Access 2000-2003 Database [Exercise1_Accdb_DataSource_2003.mdb] A CSV Data Source [Exercise1_CSV_DataSource.csv] An XLSX Data Source [Exercise1_XLSX_DataSource.xlsx]
A video walkthrough for completing the exercise. Due to the nature of the data links to external files, I will not be providing a static solution file for this course as it would not work without the exact correct folder set-up.
Merge and append datasets together. Aggregate data across fields. Expand, contract, pivot, and unpivot data columns.
How to rename, reorganize, and perform other basic data cleanup options such as removing empty data rows or re-ordering columns.
This lesson covers how to append multiple data sources together, sort of like copying datasets on-top of each other. Refer to the 2-3-practice-file-base.xlsx for the practice set associated with this video.
One of the most frequently used formulas in Excel is the VLOOKUP formula. Next on the list is likely the INDEX/MATCH combination for doing the same thing. These functions are great for singular or one-off calculations; however, they frequently get used for large sets of data. In this lesson, we'll look at how to replace many lookup calculations with a simpler and stronger relationship match through the Get & Transform interface.  Refer to 2-4-practice-file-base.xlsx for the practice set associated with this lesson.
Image contained in ZIP file
This lesson walks through the "Group By" command for calculating the sum, counting values, creating averages, and other aggregate calculations. In more recent versions of Excel, the Group By wizard displayed is accessible by selecting the "Advanced" option once the wizard loads. The file used in the video is available for download and reference.
The file used in the Aggregation Commands video for reference.
Frequently, I find a need to construct new reports from a variety of different reports rather than raw data. With the way that reports get laid out in a tabular format, merging and linking data can be challenging and then, in the end, you want your own tabular format for display purposes. This lesson will cover how to pivot and unpivot data to handle that within Get & Transform quickly and easily.  Refer to 2-5-practice-file-base.xlsx for the practice set associated with this lesson.
This file is the starting point for the exercise to complete this chapter. Solution and walkthrough also provided.
This video demonstrates my solution to the exercise. Even if you do not attempt the exercise, I would recommend watching the video to see how the components covered in this lesson can be used together with some thought to build and manipulate reports.
Learn about the different data types in Get and Transform, some details about them, and how to change and interact with them.
This chapter will introduce the variety of data types handled by Get & Transform and discuss how to identify and define a field's contents. These definitions will modify how Get & Transform treats the information within a field and how the fields interact. The Get & Transform interface has a wide variety of functions available for the different data types which we'll discuss how to use and what they do as part of this chapter. This first lesson introduces the concepts of the data types and how to identify and manage them.
This lesson dives into number fields and the available functions for calculating summary statistics and applying arithmetic calculations.
How to create customized column calculations using conditional (if-then-else) statements, arithmetic operations, and built-in functions.
Learn how to add new columns to perform different actions.
This lesson will walk you through using the conditional column wizard to construct basic if-then-else calculations within the query editor.
Microsoft has added Intellitext support to the PowerQuery editor, making custom formula writing far easier than ever before. This course refers to advanced topics as well; however, the Intellitext applies to the Add Custom Column formula box in the following lesson.
This video will introduce you to the basics of creating more complex if statements and incorporating mathematical commands into the if statements for further flexibility.
The prior lesson used custom columns to expand on conditional statements and incorporate basic calculations in order to do more with fewer steps. This lesson will expand on the custom column formulas to introduce additional functions, some easy ways to learn about the functions, and some basics on how to use the Microsoft M Reference Library.
This section consists of one nothing-to-report scenario broken into three distinct steps to align somewhat with the course sections. Each section has a PowerPoint presentation for you to read through to understand the considerations within that step of the process, with each step building upon your own prior work. In case you have trouble at any point in the process, each step will contain the solution to the previous step should you need assistance or guidance. The concluding lesson within this chapter will be my demonstration of how to complete these tasks from start to finish. Data Cleansing and Preparation This portion of the scenario will ask you to link to and prepare your data for use in Get and Transform. The concepts are straight forward and should take you no more than 5 to 10 minutes upon completion of the PowerPoint. You'll need to clean the data up to make it accessible and usable with the target dataset. Merging, Linking, and Grouping Data In this stage, you will expand on the data cleansing aspect from a single day's worth of data to applying it across multiple days worth of data. These datasets will need to be linked to each other as well as to some supporting datasets to be provided. Your task will be to create a single unified dataset from which the analysts can evaluate the profitability of the business to-date. You'll have another PowerPoint to provide you with additional details and instructions. Producing a Report In the third and final scenario, you'll step into the analyst's shoes. You will need to build a simple report for your manager. A template will be provided with the dropdown field and defined as a query for you to use. Your team (you) has already organized the data into a usable format via the prior parts of this exercise. Your report should filter out incomplete jobs and show results only for the specified classification. The report should identify the Total Profitability, the Profit as a Percentage of Income, and the average profit per job. All of these details will be provided with the associated PowerPoint, as well as other useful information. The Solution This section will close out with a video demonstration of my start-to-finish solution for each of the three steps.
The PowerPoint with the details and instructions for the first section.
A data file for you to import and clean-up.
The PowerPoint with the details and instructions for the second section.
20170110.csv, 20170111.csv, 20170112.csv -- Additional data files for use in this section. Job Listing Table.xlsx, Material Requirements Table.xlsx, Supporting Data Tables.xlsx -- Additional data sources for use in this section. 5-1-Solution.xlsx -- My solution to the prior step.
The PowerPoint with the details and instructions for the third and final section.
5-2-Solution.xlsx - My solution to the prior step of this process to assist should you encounter any hurdles with the prior section. BaseTemplate.xlsx - The starting point for building out your report with dropdown set-up and all.
A start-to-finish demonstration for how to complete the exercises presented within the PowerPoints.
A final wrap of what was covered by the course with a brief overview of what was left uncovered.

About the instructors

Explainly

Expert authors. Advanced topics. Clear explanations.
Share the instructor profile
Copied

Explainly offers online courses on powerful topics centered around the premise of instruction followed up with implementation through practical use.

Our founders, Philip Trick and Ahad Amdani, are experts in their respective fields and offer courses designed around their expertise with a history of success building online courses for other providers, including many popular Udemy publishers.

Top Software Classes

New Software Classes

All Classes
Free for 30 Days
   The video is currently being processed.
   An error occurred while uploading the video. Please upload another video.
   Please upload the required file.
Quiz: #TITLE#
Questions: #QUESTIONS_COUNT#
Quiz: #TITLE#
Question /#QUESTIONS_COUNT#
Quiz: #TITLE#
Result: You correctly answered out of questions. Result: You correctly answered out of question. Result: You correctly answered out of questions attempted. Result: You correctly answered out of question attempted. Result: You did not attempt any question.
0
Saves
13
Views
This class has not been saved

Sign Up