Introducing: Get and Transform / PowerQuery

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.

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.

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.

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, at 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 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.

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.

See how specifying data types impacts the different data components.

This lesson dives into number fields and the available functions for calculating summary statistics and applying arithmetic calculations. 

Use the attached practice set to answer the questions in the follow-up quiz.

A quick (audio-less) walk through to the solutions for the Number Data Types Quiz.

An audioless walkthrough where each answer for the quiz is derived from the practice set.

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.

This dataset is provided for use in answering the questions in the following quiz.

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 attached practice set provides a set of rules for "G&T Blackjack". You should use the rules to score the hands displayed on the "basedata2" tab. Create a Get and Transform query to score each of the hands. You can confirm your results by taking the follow-up quiz that will ask you for the results of each hand and the resulting score.

Due to the challenge involved, the solution set has also been provided for you to compare your approach against. At this point in the course, there are multiple reasonable ways to build the query so the proper results are the goal.

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.

A bond future value calculation example for learning how to build more complex custom computations by first building them out step-wise using the individual commands.

A brief overview of the upcoming section.

A start-to-finish demonstration for how to complete the exercises presented within this section.

A final wrap of what was covered by the course with a brief overview of what was left uncovered.