Microsoft Power Tools for Data Analysis: Dashboards & Reports. Class Introduction Video. MSPTDA #01.


[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.

You May Also Like

About the Author: Oren Garnes

Leave a Reply

Your email address will not be published. Required fields are marked *