Microsoft Excel: Business Intelligence w/ Power Query & DAX

Before getting started with the course, it's important to make sure that you are using a version of Excel that is compatible with Power Pivot. Check the Microsoft Office Support website to make sure that you are using a proper version.

In this lecture I'll introduce our course project, outline the downloadable files that we'll be working with, and demonstrate exactly how to access them from the course dashboard.

To wrap up the intro section, I'll explain exactly what to expect (and what NOT to expect) from the course.

This lecture is all about outlining the "Power Excel" landscape. I'll show you exactly how these new data modeling and business intelligence tools will fit into your workflow, from Power Query to Power Pivot and DAX.

In this lecture I'll explain why Power Query and Power Pivot are so awesome that they've been called the "best thing to happen to Excel in 20 years" from industry experts. We'll talk about the ability to load hundreds of millions of rows, build data models to blend data across sources, automate your data loading and ETL process, and create powerful calculated fields using data analysis expressions (DAX).

This lecture will help you understand when and why to use Excel Power Query and Power Pivot (i.e. when you are dealing with very large data sets, need to blend data across multiple tables, etc.)

In this lecture we'll introduce Power Query, which we'll connect and transform data from raw sources, edit it using the Query Editor, and load it straight into Excel.

The Query Editor is your command center when it comes to loading and transforming raw data in Excel using Power Query. In this lecture we'll take a tour of the tools that we'll use to transform and shape our data.

In this lecture we'll explore Excel's data loading options from Power Query, and talk about the difference between loading tables and only generating connections.

This lecture will cover some of the most common data transformation tools in Excel's Query Editor, such as adding or removing columns or rows, changing data types, etc.

In this lecture we'll cover Power Query tools designed specifically to work with text in Excel, such as merging or splitting columns, extracting characters, calculating string lengths, etc.

In this lecture we'll review Power Query tools designed specifically to work with numbers or data fields in Excel, such as returning aggregated values like sums or averages, creating new calculated columns, rounding numbers, etc.

In this lecture we'll cover Power Query tools specifically designed to work with date and time fields in Excel, like calculating months, weeks, weekdays, quarters, etc.

In this lecture I'll quickly demonstrate how you can use custom M queries in the Excel query editor to build a rolling calendar that will always update with dates through the current day.

This lecture will show you how to create new unique identifiers using index columns in the Excel Query Editor, as well as calculated fields based on custom user-defined conditions.

In this lecture we'll practice using "group by" tools in the Excel Query Editor to aggregate or roll up raw data to new levels of granularity.

This lecture demystifies the concept of "pivoting" or "unpivoting" a data table. I'll show you exactly what these tools do, and demonstrate with a sample table in the Excel Query Editor.

In this lecture, I'll show you how you can access, edit, and delete existing Excel workbook queries.

This lecture demonstrates how to merge Excel Power Query queries together to pull fields from one table into another based on common fields or "keys".

In this lecture, I'll show you how to append or "stack" data from multiple tables that share the same column structure and data types, using Excel's Power Query tools.

In this lecture I'll demonstrate how to connect to an entire folder and automatically append all of the files within in (including new files as they are added), and load to Excel.

In this lecture we'll review my personal favorite best practices for using Excel Power Query.

In this lecture I'll introduce Excel's "Data Model", which is where large data files can be compressed and modeled together using table relationships.

This lecture outlines the two views within the Excel data model: Data view and Diagram view. Data view allows you to access the data within tables (organized as tabs), and diagram view allows you to create and view your table relationships and overall data model.

This lecture covers one of the most important topics in the course: database normalization. Normalization is all about structuring tables to create efficient and effective data models in Excel.

In this lecture we'll compare and contrast the two primary types of tables in an Excel data model: data (or "fact") tables and lookup (or "dimension") tables.

In this lecture I'll explain the difference between manually merging fields from multiple tables and creating relationships to connect them using Excel's data model (which is much more efficient!)

In this lecture we'll use the Excel data model diagram view to create our first table relationships.

In this lecture I'll demonstrate how to modify or edit existing table relationships in the Excel data model diagram view, using several different methods.

In this lecture I'll demonstrate how to create multiple relationships against the same key, and how to determine which relationship is active vs. inactive in an Excel data model.

This lecture explores the concept of cardinality, and demonstrates the differences between 1-to-1, 1-to-many, and many-to-many relationships. I'll also show you exactly why 1-to-many relationships are critical when it comes to building normalized data models in Excel.

In this lecture I'll demonstrate exactly how to build an Excel model containing multiple data tables. Rather than connect those tables together, we'll connect them indirectly via relationships to shared lookup tables.

This lecture demonstrates the importance of filter direction within Excel's data model, and explains the concept of filter context flowing "downstream" to related tables.

In this lecture, we'll talk about when, why, and how to hide fields from Excel client tools such as PivotTables, using a number of different methods.

In this lecture we'll practice creating hierarchies within Excel's data model, which are new fields containing groups or sets of related fields (such as country, state, and city).

In this lecture I'll outline my personal favorite Excel data model best practices.

In this lecture I'll introduce Excel Power Pivot and outline some of the key benefits.

In this lecture I'll explore the similarities and differences between regular Excel PivotTables and "Power" PivotTables, which connect to entire data models.

In this lecture I'll introduce the formula language that enables you to create powerful calculated fields from a data model in Excel: Data Analysis Expressions (aka "DAX").

In this lecture I'll introduce the first method of using DAX to create new calculated fields: calculated columns. I'll showcase some "good" and "bad" examples, and demonstrate how they can be created within the Excel data model window.

In this lecture I'll introduce the second method of using DAX to create new calculated fields: measures. I'll explain how they can be used and why they are so powerful, especially when compared to traditional Excel PivotTable calculated fields. 

In this lecture I'll briefly introduce implicit measures, which are measures that are automatically created by Excel when you drag a field in the PivotTable field list.

In this lecture I'll demonstrate how to use the data model's "AutoSum" feature to quickly create basic measures using common functions in Excel (SUM, COUNT, AVERAGE, etc).

In this lecture I'll introduce the most powerful means of creating measures: building calculated explicit measures using the Power Pivot dialog box in Excel. This is where you can use complex combinations of DAX functions to create incredibly powerful and flexible measures.

This lecture introduces the concept of filter context, which is the set of filters passed by the Excel PivotTable layout. Understanding filter context is critical to understanding how measures are calculated.

In this lecture, I'll guide you through the exact steps that Excel takes behind the scenes to calculate each cell containing a measure. This demonstration will be critical to troubleshooting calculation errors and understanding precisely how measures work.

In this lecture I'll recap the similarities and differences between calculated columns and DAX measures, and explain when to use one approach vs the other in Excel.

In this lecture I'll outline some of my personal favorite Excel Power Pivot and DAX best practices.

In this lecture I'll walk through DAX formula syntax and outline the most common types of operators.

In this lecture I'll outline some of the most common DAX categories (Math & Stats, Logical, Text, Filter, and Date & Time), and compare them against traditional Excel formulas.

In this lecture I'll introduce and demonstrate several common math and statistics functions in DAX, such as SUM, DIVIDE, MAX, MIN, and AVERAGE.

In this lecture I'll demonstrate how to use a variety of COUNT functions in DAX, including COUNT, COUNTA, COUNTROWS, and DISTINCTCOUNT.

In this lecture I'll introduce and demonstrate several common logical functions in DAX, including IF, IFERROR, AND, OR, etc.

In this lecture I'll introduce the SWITCH function in DAX, and demonstrate how it can be combined with TRUE to eliminate the need for nested IF statements to test multiple criteria in Excel.

In this lecture I'll introduce and demonstrate a number of text-specific functions in DAX, including LEN, CONCATENATE, UPPER/LOWER/PROPER, LEFT/MID/RIGHT, SEARCH, and SUBSTITUTE.

In this lecture I'll introduce arguably the most powerful DAX function of all: CALCULATE. I'll explain exactly how this function can be used in Excel, and demonstrate several examples applied to our course project files.

In this lecture I'll demonstrate how to add a FILTER function within CALCULATE to create new filter context in DAX. 

In this lecture we'll revisit the use of FILTER within a CALCULATE function, and illustrate exactly how the FILTER function impacts the way measures are calculated behind the scenes. 

In this lecture I'll explain how to use the ALL function to remove filter context within a PivotTable, and demonstrate how it is commonly used for "% of whole" calculations defined by DAX measures.

In this lecture I'll show you how to use the RELATED function to create new calculated columns that retrieve values from related tables in the Excel data model (just like a VLOOKUP!).

In this lecture I'll introduce you to iterator, or "X" functions, which operate by repeating a calculation across all rows in a table and aggregating the results. In this demo we'll look at the SUMX function specifically, and compare it against the traditional Excel SUMPRODUCT function.

In this lecture we'll take a look at the RANKX iterator function, which allows you to calculate an item's rank based on a given set of conditions or criteria.

In this lecture we'll review the most common date and time DAX functions, including DAY/MONTH/YEAR, HOUR/MINUTE/SECOND, TODAY/NOW, WEEKDAY/WEEKNUM, EOMONTH and DATEDIFF.

In this lecture we'll introduce DAX's powerful time intelligence formulas, and demonstrate how to use them to measure performance-to-date, make period-over-period comparisons, and calculate running totals and moving averages.

In this lecture I'll quickly review some of the key considerations when it comes to Excel data model performance and speed, including the use of slicers, iterator functions, and redundant columns.

In this lecture I'll outline some of my personal favorite DAX best practices.