[MUSIC PLAYING]
- Yes, this is the introductory video to MSPTDA class.
That is Microsoft Power Tools for Data Analysis.
Data analysis is when we take raw data
and convert it into useful information.
Here we have some Excel files.
Here's our final report in Power BI Desktop.
Now, in this class, we'll see lots more examples like that,
where we get to use Power Query, Power Pivot, DAX
formulas, Power BI Desktop, and Excel, always
with our goal of creating data models that
will help us generate reports, dashboards,
and useful analytics.
Now, there are some prerequisites
for this class, BI 348.
Business 216, 218, and 210.
These links are below this video.
These are the complete classes for free here at YouTube.
Now, this class is about Microsoft's new Power Tools.
So most of what we're going to be learning in this class
is new and was not presented in those prerequisite classes.
But I will assume that you know the information
from those classes.
What version of Excel are we using?
Office 365.
Why?
Because it automatically updates each month with the latest
features.
What version of Power BI Desktop?
Well, it's a free tool that we will download.
And just like Office 365, it'll be updated each month
with the latest features.
Now, I want to go through, briefly,
the topics that are going to span the entire class.
We're definitely going to have to talk about data analysis
and business intelligence terms throughout the quarter.
Hey, we're definitely going to cover Power Query.
Now, Power Query is the most amazing invention
by Microsoft since the pivot table back in the '90s.
And what does Power Query do?
It imports data from multiple sources.
It doesn't matter if it's from a text file, an Excel
file, or an SQL database.
We can go and get data from anywhere with Power Query.
We can also clean and transform the data
once we get it into Power Query.
And really, this step is the most amazing one.
We will be able to take bad data from any source
and transform it into the right form
to make our reports and dashboards.
Now, the form that we're going to use most often
is actually the next tab here.
I'm going to go over to Data Model.
This is called the star schema.
We'll have one transaction or fact table surrounded
by lookup or dimension tables.
Why this particular form for our data model?
Because Power Pivot and Power BI Desktop
were designed to work most efficiently with this data
setup.
So back to Power Query.
Power Query will allow us to take
data that's not even remotely star schema,
create the data components, and form a star schema data model.
And once we import, clean, and transform,
we'll be able to load the data into Excel as an Excel
spreadsheet, the data model, whether it's
in Power Pivot or Power BI Desktop, or connection only.
Now, the fifth amazing example of how Power Query will
help us is it will replace complicated Excel solutions
with easier Power Query solutions.
A great example is, before Power Query,
if we wanted to dynamically extract
a unique and sorted list, we had to do an incredibly complicated
array formula.
But with Power Query, we can accomplish the same thing
with just a few clicks.
Now, we'll see all five of these important uses for Power Query
multiple times throughout the quarter.
And if we're dealing with data in this class--
importing, cleaning, transforming, and loading--
we're almost certainly going to be using the tool Power Query.
Now, the other amazing thing about Power Query
is, guess what?
It's both here in Excel and over in the free tool, Power BI
Desktop.
Now, if you go over to Data--
and we're using Office 365.
So the Get and Transform group--
that's Power Query.
When we get over to studying Power BI
Desktop, that little ribbon area, external data--
that's Power Query and Power BI Desktop.
So we'll be using Power Query a lot in this class.
Now, one last note.
Most of the time, what's so amazing about Power Query
is we can use the user interface inside of Power Query
to complete all of our tasks.
And Power Query will build the case-sensitive, function-based
M-Code language for us.
But in this class, we definitely will use the interface,
and we will learn how to type and edit
the case-sensitive, function-based M-Code language.
Now, data model-- we talked about that.
Excel-- we're still going to use Excel.
We'll even have to do a few formulas, some standard pivot
tables.
But most of the time, when we're in Excel,
we'll be using Power Query, Power Pivot,
and making data model pivot tables.
Now, speaking of Power Pivot and the data model pivot tables,
let's go over to the sheet Power Pivot.
Yes, one of the amazing new features, like Power Query,
is a new feature called Power Pivot.
Now, actually, Power Pivot is not new.
It first was given to us and called Gemini back in 2009.
And both Power Pivot and Power Query
used to be add-ins that we would add into Excel.
But once we have Office 365, Power Query,
which we just talked about, is on the Data ribbon tab.
And for Power Pivot, we'll have a separate tab.
Now, what does Power Pivot do?
It will allow us to create a data model where
we can have multiple tables, formulas, and relationships
building that star schema.
And then from that data model, we'll
build pivot table reports.
The other amazing thing is that, behind the scenes
in Power Pivot, there's something called a columnar
database.
And it can hold big data and process quickly
over that big data.
Now, before Power Pivot, we were limited to a million rows
in Excel.
But with this columnar database in Power Pivot,
we can easily hold millions of rows, even up
to 100 million rows or more.
The third thing that Power Pivot does
is it brings a new formula language called DAX.
And this DAX formula language will allow us
many more calculations than a standard pivot table.
And get this.
We'll be able to build one formula that
can work in many reports.
And that one formula--
we'll be able to add number formatting to that formula.
Think about that.
One formula with number formatting.
We could never do that in Excel.
We never had the ability to create one formula to use
over and over.
But with DAX and Power Pivot, we can do just that.
Now, specifically, Power Pivot will allow us to replace--
in the old days, when we'd have to use lots of VLOOKUP formulas
and build a single flat pivot table data source--
we don't have to do that anymore.
We can bring multiple tables into Power Pivot,
build relationships between the tables,
build our data model star schema,
and that will allow us to create more efficient reports
and dashboards.
And with the Power Pivot columnar database,
it will be able to hold millions of rows of data.
And of course, those DAX formulas
have more power than standard pivot table calculations,
or for that matter, some of our standard Excel spreadsheet
formulas.
Now, more specifically, let's go over to the sheet DAX.
What does DAX stand for?
Data Analysis Expressions.
That "expressions" is a synonym for formula.
So you could think of it as data analysis formulas.
The X, of course, is the second letter.
So when you see DAX, Data Analysis Expressions
or formulas.
And they are formulas for your pivot table.
We'll actually see how to create calculated columns in the data
model.
Measures-- those are the formulas in the pivot tables.
We'll see lots of amazing DAX functions,
like SUMX, CALCULATED, RELATED, and much more.
We'll learn about why we always want
to use explicit rather than implicit measures
in our pivot table.
We'll talk about row context, filter context.
We'll get to see scalar and table functions.
Yes, there's actual functions that
deliver entire tables, like what we used to do
in Excel with array formulas.
And we'll get to see this amazing thing called
DAX Studio, which will help us visualize and analyze
the DAX formulas.
Now, we'll also get to talk about Power BI Desktop.
Now, what's that?
That's not in Excel.
It's a completely free, amazing tool
that combines all of these in one tool.
That's right.
Power BI Desktop actually has Power Query built in.
It has the columnar database, where we build our data model.
It has DAX formulas.
But the thing that's different is
that we can create interactive visualizations.
So if you're after visualizations--
now, I don't have an interactive one here.
But if I were to click on one of these columns,
all of the other visual elements in our dashboard or our report
dynamically change.
Now, it's different than Excel, because it's a free tool.
You don't have to buy it like you do Excel.
And it's perfect for building dashboards.
And in fact, a number of times in the class,
we'll do the same data analysis project.
And we'll use both Power BI Desktop and Power Pivot.
Now, in this class, we'll definitely
get to talk about building dashboards.
We'll build dashboards like this in Excel
and dashboards like this in Power BI Desktop.
And then we'll close out the class,
after studying all the tools, with a bunch of case studies
where we get to perform our data analysis using all of the tools
together to create useful information from multiple data
sources.
Now, one more important thing about this class
is you can download the Excel files
or whatever files we might be using.
Text files, Power BI Desktop files,
and even a summary of all the notes
from every video in a PDF form.
You can download those files below the video.
Not only that, but for a lot of these videos,
they'll be practice problems so you
can practice what you learn.
Now, because this is a free resource here at YouTube,
be sure to show your support for ExcelIsFun
by clicking that thumbs up, leaving a comment,
and subscribing.
All right.
We'll see you next video.