• Post category:StudyBullet-3
  • Reading time:11 mins read


Use Power Query, Power Pivot, DAX, & Excel’s data visualization tools to build powerful Business Intelligence solutions

What you will learn

Use Power Query to pull external datasets into Excel, and transform them into whatever shape is needed for your analysis

Master the art and science of Data Modeling in Excel, connecting your datasets from Power Query so they can be analyzed together

Build “Power” Pivot Tables to rapidly extract insights from your Data Model, whether your data tables have 10 rows or 10 million

Become proficient in the DAX formula language, to inject more sophisticated calculations and KPIs into your Pivot Tables than you even thought possible

Polish your calculations into professional presentations with data visualization tools like Pivot Charts and conditional formatting

Leverage Slicers and Timelines to engineer dynamic dashboards that maximize the amount of information you can present on a single screen

Retain – AND build on – everything you’ve learned, by tackling dozens of hands-on exercises that cover every concept in the course

Description

The first thing you need to know about this course, is that this is NOT your granddad’s Excel.

Instead of the same old spreadsheet stuff, we’ll do a deep dive on a truly revolutionary set of tools that empower you to do industrial-strength Business Intelligence: the art and science of transforming data – usually massive amounts of it – into meaningful, actionable insights.

In the past, doing “real” BI meant using expensive enterprise software that only a select few people would have access to anyway. But with what you’ll learn in this course, you’ll be building robust Business Intelligence solutions in no time, using nothing more than an Excel spreadsheet on your desktop.

  • First up, we’ll dive into Power Query, a feature-packed yet easy to use tool for extracting, transforming, and loading (ETL for short) data from just about any source you can imagine into Excel. From text files to databases, wherever the data you want to analyze might be stored, you can use Power Query to pull it into Excel, and then transform it however you need to support your analysis.
  • Next, there’s an optional section on Pivot Tables, just in case you’re not already familiar with them. While Pivot Tables may not be a revolutionary new feature in and of themselves, they nonetheless play a crucial role in the Excel Business Intelligence landscape.
  • We’ll then learn to turn those Pivot Tables into “Power” Pivot Tables, by connecting them to the Excel Data Model…which is basically a relational database that lives right inside your Excel spreadsheet! This will allow us to mash up and analyze multiple datasets in a single Pivot Table…no VLOOKUPS required!And remember how Excel usually starts to sputter out once you try to play around with more than a couple hundred thousand rows of data? No more! Power Pivot let’s you work with up to hundreds of millions of records in a single Excel file.
  • Then, so we can get the absolute most out of our Data Model (and into our Power Pivot Tables), we’ll learn DAX, an incredibly powerful formula language for creating complex calculations that you can drop right into your Pivots.Since the basics of DAX syntax are easy to pick up if you’re already familiar with Excel formulas, we’ll venture into advanced topics like Time Intelligence, Iterator Functions, and Variables; and with exercises after almost every video, you’ll have plenty of opportunities to master what you’ve learned, right after you learn it.
  • Of course, all that number crunching doesn’t help much if we can’t present those numbers in an intuitive, easily digestible way. That’s why I close the course out with a series of powerful visualization techniques – from conditional formatting to Pivot Charts – that will transform your calculations into insights that can be used to make real-world decisions.We’ll even explore techniques for building dynamic dashboards in Excel, using Slicers and Timelines to not only filter our charts, but actually change the metrics we display in them. And all supported by the Data Model’s capacity for juggling hundreds of millions of rows of data in a single spreadsheet.

And in each section, I use hands-on demos, practical examples, and intuitive, common-sense explanations to teach you these concepts in a way that will help you see the connection between your new skills and the problems you’re trying to solve on the job.


Get Instant Notification of New Courses on our Telegram channel.


But just as importantly, I’ve packed the course with TONS of exercises – ranging from straightforward to challenging – that will help you retain, and even build on, what you’ve learned.

So if you want to master these game-changing tools and build professional-grade Business Intelligence solutions right on your desktop, all you need is Microsoft Excel – and this course – to do it. I look forward to seeing you there!

English
language

Content

Introduction
Introduction
Power Query for Extract, Transform, and Load (ETL) operations
DOWNLOAD: Resources For This Section
Introducing Power Query
Applying Basic Transformations
Editing Transformations
Numerical Transformations
Text Transformations
Date Transformations – Building a Calendar Table
Conditional Transformations
IMPORTANT – If You’re Following Along With The Examples
Merging Datasets
Appending Datasets
DOWNLOAD: Exercise Files For This Section
EXERCISES: Extracting, Transforming, and Loading With Power Query
OPTIONAL: Pivot Tables 101
Intro to Pivot Tables
Adding Layers to Pivot Tables
Pivot Table Formatting and Layout
Filtering Pivot Tables
Slicers
Pivot Table Math
Pivot Tables – Exercises
The Excel Data Model
DOWNLOAD: Resources For This Section
Introducing Excel’s Data Model
Putting the Data Model to Work
Relationships
Relationships in the Excel Data Model
Power Pivot Tables
Calendar Tables
Power Pivot Pitfalls
Multiple Data Tables
DOWNLOAD: Exercise Files For This Section
EXERCISES: Data Modeling in Excel
Analyzing the Data Model With DAX
Calculated Columns With DAX
EXERCISES: Calculated Columns With DAX
The IF Function in DAX
EXERCISES: The IF Function in DAX
The SWITCH Function
EXERCISES: The SWITCH Function
The RELATED Function
EXERCISES: The RELATED Function
Introducing Measures
EXERCISES: Introducing Measures
Using DAX Functions in Measures
EXERCISES: Using DAX Functions in Measures
The CALCULATE Function
EXERCISES: The CALCULATE Function
The ALL Function
EXERCISES: The ALL Function
Advanced DAX
Introducing Time Intelligence Functions
The ISBLANK Function
EXERCISES: The ISBLANK Function
The DATEADD Function
EXERCISES: The DATEADD Function
The DATESINPERIOD Function
EXERCISES: The DATESINPERIOD Function
Variables in DAX
EXERCISES: Variables in DAX
The “X” Functions
EXERCISES: The “X” Functions
The RANKX Function
EXERCISES: The RANKX Function
The TOPN Function
EXERCISES: The TOPN Function
Data Visualization Techniques
Pivot Table Formatting Tips
Data Bars, Color Scales, and Icon Sets
Custom Conditional Formats
Introducing Pivot Charts
Column Charts
Line Charts, Area Charts, and Timelines
Combo Charts
Changing Measures With Slicers
Using Pivot Tables as Formulas
EXERCISES: Data Visualization Techniques