Hi everyone, Kevin here.
Today I want to show you how you can use Microsoft Power BI. With Power BI,
you can get insights from your data. When I worked at Microsoft, I used Power BI all the time to
analyze how our businesses were performing and to make decisions on that performance.
We're going to walk through a few different things today.
First off, I'm going to show you how you can both download and install Power BI.
It's entirely free to do. Then we're going to import some sample data into Power BI.
We're going to create some visualizations off of it, and what good would visualizations be
and reports be, unless you could show it to others. We're going to save it, publish it,
and then share it with others so they can get access to your visualizations as well.
This is going to be a high-level overview of Power BI,
more of a 101 or introduction. If you want to see me go more in depth in Power BI,
leave comments down below, leave some questions and I'll add them to my list of videos to create.
All right, well, why don't we jump on the PC and get started here.
Here I am on my PC and first off, I want to show you how you can download and install
Power BI and to do that we're going to navigate to the following website. Now don't worry,
you don't have to type in the website, instead you can click into the description of this video and I
have a link to this website. Simply go ahead and click on there and that'll drop you on this page.
On this page, by far the easiest way to install Power BI and the way I'd recommend doing it,
is clicking on the download free button. When you click on this,
this will open up the Microsoft Store on the Power BI download page. If you haven't installed it yet,
this text here will say install. Once you’ve installed it, you'll be able to launch it.
Now, once again, downloading Power BI desktop is entirely free.
You don't need an enterprise account,
you don't need a commercial account, anyone can download Power BI Desktop.
The benefit of installing through the Microsoft Store is anytime Power BI updates,
you automatically get those updates. Anytime any of the components change, the Microsoft
Store will only download the pieces that change, so the updates should be pretty quick and easy.
And lastly, a nice added benefit is you don't have to be the admin
logged onto your machine, you could be any user to go ahead and install Power BI Desktop.
This is one way you can download and install. If you prefer, there's also another option where you
could click on see download or language options. When you click on this, this does not go through
the Microsoft Store. Instead, you could download an EXE and then you could install it on your own.
So here on the page, you simply go ahead, choose the language that you want to install in,
and then click on the download button. On the download page, you could either choose 64 bit
or 32 bit depending on what type of system you're on and then go ahead and click next
and it'll download an EXE that you can install on your machine.Those are the two ways you could
install. Once again, I would recommend going with the first option where you click on download here.
Once you finish installing Power BI, let's go ahead and launch it.
You could go to your start menu and type in Power BI.
I also recommend pinning it to your taskbar for quick access in the future.
When you launch Power BI for the first time, you'll see this welcome screen appear
and I want to take a moment to walk through it. Up here in the top left-hand corner,
the most important thing you could do to get started is to get data. You can't create
visualizations unless you have some data. You'll also see recent sources, so these are any other
data sources that you've used in the past for quick access, and down at the bottom, if you've
ever used Power BI before, you'll also be able to get back to reports that you've created.
Now, over on the right-hand side, Power BI provides
a whole bunch of training content to get you up to speed on how to use Power BI.
Once you watch this video, if you still feel like you need some additional context
or you need some additional tutorials, I highly recommend looking at this material.
There are some awesome videos and also tutorials that you can run through.
Today what I want to do is I want to run through an example of the Kevin Cookie Company.
This is a fictional company that I created that I use in many of my different tutorials,
and I want to show you how we could analyze some of the sales data for the Kevin Cookie Company.
Before we get the data in Power BI, I want to just give you a preview
of what the data looks like so you know what we're working with today.
First off, I have an Excel spreadsheet that contains a whole bunch of data.
It happens to be in Excel, but this could also be in, say, a SQL database,
it could be a Kusto query that I run to get this output.
So just think of this as some data that I'm getting and it just happens to be an Excel
spreadsheet just for simplification and also so you could follow along as well.
Within the Excel spreadsheet, I have a whole bunch of revenue, cost, and also profit data.
I'll just go through one row just so you have an idea of what type of data we're working with.
First off, I have sales in a number of different countries, so I have the country listed in a
column, then I also have the product. We have a variety of products like chocolate chip cookies,
oatmeal raisin and so on. We've sold a certain number of cookies in each of these markets,
and I've listed out how many sales. You have the revenue for each type of cookie,
the cost, the overall revenue, then we have the overall cost, and then you have the profit.
Then for the last few columns we also list out the date, the month
and then also the year when these sales occurred.
So that's a quick view of what's in this spreadsheet, and I'm also going to pull in
one other source of data into Power BI as well and it's going to come from the web.
Now there's a Wikipedia article here that lists out countries and also the population
by the country. For my analysis in Power BI, I want to show all the countries where
we have locations and I also want to align it with the population.
Once again, I have a link to this spreadsheet and link to this website URL on Wikipedia
in the description, if you want to follow along with this example.
Now that we've taken a look at what type of data we're going to be working with,
let's go ahead now and click on get data.
This brings up the get data dialog,
and like I said before, there is an exhaustive list of places where you can get data from.
You can get data from a SQL database, from the web, from Kusto queries.
The list goes on and on. Chances are, if you have data somewhere,
you'll be able to pull it into Power BI.
Today we're starting out with a simple example and we're just going to use a spreadsheet and
it's an Excel spreadsheet, so I'll click on Excel and then click on connect. Next,
this brings up the file picker and I see the file here on my desktop.
If you're following along and you downloaded the spreadsheet,
feel free to navigate to where you downloaded the spreadsheet and go ahead and then click on open.
This brings up the data navigator and I see my spreadsheet right here,
the Kevin Cookie Company financials and within the spreadsheet it detected two different items.
One of them is the financials table and the other one is the cookie sales
sheet. The cookie sales sheet is made up of only one table and that's the financials table.
So these two items are identical and I can choose either one.
In this case, I'm going to go ahead and check the box for cookie sales,
and then I see all of my data over here on the right-hand side.
If I just wanted to continue at this point,
I could go ahead and click on load and that would bring it into Power BI.
However, I want to transform the data first.
Now you might be wondering, well what does transform data mean?
In a sense, it allows me to modify my data
and select what data I want to bring in before I bring it into Power BI.
So let's go ahead and click on transform data
and I'll show you some of the changes I want to make to it.
This opens up the power query editor and this is where I'll
be able to transform my data. Basically what I'm doing is I'm shaping the data
and I'm building a data model from it, so let me show you what I mean.
So here you see the spreadsheet view again and I want to make a few different changes.
For instance, we have a bunch of different products we sell at the Kevin Cookie Company
all the way from chocolate chip cookies to white chocolate macadamia nut cookies. We also
sell fortune cookies; however, we've decided to cut this product line because in the past
when we've looked at it, we've seen that the profitability of selling cookie Fortune cookies
isn't that high, and it's been distracting us from our other products and our other business lines.
So I want to remove it from this analysis,
so I'm going to go ahead now and uncheck fortune cookie and then click on OK.
So now by doing these changes, I'm starting to transform the data.
Now when I look over at units sold, you'll see that I also have some decimals here.
For United States, chocolate chip cookie in one month, we sold 0.5 cookies.
Now that doesn't make any sense,
and so I want to eliminate decimals from the data analysis that we're going to do.
And So what I'm going to do is I'm going to click on the unit sold column.
We're going to right click on it, and then I want to click on change type
and I'm going to go ahead and make all of these just whole numbers.
Next, as I look through my table here, one other thing that doesn't quite make sense,
if I go over to the right-hand side,
I say month name. Instead of saying month name, I could be a little more concise.
So I'm going to just double click on this column header and I'm going to get
rid of month name and we're just going to call this column month.
As I go through and I make all of these changes, over on the right-hand side, you'll see this
section called applied steps. These are all the different transformations I'm making to the data.
So let's say that maybe I made a change and I didn't really intend to make that change,
I can remove that step. So here is an example. You see this filtered row. If I click in here,
I could see what I filtered. Here I filtered out Fortune cookies.
Now I don't want to just eliminate fortune cookies altogether because it was something
we were selling and I probably should include it in our visualizations that we're making, so
on second thought, let me go ahead and keep it. So what I could do is I could click
this X and then I could delete that step and that'll remove that transformation to my data.
All of this data looks pretty good now and I feel good about it and
I'm ready to bring this into Power BI.
One thing I want to do though, before I bring this data in is I also want it to
bring in population for these different countries.
To do that, I'm going to go up to the top ribbon and I'm going to click on new source and this
data is coming from the web. Now once again, I could click on more and that'll bring up the same
data source dialogue that we saw earlier, but I also see Web highlighted here,
so I'm going to click here because that's easier to pull this up.
This brings up the From Web dialogue and what I'm going to do here is it asks me for the URL,
so I'm going to go ahead and paste the Wikipedia site URL within here
and once I paste it in, I'm going to go ahead and click on OK.
Similar to before, this also brings up the navigator, and here I can see
all the different elements that are on the Wikipedia page.
I'm going to click on this first one, the sovereign States and dependencies
and here I see all of the data, all of the countries with the associated population.
This is exactly what I want. Now this is really cool that if you find the table on some website,
you can pull that in as a data source into Power BI.
Once I'm satisfied with this table and everything looks good, I'm going to click on OK.
Over on the left-hand side, now you see that I have two different queries,
one of them is or my cookie sales and the other one is the Wikipedia
article. I'm going to right click on this and give it a name that makes more sense.
I titled it country populations and simplified the name somewhat.
And here within this table I don't need all of this data, so similar to before,
I'm also going to transform the data that appears here. For instance, I don't need the percent of
world population, so I'm going to go ahead and remove this column. I don't need the date and
I also don't need the source to appear either and I don't care so much for the rank either.
All I want is the country name and then the associated population. For the Kevin Cookie
Company, we only have locations in a few of these countries, do I don't need to bring all this data
in for all these different places, so once again I'm going to transform the data even more. So I'm
going to click on this dropdown and I'm going to filter it down to only the countries I care about.
So I'm going to type in Canada, France, Germany, Mexico and the last one is the United States.
Once I enter in all my different countries where we have locations,
I'm going to go ahead and click on OK and here I see the five countries where we have locations.
Now it's not quite done because here I see United States and there's a D after it.
So one thing I'm going to do is right click on this and I'm going to replace the value
and the replaced value will remove the D and then I'm going to click on OK,
and similarly for France I'm going to go ahead and do the same thing.
Now I have my list of countries and I also have the population
associated with those countries.The next thing that I want to do is I want to connect
this data here or this table with my cookie sales table.
Right now they're both independent tables and I want to be able to associate the two of them
or to join them, and to do that, I'm going to click into the cookie sales table.
I'm going to go up to merge queries and I want to connect it based on the country name. So I'm going
to select the country name here and my cookie sales data and then in my country populations,
I'm going to connect it based on the country name. It asks me about privacy. I'm going to
ignore privacy checks for now. This is just Kevin Cookie Company, so I'm not too worried.
I'll go ahead and save that, and it looks like every single row in my Kevin Cookie
Company table has a match with populations and I'm going to go ahead and click OK. Over
on the right-hand side, now you see in cookie sales it's added a column.
Let me go ahead and click on this filter option and I want to see the population.
So I'm going to click on OK and now here for every row within the Kevin Cookie Company data,
I see a population associated with that country.
All of this looks good.
I brought in my spreadsheet.
I brought in data from Wikipedia and I connected the Wikipedia data to my cookie sales data,
so I'm pretty satisfied now. I don't need to make any other changes and the next thing I
do now is over here in the top left-hand corner, let's click on close and apply.
Now that we've created our data models, Power BI drops us on a blank canvas.
I want to take a moment to walk through and orient you to where we are right now.
Right here in the center of the page, this big blank area, this is called the report editor.
This is the canvas where we're going to create all of our visuals,
so think things like charts or tables or maps.
All of those will go within this space here. Over on the right-hand side,
these are all of our fields.
And so you'll recognize the Kevin Cookie sales and then all of our columns appear within here,
and similarly I have another table for the country population, so all of those are called fields.
Basically, it's all of the data that we have and that we could build visuals
off of. Right to the left of that, there's another pane called visuals.
These are all the different types of visuals we can build
using the data over in fields or from this pane.
Now that we've looked at visualizations,
I also want to show you what's over here on the left-hand side.
Over here, the first view and the default view that we drop in is the report view.
This is where we're going to build all of our visuals.
Underneath that you have another option for data,
and if I click on that you'll see the yellow indicates the view that I'm in.
This is a view of all of my data, so once again
this is the same type of view as what we saw in the spreadsheet earlier.
Although it's all been transformed now based on the earlier step.
Over here on the right-hand side, I can see my fields again, and I could jump between
the different data or underlying data that I have here in Power BI.
Then over on the left-hand side, there's a third option called model.
These are all of my different relationships between my data.
I'm going to go ahead and expand this. If I hover over, you'll see this line connects my 2 tables.
One of them is my cookie sales, the other is my country populations.
When I hover over this line, you see that I related or joined these tables
based on the country name and so that's why those two items
highlight. Let's go back and click into report and let's go ahead and start
building some visualizations to help us make decisions based on our business data.
The first thing I want to do is I have this blank canvas and I should give a
little bit of context when people look at this report. So what I'm going to do is when
I click on the home option on the ribbon, there's an option to insert a text box.
I'm going to click on that and let me insert a title for this report
and maybe I'll go ahead and call this the Kevin Cookie Company performance report.
I typed in the name. Let me make it a little larger just so it helps
frame the entire report that we're going to create,
and I'm going to go ahead and expand it so it spreads across the entire report.
OK, we have the title on here, things are looking good, but we need to get some visualizations in.
The first thing I want to look at is what was our profit by date.
To go over to the right-hand pane on the side,
and I'm simply going to click on profit. Now Power BI pulls in a default bar chart.
It predicted that this was the most likely one that I wanted to use.
We could still change that, though if it doesn't align exactly with what we wanted to create,
and I also want to align it up with the date.
So I'm also going to click on date over here within the fields pane.
Now I see it shows it by year so in 2018 we had about $650,000
of profit. When I hover over the item, I see a tooltip that shows me the exact value.
And when I hover over 2019, I see that we had a little over
$2,000,000 worth of profit. Over on the right-hand side,
let's say that I want to see the profit data not by year, but I want to see it by the month.
So what I could do is I'm going to click on this drop down,
and instead of doing date hierarchy, I'm going to switch it to date
and now this will show me the month-by-month and year view of what profit was like.
Now bar chart works OK for this type of analysis, but I actually prefer
a line chart and to do that I simply go up here in the visualization pane
and I can click on line chart and that'll switch the type of this chart.
Now this looks pretty good. I'm going to go ahead and just make
it a little bit smaller and then move it up on my report.
So here now I could see my profit by date.
When we look over at visualizations, you'll see a few other things. Here in the axis, Power BI
automatically identified that the date should be the axis and the profit should be the values.
Now you can move things around as you see fit.
You could also add things to the legend if you want to do that.
You can even add secondary values here.
So you can define what the different elements of the chart are.
The next thing that I want to do is I want to create a view of all of the geographies
where we have locations and I also want to see the profit by those geographies.
To do that, first off I'm going to click on country.
Now here too, Power BI identifies that it's country data and it automatically
inserts the map visualization.
And here you see a map now.
Now I see dots where I have locations, but I want to see profit data by those different countries
and so here too what I could do is I'm going to click on profit and I can even drag that over,
drop it on my map and now I see visuals that represent how much profit by country. So here
it looks like North America, Canada, and also France all had about the same amount of profit,
while Germany and Mexico are smaller dots indicating that there was less profit there.
I could also once again if I want to switch the type of view, I can switch it to a tree map
and here I can see profit by country this way as well.
So here once again I could see which country had the most profit all the way down to which
country had the least, and now it turns out most of these countries are pretty similar,
but here I get the sense of what was the most all the way down to what was the least.
I'm going to switch back to the map view.
The next thing I want to do is
insert a table that'll show me the population and then the units sold for that population.
So here I'm going to click on table.
This will insert a table.
Now when I click on the visualization first without selecting any of the data,
it'll simply insert a blank table.
Let me pull it up here.
I could easily drag and drop things around to organize my report how I see fit.
Here what I'm going to do is I'm going to pull in the population,
so here I have a field for population, country population.Let's go ahead and drop that in
and then I also want to pull in the different countries.
Now I can see the country, the population for that country.
The next thing I want to do is also how many units were sold for each of those countries.
Here now I have a table and let me go ahead and I'll position country first.
So here now I can see the country, what the population of the country is,
and I can also see how many units were sold for that country.
Now, a little beyond what we're going to do today, but some of the things you could do.
You could also insert calculated fields,
so you could do things like per capita, how many cookies did we sell?
You could do those types of calculations given the data that you have existing,
but that's above and beyond what we're doing today.
The last thing I want to do is insert a bar chart
and for the bar chart I want to look at sales by product.
So let's go ahead and I'm going to click on the blank report again, and here I'm
going to click on product and next I want to click on the profit for each of the products.
And let's go ahead and change the type and I'm going to go with a bar chart.
Here you see a simple bar chart that I inserted and it shows me all of the
product names and the associated profit for each of those products,
so here I can see that chocolate chip cookies generated the highest
amount of profit and then the Fortune cookies generated the least amount of profit.
Now that I've inserted all of these different visuals into my report,
it's also very easy to slice the data.
So here I can bring in a slicer. I'm going to insert that
and then I'm going to drag and drop the country name on the slicer.
So here if I just want to view data for the United States, I could click on the United
States and you'll see that all the visuals now adjust to reflect just that country.
Similarly, I could click on Mexico and that'll shift to Mexico and I could go through and slice
my data by country. So very quickly I could see my profit by date by country, I could see the
population and then I could also see which product was the most profitable very quickly simply by
selecting the market. Another way I could also slice the data is rather than inserting a slicer,
I could also click into my different visuals. So here if I click on the map on the United States.
This too also serves as a quick way to slice my data, so here I could just see Germany data,
I could see France data, or here I could see Mexico data.
So very quickly I can adjust my, I can very quickly slice my data.
Here if I just click on the map in let's say a random area like the Atlantic Ocean,
it will then turn on all the different countries again. Even in the table too, I could click on a
country here and that'll filter it by that country and once again I could go ahead and undo it.
I have a whole bunch of really valuable information in this report now.
I also want to show you how you can change the look and feel of it.
This report doesn't look all that great, so let me go ahead and see how I could change this.
If I go up to the ribbon and I click on view under themes,
I have all these different themes that I can apply now.
Maybe the executive theme would be a nice one, so let me click on executive and here you'll
notice that the colorw changed and it looks a little bit more professional in my opinion.
I can also adjust the look of my different visuals that I have within this report,
so if I click on the visual over here under visualizations,
the initial view is the field view. But here I could click on format.
It kind of looks like a paint roller, and if I click into that, I can adjust
all these different elements of the visual. For instance, let's say I wanted to make the title
a little bit bigger, I could click on title and then I could go ahead and increase the text size,
and so maybe I make it a 20-point font just so it stands out a little bit more.
Maybe I want to call out this one and I could go through and
modify the titles on the other ones as well.
All-in-all, with these different tools that you have under the format tool,
you could go ahead and get the visuals to match your company or your business’ brand and look
and feel. Feel free to go ahead and play around with it and get it to how you want it to look.
I've gone through and I have this report. It looks pretty good if I wanted to add, say,
additional pages to my report, I could also go in and I could add more pages. I could also go ahead
and rename this page if I wanted to, but for now I think page one, that sounds fine. Once
I'm done making all of my edits, I want to go ahead and save this and to save it I'm going
to click on file save as and let me go ahead and for now I'll save a copy on my desktop.
I'm going to call this Kevin Cookie Company Report and then click on save.
Now I can use this on my own just fine. I could analyze the data,
I can create different views on my data and I can work in isolation.
But really, the power of Power BI comes from being able to work
with others and being able to publish reports so other people can see it,
and other people can interact with the different views that you created.
To share the report with others, first we need to publish it, so let's go up to the file menu.
We're going to go down to publish and then we're going to publish to Power BI.
The Power BI service is what houses all of these different reports that we publish, so I'm going
to go ahead, let's click on Power BI, and it says do you want to save your changes? I'll go ahead
and save the changes again before I publish, and then it asks me to choose a destination.
I'm going to go ahead and select my workspace.
Look at that! It looks like it was successful at saving it to the Power BI service. Now what
I could do is if I click on open Kevin Cookie Company Report pbix, I could open it in Power BI.
Let's go ahead and open it and see what this looks like. This will open up the Power BI
service and here I can see my Kevin Cookie Company report on powerbi.com and here too I
could go ahead and filter the data just like I did in the Power BI desktop application.
The nice thing about putting it on the web is if I want other people to come in and to see
my visuals that I created, I can go up to the top right-hand corner and I can click on share.
This opens up the share dialog and I have all sorts of different options here.
I can go ahead and grant access to different individuals by entering in their email address.
I could also go ahead and I can allow them to share the report with others.
I can let them create new content based on the underlying datasets,
and I can send an email notification and if I make any changes it'll include it for them.
So here I could define what the permissions are when I share this with other people.
All right, well, that was a quick look at how you can get started using Power BI.
Once again, that was a high-level overview.
If you want to see me go deeper into Power BI, leave comments down below.
Let me see what else you'd like me to jump into and I'll add it to my list of videos to create.
If you found this video helpful, please give it a thumbs up.
If you want to see future videos like this, hit that subscribe button,
that way you'll get a notification anytime new content like this comes out.
All right, well that's all the content I had for you today,
I hope you enjoyed it and I hope to see you next time, bye.