Transcripts
1. Lesson 1 - Course Introduction: Hey, friends. Welcome to PowerBI for business
intelligence. Most people learn PowerBI
by memorizing features. This course will teach you
something more useful, how to think with data
and tell a story with it. I'm Sherman, and I'll be guiding you through
the whole thing. Similar to my Excel class, this class is designed for
two kinds of learners, beginners who have never used the PowerBI
platform before, and intermediate users
who are familiar with the platform will want
to understand how to tell better stories
in business report. For different audiences.
In this course, we'll explore the logical
frameworks companies use to evaluate your
analytical skills, whether it's for
a job interview, an assessment or an
on the job task. A little bit about my
background in PowerBI. I started using PowerBI when I became a reporting
analyst in 2021, and there was a strategic reason behind why I wanted that. My background is in finance, and I had only worked roles involving financial derivatives. And while I was very good with numbers and analysis in Excel, I was missing the part
where you show how numbers actually drive
business decisions, and I was very interested
in learning that. Storytelling has become a
critical skill in business, and that reporting
role taught me how to communicate insights
in the right way, depending on the audience. This course is built on
the 80 20 principle, the 20% of knowledge that drives 80% of real
world outcomes. And so instead of cramming
every shortcut and feature, you'll learn how to think
through a problem and then present a story
that showcases your data is trying to tell you. We all know how rapidly
things are changing. New features get
added constantly. AI is being incorporated
everywhere, and a single button now can do a lot more than
it could before. And while that is great, an
important consideration is that critical decisions still require the human component. And so understanding
the logic will help you figure out which
feature to use when. In business, the tool
supports the decision, and a common mistake
people are now making is focusing too much on showing off features rather than
clear reasoning. And so in this course, I
want to help you step back understand the
logic and actually learn how to converse
with your data. The dataset I'm using has
been generated by AI, but is designed to
reflect the kind of business data companies
actually work with. It is also a lot cleaner than real world data just so that you don't get overwhelmed
at the beginning, which studies show is the best way of
learning a new concept. Now that you know
what to expect, let's start by
understanding what business intelligence
actually is.
2. Lesson 2 - Intro to Business Intelligence: How is business intelligence different from data analytics? Data analytics is about
examining your data to answer a specific question,
identifying patterns, drawing conclusions, and using data to examine
a specific problem, which is why we
spend so much time on the problem statement
in the Excel course. Business intelligence
takes that a step further. It's about helping people in the organization understand what has happened in the past and what is happening right now on an ongoing basis so that decision makers can act
on it in real time. That means tracking key
performance indicators, KPIs regularly, making interactive dashboards, and making insights accessible to people across the
organization, not just analysts. So if data analysis
answers the question, business intelligence
monitors a business on an ongoing basis, and Power BI is one of the best tools in the
market to do that. Whenever you're
building a report, there are two main things that should guide
every decision. The story and your audience. What are you trying
to communicate with your data and who will
be using this dashboard? Because the answer to
the second question will change a lot about
how you build the report. How detailed would it be,
what visuals you choose, and how much context
you provide. And we're going to
touch upon this a lot in the
visualization section. So now that you understand
what business intelligence is, let's go ahead and examine
the Power BI platform itself.
3. Lesson 3 – Getting Familiar with Power BI: In this lesson, we'll explore the PowerBI platform so you
know where everything lives. If you haven't
downloaded PowerBI yet, you can go online, search for Power BI Desktop on
the Microsoft site. You'll see the
option to download now and it's completely free. I'll also include a link
in the course description, so you can use that link
to download PowerBI. When you first open PowerBI, you'll see a bunch
of options like this and you'll start
with a blank report. This is what the
Power BI Desktop page initially looks like. It's a blank canvas
at the moment. And once we add visualizations, this is where we'll
build our report. On the left, you'll see three
icons stacked vertically. The first is the report view
where we are at right now. The second is our table view. It's empty right now,
but once we import data, you'll be able to see the
different tables right here. And the third is our model view. This is where you'll
build relationships between the different tables. Don't worry about
memorizing any of this. It becomes a second nature
once you start using it. Along the top, we have the
ribbon similar to Excel. The first option is get data. This is what you will use
to import your dataset. You can see that
you have a bunch of different options here, but we will be using the
Excel workbook option. We won't go through
all of the buttons. You can explore
them on your own, but I will just be showing you the ones that we'll
use in this class. The second button
is transform data. This opens a Power Query editor, and that is where we'll
do most of our cleaning. The next is refresh. Obviously, right
now, it's great out because we don't
really have any data, but this is used to update
your data from the source. So whenever you add, delete or make changes
to your dataset, refresh is the button that you will click to update
your dashboard. The calculations section
is also commonly used, especially quick measure
and new measure because you can create new functions
using these options. The next is publish. This uploads your report
to PowerBI service. Now, that is the Cloud version and requires a paid license. But if you're using
PowerBI at work, your company probably
already has it. So you would be able to
publish your report, and then you can share it
with the rest of your team. The right here, you
have the filters, visualizations and data panes. In this course, we won't be
using the filter span as much because we will be doing all our filtering and
Power Query editor. At work, I use the filter span quite a bit if I have multiple
tabs in the same report, and if every different tab needs a different filter,
that's when I use this. The visualization pane is where all your chart types live, and this is where you will
select which chart you want to add into the blank Canvas
or into your report. You will drag and
drop fields from the data pane into the
visualization span, so we will spend quite
a bit of time here. The data pane is
empty right now, but this is where your
data will get loaded in once we import our dataset. This section right here is
called Power BI Desktop, and now I want to briefly
show you Power Query Editor. So if you click on
transform data, another window will
open up, and this, as you can see on the top,
is the Power Query editor. We will spend a
lot of time here, and this is where you will do
majority of your cleaning. I'll show you the steps that we take once we import the dataset, but this is where you
will do your cleaning. This is the first thing
that you will open up after importing your dataset.
So that's your orientation. You've seen the three views,
the ribbon at the top, Power BI Desktop, and
Power Query Editor pages. So now let's go in and
import our Excel dataset.
4. Lesson 4 - Importing Data: Let's now import
data into PowerBI. To do that, you would click
on the Get Data button, and as you can see,
you have a bunch of different options to
move data into PowerBI. You can use a
standalone Excel file, which we will be using
for this course, but you can also
use SQL databases, SharePoint folders, web sources, there really are a lot
of different options. But before we jump in, I want to quickly mention
something that's genuinely useful for anyone working in a corporate
environment. If you're building
dashboards at work and the source data gets
updated regularly, you don't want to manually re upload your file every time. What companies typically
do is they store their Excel files
on SharePoint and then connect
directly to PowerBI. Or they have a SQL database
and then they pull data from the SQL
database into PowerBI, so it's directly linked. Now, whenever the SQL
database is updated, PowerBI will
automatically refresh. In SharePoint, you can
click on the refresh button and you'll see that the
dashboard updates automatically, or using Power Automate, you can add triggers that whenever the
source data is changed, Power BI will
automatically get updated, so you would never even
have to touch the file, and that is essentially how real time dashboards
are created. The reason I'm mentioning
this is because the data connection logic
is always the same. The only difference
is the source path. So you could also sometimes
just be working on a standalone static Excel file and then upload to SharePoint. You wouldn't have to change
anything in your dashboard. Nothing would change
except for the source pad. If you're in an
interview setting, you will almost always use a standalone
Excel file because they won't give you access to their databases or
their SharePoint links. But when you're
working, you'll likely just connect to wherever
the data lives. So again, we went into home, clicked on Get data, and we
have all of these options. I'm using an Excel workbook,
so I would hit Connect. And right now, I have
it saved in Desktop. Select the file, click Open. It's establishing a connection, and now this is one of the most important parts
of importing data. You will select
the different tabs that you want to work on. In this case, we have two tabs, Sales data and Sales Rep Info. And this is what I mentioned
in the last lesson, you will not click on Load. If you click on Load, this
data gets loaded into the Power BI Desktop page and essentially is ready for
building dashboards. That is not correct. We always want to clean our dataset first. And so whenever
you would click on the G data option
and select a file, you would then click
on Transform data. And this will take us to the
Power Query Editor page. I want this to become
a habit for you, no matter how small how
clean your dataset is, whenever you load it into PowerBI always click on
Transform data first, so the Power Query
editor page opens up. And then this is where we will start cleaning our dataset. Once the data is clean, you can be very sure
that everything downstream will
work much better. In the next lesson, we'll
start working on this dataset.
5. Lesson 5 - Data cleaning: The last lesson, we
imported our dataset into Power BI Desktop and opened up the Power Query editor by
clicking on Transform data. Before I click on
anything over here, let me quickly walk you through the window that we can
see in front of us. On the left, we
have our two tabs that have been imported
as separate tables. So the sales rep info table
and the sales data table. In the middle, you can
see the data preview. And on the right, we
have our query settings, which first has the
name of the sheet. So here we can see under
properties. It says name. You can rename this file
as well, right now, I like the name that has
been imported directly. The second is one of the
more important sections of Power Query Editors, which is the applied steps. You'll see that every
single step that PowerBI has taken itself
has been loaded onto here, and if we wanted
to undo any step, we would just click
out of that step. So if I were just to click out of changed type,
you would go back. And as we do our data cleaning, you'll see that the steps
are recorded over here, and this is basically the
same thing as Control Z or undo in any other application that you would do, you
would see that here. Applied steps does two things. One, it shows you exactly what you've done in
every single step. So even if I go into
the other table, you can see in every step it'll show you
exactly what was done. So that is one really good
thing about applied steps. And the second is that
whenever your data is updated and you
refresh your dashboard, it goes through all the
different steps you've done. So whatever cleaning
you've done, whatever data
manipulation you've done, it will do the exact same things and then you'll get
an updated dashboard, which is why you can create
real time dashboard. Using the same data, just
updating new information. This is why whenever
you have Excel sheets, it's important to do all
the steps in PowerBI. Don't worry about cleaning
up your dataset first in Excel and then importing
it into PowerBI. You want to do everything
in PowerBI because of the applied steps features because when you
update your data, you don't have to touch
anything in Excel. It will do all the same things to give you the final output. So that's one really good
thing. About this feature. Along the top, obviously, we have the ribbon like we
did in Power BI Desktop. The same thing happens
in Power Query Editor. There are a lot more features
and a lot more things that you can do because this is where you clean your dataset. I won't go through all of them. We'll just see what we use, and then you can play
around with it yourself. The one thing that I do want to mention is the data
source settings. If you recall in
the last lesson, I mentioned that if you ever change the location
of your file, you don't have to
worry about your dashboard and if you have to do everything all over again because you would only
have to change the source. So this is where
you would do it. You would go into
data source settings. And if you saved your
Excel file on SharePoint, for example, this is where
you would change it. All right, so now
for the good stuff, the very first thing I
do whenever I import my dataset is make sure that
the data types are correct. Right now, you can see that one of the automatic steps that was applied in PowerBI
was changed type. This was not the case
a few years ago. So you would have to
manually go in and make sure that the
data type was correct. For example, if I open
the source file here, total sales is a text field. The unit sold is general
unit price is a text field. That is not correct
because obviously these values are numbers. Now, why is this important? This is important because if your data types are not correct, your data will not act the
way it's supposed to act. So if, for example, you're calculating the total
number of units sold, if units sold is not a number
and it's a text field, Power BI would not be able to calculate the sum because
it's a text field. It's not a numbers field. Date, for example, if date is a text field and it's
not a date field, you would not be able to extract the year and the month
and the for that reason, it's very important that
data types are correct. Another reason you
want to make sure your data type is
correct is it will be important when
you are merging different tables or
building relationships. For example, in our
Sales Rep info page, our primary key or
a unique identifier is the sales rep ID. We will be using this
to build a connection between sales rep
info and sales data. So sales data also
has sales rep ID. If the data type between these two columns
is not the same, you won't be able to merge the two tables or
you won't be able to build a relationship
between the two tables. So those are two reasons
why checking data types as the first step is the most important
thing you will do. Power BI, there are multiple ways of
doing the same thing, and you'll notice that as
you use the platform more. So for checking the data type, you can either
check it over here, you can see the ABC on the
left side of the header. If you click on here, you'll see all the options for data types, and another way to change is
in the ribbon at the top, under the home tab
under Transform, you have the data type
option over here. So those are two ways you can
play with the data types. I'm just going to go
through the columns and make sure
they're all correct. Sales Rep ID is a text
field that's correct. I want to make sure
that in sales data, sales rep ID is
also a text field. So this will help us build a relationship between
the two tables. Name is text, region
is also text. Age is a whole number. This is correct. Gender
is also a text field. Column six onwards, it feels like these are just empty, so I'm going to go ahead. I'll select this column,
go to the very end, hold Shift and select
these columns, and then under managed columns,
I'll just remove them. If I go back into
our Excel file, these were just empty columns, so they were loaded as something into PowerBI, so we
just removed them. For sales rep ID, I don't
want any null items, so those are the blank cells, and then I can click Okay. So as I did the two steps, you'll notice that
under applied steps, we have two steps here, removed columns and
then filtered rows. Everything else here is fine
if I go into sales data. So here we have
date as DateTime. I don't really care
about the time. You can see that it's
all 12:00 A.M. Anyways. So I would rather
this just be a date. I can click on the
left icon right here or I can click here
and select date. It asks me if I want
to replace current. I do want to replace that
and make that a date column. It also seems like there are some empty rows in our dataset. And we don't want
that to be the case. So I'll just remove null
and anytime there are empty rows loaded into Power BI, it's going to remove
the null rows. Country is fine,
product is fine. Unit sold is a whole
number that's fine. For unit price, I would rather
this be a currency value. So as I changed the
type to currency, you saw that in
the applied steps, we had those steps reflected. Sales drop ID is also correct, and then the total sales, I also want this to be
a fixed decimal number. I can also see that there's
a typo in the header. So if I double click here, I can put another S enter, and you'll see that
in applied steps, it says renamed columns. As I mentioned in Lesson one, this is a relatively clean
dataset because it's important when you are learning
a new concept that things aren't
too difficult. So I try to keep it as clean as possible to just show you
how a few steps are done. But normally your dataset
would be a little messy. For that reason, it's
very important or actually very useful for you
to see the column quality. So you can do that by going under view in the ribbon above. And if you click
on column quality, you'll see how many valid
rows, how many empty rows, how many errors there
are in your dataset, and it really, really helps you in cleaning up everything. And you can also see the
column distribution. And here you'll see how many
distinct values you have, how many unique values you have. This can also be
very helpful when your dataset is big and
it's a little messy. So right now, I'm going to not show this because we
don't really need it, but I just wanted
you to know that that exists in Power BI. One very common thing is to add conditional columns
or custom columns, so I want to show
you how to do that. For units sold, if we've
sold more than 15 units, I'm going to call
that a large order or a small order, otherwise. So I can go into add
column in the ribbon above and then add a
conditional column. We can call this order size. And then if column
name is units sold, and this is greater than 15, then I would like to call it, then I would like to call it a large order else
it's a small order. So if I click Okay, you
can see that over here, we have order size,
large or small, and I think I want to
keep this as text only. You can do something
similar in Dax, but I want you to do whatever you can in
Power Query first. There are some measures
that you might create after loading
your dataset, but it's very important that any conditional columns or custom columns that
you know you will add, you do that in Power Query. So now that everything is done, I would like to apply whatever I did in Power Query Editor and go into the Power
BI Desktop page and start adding visuals. For that, you will go
back into home and this close and Apply button is something that you
will use all the time. Anytime you make a small change, you will have to
close and apply. So you can either apply, you can close or you can just close. There are three
different options, as you can see in the
drop down right here. So I want to close out
of Power Query Editor because I want to return to
the Power BI Desktop page, but I also want to
apply the changes, so I can go in here and
hit Close and apply. You'll see that now it's
loading the changes. Perfect. And now we have our dataset loaded
into Power BI Desktop. So you noticed the change that happened in our Canvas
in the report view. Now it says, build
visuals with your data. But the way to see
that data has been loaded is going into the
data pane on the right. So before, if you recall, the data pane was
completely empty. Now we have two
tables over here, and if you open the drop down, you'll see that all
the columns have been loaded into the data pane. You'll also notice that there's a calendar icon next to date, which shows that this
is a date field. So you have the year quarter, month and day broken down. If you hadn't changed
the data type, this would not have
been an option. Now, if you go into the
table view on the left, you'll see that we
have our two tables loaded before this was empty because there
was no dataset, and the data of pain
is still the same. You can see the
different columns here. So as you can see, if
you go into table Tools, you can add a new table, new column measures
here as well, but there aren't
that many options as there were in Power
Query Editor. So again, it's important that you make
changes over there, but sometimes you
might have to make changes in Power BI
Desktop as well. So we saw how the table view and the report view changed as soon as our data was loaded
into Power BI Desktop. In the next lesson, we're going to check out the model view.
6. Lesson 6 - Data Modelling & Relationships: Last lesson, we cleaned our
data, hit Close and apply, and exited the Power Query page to come back into
Power BI Desktop. If you've taken my Excel course, you'll probably recall that
after cleaning our dataset, we used Loup functions, V Lou Lou, and Index Match to bring everything together
into one flat single table. PowerBI does this differently. Instead of pulling
everything into one table, it keeps the tables separate and defines how they
relate to one another. That is the data model
and what we will be doing in the modeling
it's a better, flexible, faster way to bring tables together
without duplicating data. In the last lesson, we saw
how the report view and the table view changed after data was loaded into
Power BI Desktop. If we go into the model view, we can see that that
has changed as well. Before we go into the details, I want to cover a few
foundational concepts, and this doesn't just
apply to PowerBI. It applies to whenever you're working with
relational data. The first is primary
and secondary keys. The primary key is the unique identifier in
our sales rep info page. Actually, let me go
into transform data. So this is our sales
rep info page, and the sales rep ID
is our primary key because every sales rep is
identified by the primary key, and there's only
one of each key. If we go into sales data, we'll see that we also have
the sales rep ID here, but there can be
multiple instances of the same sales rep, but this is the secondary key. This key references
the primary key in the main sales rep info page. The relationship
between the two tables will be built on exactly this. The sales rep ID
in sales data will point to the sales rep ID
in the sales rep info page. Now, there can be multiple
kinds of relationships, and you will hear
these terms a lot. The first is a one
to one relationship, which means every sales rep
ID in sales rep info page will only 0.1 sales rep ID
in the sales data page. That is a one to one relation. Each table has only
one of each item, and that is a very rare case. The second is a one
to many relationship, which we will be working
with right here. If you do a quick filter here because this is the
first one that I can see, so if I filter sales
rep 1005 over here and sales rep 1005 over here. We can see that there
is only one instance of sales rep 1005 in the
sales rep info page. But in the sales data page, there are multiple
instances because this sales rep has made
many different sales. So it's a one to
many relationship. The last is a many to
many relationship, and we won't be
working with this, and you want to avoid that
situation as much as possible. It just means that there are multiple instances of the
sales rep in both pages. This can get very complicated. PowerBI can handle these, but not just in Power BI. Overall, you'll
notice that whenever it comes to a many to
many relationship, there is a high chance
of error and inaccuracy. So you want to avoid these
as much as possible. So I've removed the steps, and here I can just hit close because we don't really
want to apply any changes. And now we're back to our Power BI Desktop
Model View page. In the model view,
we can see that PowerBI has already
detected the relationship, but don't always trust it. Always make sure that the right columns
have been connected. We can see that the sales
rep ID has been connected, but I want to show you
how to do it yourself, so I'm going to go ahead and
delete this relationship. And yes. Alright, so whenever you want to
connect two tables, build a relationship,
it's super easy. All you have to do
is drag and drop the sales rep ID to the sales
rep ID on the other table. And this new relationship
window will pop up, which shows you the columns
that are being connected, the tables that are
being connected. And then it shows
in the cardinality that it's a one to
many relationship, which is the exact
relationship that we want. So I can click on Save and now we have a relationship built between the two tables. We're also going
to see why this is important when we get into
the visualization piece. But right now, I just
wanted to show you how the relationship
is actually built. This will be very
important when you start working with
multiple tables. Here, obviously, it's very easy, but when you have
multiple tables, you have to identify
which table has your primary key and which
table it's connecting to. Not all tables will
connect to one another. There will be multiple tables connecting to many
different tables. And that's where these concepts can be very, very helpful. If you do have multiple tables, sometimes you might not be able to see where the table is. So there's this button
on the right here. It says fit to page. If you click on this button, all the tables will be
shown on the screen, and this again, is very
helpful when you have multiple tables and you want to see where the
relationship is going. Whenever you're working with more than two or three tables, always make sure that you
hover over the line over here that's connecting
the tables to confirm that the right
columns have been connected. Data is connected.
The relationship is built, the model is set. Now it's time for the fun part, which is building the dashboard. We'll do that in the next
susson and I'll see you there.
7. Lesson 7 - Building the Dashboard: This lesson, we're going to
start building our report, and this is the fun
part in Power BI. So here we are on
the report view, and this is where we
will start dragging in visuals and building
our dashboard. If you remember, I talked about the two pins on the right, which will be the most
important in this part, the visualization span
and the data pane. Before we start adding charts, think about what we discussed in the last few lessons
multiple times, the two things that are important
when creating a report, the story and the audience. If you think about any good
story, there is a beginning, a middle, and an end, and you always want to be pushing
the narrative forward. So we're going to
keep that in mind. The second thing
is the audience. Are we building
this for? We will be building an executive
level dashboard, which means we won't be adding too many tables with
rows and rows of data. We just want to be adding
the big picture charts. With that in mind,
let's add some visuals. The first thing I want
to do is just look at the different columns
that I have in my tables. What I want to start with
is country information. What I'm thinking right now is I look at how many products I sell or what is my revenue
in the different countries. Then I can talk
about the products. I can talk about
monthly revenue, and then maybe I can give in some sales rep
information as well. So first, the map chart wasn't really working
for some reason, but for country data,
you can add a map chart. For now, I want to
keep things simple. So I'm going to start by
adding a stacked column chart. For that, all I did
was I clicked on the stacked column chart
in the visualization span, and this box popped up. Right now, I don't have any data in it. That's why it's empty. This is where the data
pane will come in handy. I can click on Country
or I can drag and drop. So I'm just going to click
on Country right now, and automatically it
went into the Xaxis. And then for the Y axis, I would like to see
the total sales. So now I have country
and total sales. Maybe that can be
my first chart. Now for going into
the next chart, you want to make
sure that you click out of this chart right here. If you don't and you have
the initial chart selected, and if you select
maybe a line chart, you'll see that the
chart gets replaced. So always make sure that you
click out of a chart first. So I'm going to
go back and click on Stacked Column chart. I also could have hit Control
Z to go back to Undo. So I'm going to click
out of this chart, and then the next chart
I have is a line chart. Maybe, I want to show
monthly revenue. For this, instead of
pulling the entire date, I'm only interested
in the month. If you have a lot
of historical data, you might want to play
around with year and month because you can
look at year over year. You can look at
month over month. Maybe there are certain
months with more sales, so there could be seasonal information hidden
there somewhere. So that's why it's
really helpful that Power BI breaks it down into
year month and quarter. So I'm going to look at month I pull that into the Xxs and I want to look
at monthly revenues. I'm going to click
into total sales, and you'll see that
the first time I drag and dropped and it came
into the YXs and this time, I just clicked and Power BI I knew that it had to
go into the YXs. Sometimes you want
your canvas to be a little bigger if you have
a lot of charts there. Right now, I think I can
fit all four charts, but just so that you
can see how it works, I'll maybe change the size
of the canvas a little bit. So first, we'll make sure that none of the charts are selected. Then I can go under the visualization span into the second option here
format, your report page. Under page
information, actually, no, it's under Canvas settings, and we can change the type
from 16 by nine to custom. And I just want the height of my chart to be
a little bit more. Maybe I can do 800, enter, and then I can go into view
and fit to with Perfect. So now I have a bit
more space and I can increase the
size of my chart. Maybe I could move this
here because then I have that story that this is
the total sales by country, then I'm going to
show month by month. Now, like I mentioned earlier, that this is hat
GPT generated data. So obviously, it's
not actual real data. But when you look at
sales information, whenever you're doing month
over month or year over year, you always want to
look for trends. This is something
that you will do whenever you're doing
time series analysis. Identifying trends is one of the big things that you want to do in business intelligence. Fictional data, but I
can see that there are some months where there were more sales than other months. When you have year
over year data and you can see the same trend
in all of the months, that gives a company a lot of information
about their products. For example, maybe there are more sales in January
and December. That could be because of the
holidays, Christmas time. There could be more
sales in November because of Black Friday sales. So businesses can
prepare ahead of time. You can buy more inventory. You might need more
staff on site. Are some months where you
know that sales will be low, so you don't buy
that much inventory or you don't have a lot of
people working at that time. A lot of companies that are maybe newer and don't want
to pay a lot in rent, they can prioritize
different timings, different days of the week
to set up their stores. There are so many things that these dashboards can really
help you think about. The next thing I'm interested
in is product information. So I'm just going to
use the same charts. I'm keeping it very simple. I can have the product
in the X axis. And again, I'm
looking at revenue. So I have revenue in the Y axis. And then another chart
that I want to add, maybe I can do a
clustered bar chart. And now I want to look at
some performance data. So how are my sales
reps performing? Now, this chart will
show you the power of connecting the two tables that
we did in the model view. So here I will use the name of the sales rep because
obviously you can't really identify
people with their ID. You need the name of the person. So I'm using one field from
the sales rep info page, and the second field I'm
using is total sales, which is in sales data. So we're using columns
from two different tables, and if I were to
collapse these tables, you'll see that both of
them are being used. If I were to click
on another chart, you'll see that
only sales data has been selected because that is
the only table we're using. But when I go into this chart, we're using both our tables. Now I want to show
you what would happen if the tables weren't
connected with each other. So if I go into Model View
and I delete my relationship, and go back into report view. Now you can see that this table is not showing
us any information. It's just showing us the
total number of sales, like the overall total
number of sales, and every bar has
the same number. So essentially, this chart is now useless because the tables aren't talking
to each other. So I'm going to go back
into the model view, and I'll drag and drop the
Sales Rep ID, click on Save. Now we can see that
this is much better, and it's really showing us
some information that we need. I know that I'm building an
executive level dashboard, so I haven't added any tables
with rows and rows of data. If I wanted to add a table, I would just go into
the table visual, and this is where you can add a lot of different information. So if, for example,
I wanted to look at the products that
they are selling, I can add that as well. So you can go into a lot of
details with these tables. But right now, what I want is the name of the salesperson and the
sales that they have made, and I'm going to choose
a bar chart for it. So I know the audience that I'm building this dashboard for. And right now, the
story I'm telling is the total sales by country,
then month over month. What products are we
selling and who are the salesperson who are selling these products and
making the most sales? Now, we have our
four main charts, but they're just sitting there. We can't really
do much about it. They're just static. We need these tables to
start interacting, and that is one of the powers of Power BI that we can build
interactive dashboards, and that is what we're going
to do in the next lesson.
8. Lesson 8 - Building an interactive dashboard: One of the biggest strengths of Power BI is interactivity. In business intelligence, you don't just display the data, you let people explore it. A good dashboard allows
the viewers to ask their own questions and find their own answers just
by clicking around. In Power BI, there are two
ways of filtering your charts. One is directly in your chart. The other is by creating slicers. I'm going
to show you both. So like I mentioned,
the first way is just to click in your chart. So if, for example,
I wanted to look at information for
United States, I would click on
the United States bar in the first chart, and you can see that
all of my charts have adjusted to just
show United States data. You can exit the filter
by clicking back into United States
and automatically, all the charts will go back. If I'm interested in product and I want to look
at noise canceling earbuds, I would click on the noise
canceling earbuds chart, and all charts have been filtered to show
that information. I can click back or
I can click into the empty space in the chart
to get out of that filter. The second way of
filtering is by slicers, which honestly I like better. In the visualization span, you can go into the
slicer option and you'll see that we have a box here ready to be populated with data. The first filter I want
to add is for country. Now, we have a list
of all the countries. I don't really like
how this is showing up because it's taking a lot of
real estate on my canvas. So in the visualization
span, again, I can go into the second option, which is format visual. Under visual in slicer settings, instead of a vertical list, I'm going to select a dropdown. And here I can see that a dropdown has been created
that I can throw up there. And now there's a dropdown
for all the countries. If I click into the slicer, you'll see that
the data is being filtered according
to my selection. So I can click United States again and I'll be
out of the slicer. I want to add two more slicers, so I can select this again. I'm also interested in
filtering by product. Again, going into format visual. I can go into slicer settings
and then select Dropdown. Can adjust the size and
throw it up here as well. I would also be interested in monthly data, adding
another slicer, I can go into month
and then again, I'll do a drop down and then
put that up here as well. I also want a slicer
from my second table, so I'll click on
Slicer again and this time I'm looking at
sales rep information, just their names,
and I'm going to put in a dropdown again. If I had any date slicers, you could also pick range. There are many different styles that you can pick
for your slicers. Now, let's try out
these slicers. So I'll select United States. And then maybe I'm interested
in Smartwatch sales. So using these slicers, the information
that I can see on my dashboard here is specific for United States and for smart watches sold in
the United States. So we can see monthly sales, and we can look at the sales rep that have made these sales. If I was interested in
one particular sales rep, I could go in and select their name and see
their activity. Different countries for
the different products, and so creating slicers
can be very, very helpful. Also, after applying
filters through slicers, you can add additional filters by filtering within the charts. So now if you are only
interested in Germany sales, so you can click on Germany
and then you'll see that the filters are activated throughout the different charts. So one thing you've noticed is that for some of the charts, filtering doesn't
really make sense. For example, if I select
United States as the country, for this sales by country, there's only 1 bar chart that I'm seeing because it
filters out everything else. It doesn't really show me much. So what I want is
for the country slicer not to interact
with the first chart, because I always want to see the comparison of the
different countries, no matter which country
I select in the slicer. To do that, I want to
limit the interaction between one slicer
and one chart. And I can do that by
clicking on Country. There are two options that have popped up
in the ribbon above. I'll go into format, edit interactions and using
this icon right here, I'll click on this and I'll stop the interaction between
country and sales by country. So now if I go back and
select United States, you'll see that all the
other charts adjusted and filtered except
for sales by country. So sometimes you want
to limit interactions between certain slicers
or certain charts, and you can do that
by the format option, and this option, you will use a lot whenever you're
building your reports. We've added charts,
we've added slicers, we've managed interactions
between charts and slicers, but our dashboard still
looks kind of ugly. And the next lesson, we'll do some formatting
on these charts.
9. Lesson 9 - Formatting: PowerBI is a visualization tool, and for that reason,
you want to make sure that your report
is visually pleasing. But that doesn't mean making
it pretty for its own sake. It means removing friction between the user
and the dashboard. When someone looks
at your dashboard, they should immediately
understand what they're looking at and what the
charts are trying to say. If someone has to squint or
reread things or ask a lot of questions just to understand what the chart is
trying to communicate, then, unfortunately, your
dashboard has failed. So you want to make
it easy for the user. On that positive note, let's start by cleaning up
our charts a little bit. There are a few
things that we're going to change on these charts. The first thing is color. Whenever you're
thinking about color, try to stick to a simple color palette and
if you're building this for another company
in maybe an interview, for example, try to look up their colors and use those
colors along with their logo. It's going to give you
some brownie points. Don't try to use
too many colors and make it too colorful.
That never helps. Another thing worth mentioning
is if you can apply the accessibility guidelines for color contrast to
your dashboard, is a mark of a very
thoughtful analyst. I'll include the link
in the course resources if you are interested
in checking that out. So if I go into the
first chart here, we can go into the
formatting visual option. That's where options
for colors are. And then under columns, I will change this to I'm trying to remember the colors that I used
in my last company. We used this color a lot. The next thing I want to do is I want to fix
the X and Y axis. It's very obvious that
the X axis has countries, so I don't really need
that title there. So if I scroll up in the Xxs I can remove scrolling down title, and then I can do the
same thing in the YXs. I will be changing the
title of the chart. So the chart title is going to communicate what the
chart is showing, and so I don't really need
titles for X and YXS. So I can go into the YAXs
and turn off the title here. To change the title itself, I can go into General, which
is also under format visual, and there is the title
option right here, total sales by country. So now the title has changed. I want to make that
a little bigger. This chart already
looks a lot better. One thing you'll
notice is you can't really tell when a
chart starts and ends, and that can get a
little frustrating when you're filtering
within the chart itself. So if I were to
click on Germany, I would have to click
in the same chart to get out of that filter. And so what I like to do is
after selecting the chart, I go into format, and then under General, I go into effects, and I add a slight shadow. A lot of people don't
really like that, but I mean, I like it, so
I'm going to do what I like. And then undercolor you
can pick a light color. And then if you click out of it, now there's a light
shadow outside the chart, so you know where the
chart starts and ends. And so if you're doing
a lot of filtering, like if you're in the zone really trying
to figure something out and you want to click into and out of
different charts, this shadow option is so subtle, but it helps so much. Going to do the same thing
to the other charts. I'll change the color. I'll change the X and Y xs. I'll change the title, and I'll add a bit of shadow
to the chart. To make it easier for myself, I'm going to use the colors that I used in my last company, and I'll quickly finish this up. I finished up the second chart, and then I remembered that the format painter option
is there, which is amazing. So you select one chart. Under home, you can
click on Format Painter. And it does the same thing
to all the other charts. And then all you have to do
is change the colors a little bit so that not all your charts have the same color, obviously. So I fixed up my charts. I fixed up the slicers. One thing I want to do is
change the name of this report. Obviously, I can't believe
I forgot to do that. I can do sales data. One thing I always do
in all my dashboards is add key metric cards
if it makes sense, which mostly it does, because it helps the
user think about the overall picture and then they dive into the
different charts. It's just a storytelling
perspective again, that you think about
the overall theme and then you trickle down
into the rest of the item. To do that, I can go
into the key card. And then what I can do is
I can add total sales. Again, I'm going
to make it look a little nicer and so
I can adjust it, and I'm going to fix the
colors for this, as well. I think I'll add a background. I used to do this before. Again, because my company used this color, so
I'm just used to it. And then white increase the size of the callout
depending on the dashboard. Okay. Category label
is also white. Now you have your final report. If someone were to
go into country, for instance, select
United States. All the charts filtered out
except for the first one because we removed
the interaction because we didn't want
to see 1 bar there. And the total sales is
$43,000 for United States. I think I'm going to change
the title for this, as well. Here, we can just say
total sales in dollars. Perfect. So that's what
it would look like. If I'm interested in just
the month of February, I can filter that out
in the chart itself, or I can go in and select the month of February,
which also works. In that case, I would probably remove the interaction
between months between the chart and the slicer because look what it looks
like, it's just a dot. Because what am I supposed to
understand with just a dot? So I would probably remove the
interaction there as well. So you can play
around with this. This is what our final
chart looks like. This report is now complete. It's formatted,
it's interactive, and it's ready for executive
level presentation. And we went from a raw Excel
file to a ready report, and that is really
the beauty of FR.
10. Lesson 10 - Conclusion: And that's it. You've made
it to the end of the course. We started with
getting familiar with Power BI Desktop and
understanding the three views. We then imported our
dataset and cleaned it up in Power Query before
it ever hit the model. Then we added
relationships between our tables so that everything
could talk to each other. After that, we built a set
of intentional visuals, added interactivity
with slicers, and then formatted
the whole thing into a clean
executive dashboard, and that is the
entire pipeline from raw data into a
presentation ready report. But more than the technicals, what I hope that you're taking away from this course
is a way of thinking. I said this in the Excel course, and it's just as true here. Anyone can learn how to click buttons and how to
use different platforms. It's also become very
easy with AI now. But what employers, clients, and stakeholders actually value is how much you understand
their business, how you can tell a story
with the data provided and communicate it clearly to the right audience
in the right way. Power BI is just a tool that helps you do that
at a large scale. If you found this
course helpful, I would love to hear about it
in the discussion section, and also I would love to
see what you built and what visuals you decided
to add in your report. Upload some screenshots as well. Thank you so much for
spending your time with me. Keep building, keep
asking good questions, and happy analyzing. Bye.
Shermeen Velani, Shermeen Velani
