E-DAB 09: Power BI Desktop: Data Modeling & Interactive Visualizations


Welcome to E-DAB
video number nine, Data Analysis and
Business Intelligence Made Easy with
Excel Power Tools, except we’re not using Excel. We’re going to use
Power BI Desktop. Now, the good news
is, everything we learned in last video
over in Excel Power Pivot, it’s virtually identical over
here in Power BI Desktop. And the big benefit of jumping
over to Power BI Desktop is that it has interactive
visualizations. And here’s what I
mean by interactive. If I click on the map
on the United States, instantly the other elements
in our report update. Click off to the side,
back to not filtered. If I switch from
competition to beginner, and I want to look at what
happens in May 2016, June, all the way to December, no
problem in Power BI Desktop. You can download the files
for this video in the link below the video. When you download
this zip folder, right click, Extract All. These are all the files that
you need to follow along. Inside this folder
is all the data we need to import
into Power BI Desktop. There’s the PDF notes
with written summaries from the video, even a
few homework problems. Now here are the
topics for our video. And be sure to look
below each video. Now this is Excel Basics 1. But below our video, if you
look for that Show More button and click, there’s a time
hyperlink table of contents. If you like what you see,
click that Subscribe button and the bell icon. Now here’s the overview of
what we’ll do in this video. We’ll first have to download
Power BI Desktop, which is a free download from Microsoft. And we’ll look at the
links in just a moment. Then we’re going to have
a lot of data coming from different Excel workbooks. So we’ll have to point Power
Query, which is inside of Power BI Desktop, to a folder. But unlike earlier
in the class when we imported from text
files, in this video, we’ll get to see how to
import data from lots of different Excel files. Then we’ll do our
data modeling, import the tables, relationships, hide
the columns, and DAX measures. And then we’ll build our
final visualizations. Now how do you choose between
Excel Power Pivot and Power BI Desktop? Well, guess what? Both of them have Power
Query, columnar database for our big data
relationships, DAX formulas. Both are almost identical. Now you might want to
stick with Power Pivot if you’re familiar with Excel. If a pivot table report
is what you want, you might want to go with Power
BI Desktop, because it has more varied visualizations
and reports. And the visualizations and
reports are interactive. Now in Excel Power Pivot, we
can publish the Excel files, but they’re harder to
share on any device. Not with Power BI Desktop. Next video, we’ll
see how to publish. And with Power BI Desktop,
whatever report you create can be consumed on any device. Now, Excel Power Pivot does
not have DAX table functions. In this video, we’ll
see a table DAX formula that can be used
as part of the data model. And here’s a drawback. DAX formulas calculate
more slowly in Excel, but over in Power BI Desktop
calculate more quickly in Power BI. Now, this only comes into play
when you have very big data. But if you’re working
in Excel and things are slowing down too much
as we’ll see next video, you can actually send the data
model over to Power BI Desktop and see if that works faster. I switch from Excel Power
Pivot to Power BI Desktop when I want
visualizations in Power BI rather than a pivot table,
I want interactivity between the visualizations
and reports, or I have too much data and I
need faster calculation times over in Power BI. Approximate history
of Power BI Desktop. In Excel in 2009, that was
the first Power Pivot add-in. That’s where we had our
first glance of the columnar database, DAX formulas,
relationships, and the data model. Power Query was debuted in 2013. Then, all of these
power tools were refined in Excel between 2009, 2015. And then in 2015, Microsoft
combined all these tools together and gave it away for
free called Power BI Desktop. Now there’s different
versions of Power BI. Here’s the Microsoft
website that describes each one of
the versions, Power BI Desktop, Power BI Pro,
and Power BI Premium. Now, we’re going to use
the Power BI Desktop. This is where we connect
to multiple sources, clean and transform our
data, do our DAX formulas, and build our dashboards. But in terms of publishing,
with the free version, we can publish to the
powerbi.com website. And there’s some great
services once we publish it, like printing, creating
PowerPoint slide, and even creating the
embed code that we can use in our own website
that’s viewable to the public. But what the free version is
missing is when we publish it to powerbi.com, it’s
not like the pro version where we can publish it and have
others view it on any device. Also, when we publish
it here, if you don’t want to publish
it to powerbi.com but you want to
keep it on premises, then you want the
premium version. Now, we are going to try the 60
day free trial, because we want to see how amazing it is to
publish our powerbi.com reports and view it on any device. But if you only have the free
version, well, guess what? We can still share
two different ways. We can do it the
old-fashioned way. Simply email the .pbix file,
and then the person can simply download the free
version and view it. And of course, you can
use the embed code option. Now, downloading
Power BI Desktop– guess what? There’s two
different ways we can download it, Microsoft Download
page and Microsoft Windows Store. But here’s the deal. I’ve been downloading
for the last few years from Microsoft Download page. And that means, every
month when they update it, I have to go and re-download
and reinstall it. If, however, you use the
Microsoft Windows Store link to download, then you get
automatic updates each month. After you download
Power BI Desktop and open up a blank file, you’ll
see this, because Power BI Desktop updates each month. This is really handy. Click this and go see
the latest updates. I’m going to close this. Up in the title bar,
Untitled is not a good name. I’m going to use the keyboard. F12. That opens up the
Save As dialog box. I’m going to name
it something smart. And notice, .pbix. That’s the file extension. Let’s click Save. Now, everything is going
to be in a different place and look different over here
in Power BI Desktop compared to Excel Power Pivot. But internally, almost
everything is the same. So for example, instead
of the Get & Transform group over in Excel, Power
Query is in the external data. There’s our Get Data, where we
can connect to different data sources. Over here on the
left, if we hover, we can see Report, Data, Model. This report area, this
is a blank canvas. Over here, we can
see visualizations. We’ll use those later. Now this white area here
is like an Excel worksheet. It doesn’t have cells, but down
here we can see there’s a tab. And this is where we’ll build
our reports and visualizations. We can have different tabs
with different visualizations on each tab. In Power BI Desktop,
each tab is called a tab. All the tabs together
are called a report. Now we don’t have in the
data group any tables, but this is where
we’ll see our tables. This is where we’ll
build our relationships. So step one, we got to
go and get our data. It doesn’t matter which
area we start from. This is eventually where the
tables are going to land. So here’s external data. Come up to get data. Well, we have lots of
Excel files in a folder. But wait a second,
I don’t see Folder. Down here more. Now I’m going to click More. There’s the Folder option. But before we jump in,
let’s go look at our data. Here are the Excel files. Let’s open up 2014. On the sheet 2014, we have a
proper data set with our field names in the first row. Now, control down arrow. Wow, almost 90,000 rows of data. Control Home. Now, this is an Excel file. This is not going
to be like when we imported text files from a
folder earlier in the class. Inside an Excel
workbook file, we can have many different objects. Now for us, we have
control over our data. We know that in every
single Excel file, there’s exactly one object. That means there’s one sheet. Each sheet has a
proper data set. Now we’re going to close this
and go back to Power Query. Back here in Power Query
in Power BI Desktop– Folder, Connect, Browse. We’re pointing Power
Query to this folder. It will import
every file it finds. Click OK. Click OK. Click Edit. Not Load or Combine, Edit. Now this is the
Power Query editor over here in Power BI Desktop. Step 1, let’s name this. fTransactions and enter. Now, just like with our text
files earlier in the class, when we use From
Folder, it gives us a list of every single file. Clicking off to the side, not on
Binary, in this Content column, I look down here. There’s an Excel file. There’s an Excel file. Now we want to remove all the
columns except for Content. So right click Content,
Remove Other Columns. And what we do not want to do
is click that Combine button. These are Excel files. And because there can be
many potential objects, that button is not
going to work for us. We’re actually going to
have to add an extra column and use the
Excel.Workbook function. So we go up to Add
Column, Custom Column. We’re going to call this column
something like, GetExcelData. Down in the custom formula,
this is the first time we’re going to use a
Power Query function. Excel.Workbook,
open parentheses. Now, Power Query functions
are case sensitive. You have to have capital E and
a W and the rest lowercase. Now remember this Content
column has Excel files. And what Excel.Workbook will
do is it will, from each row, get all of the objects
in each Excel file. So I’m going to
double click Content. Now because we have
proper data sets and we want to
promote headers, that means over in that Excel file,
the first row were field names. So in the second argument
of Excel.Workbook– comma– we have to type lower case true. That tells Excel.Workbook,
when it gets one of the tables, to promote headers. Close parentheses,
and that will work. Click OK. Now I can click off to the side. And down here, this
is a table that lists every single object that
it finds in the Excel file. For us, our pattern
is always the same. We have exactly one sheet. Now we need to expand and
list all of the objects, one on top of each other, before
we get to our final proper data sets. I do not need Content. Right click, Remove. Now I can use the Expand button. Click, uncheck Use Original. These are all the columns. Click OK. Now in the data column, if
I click off to the side, there’s my proper data set. That’s the next proper data set. Now, we don’t have
to do any filtering on any of these
other columns, again, because we know that
each Excel workbook has one object, the Excel sheet,
with a proper data set. Now I could delete these
and leave this column here, but watch this. I’m going to double-click
this to open this back up. Because really,
in this case, when we’re sure about one sheet
in each Excel workbook, all I need to accept
is the Data column. Now I click OK. Now we can see our
proper data sets. Now we can use
this Expand button. Click. I definitely want
all of the columns. Click OK. And there, from all
of those Excel files, we have appended,
or combined, one on top of each other to get
a single proper data set. Now we definitely need
to go through and verify the names and the data types. Click the icon for data type. Date, Text, Decimal,
Decimal, Text. And Units are going
to be Whole Number. Now this is amazing. There’s our query. There’s our resultant table
we’re about to load or apply to the data model. There’s all of our steps. Now we come back over to Home,
and this is a difference as compared to Power
Query in Excel. We can simply click
Close & Apply. The close means
close this window. Apply means deliver
this proper data set to the columnar database in
Power BI Desktops data model. So I click Close & Apply. It’s working to
get all of the data from each one of
the Excel files. After the query has loaded,
over here in Data or Table we can see our table. Over on the right, we could see
fTransactions and the columns. If we ever need to
edit that query, we have to go back
up to Edit Queries. This opens up the
Power Query editor. I’m going to close this. If we go look at Relationship
View, we have one table. Now we have two other
tables we need to import. Another one of the files
you’re supposed to download is this one. If we open it, we need
to import the Country table and the Product table. Now we’ll use Power Query to
look inside of this Excel file and choose just
those two objects. Back in Power BI Desktop,
Get Data, Excel file. Click. Select the file. Click Open. In the navigator window, we will
select dCountry and dProduct. Those are the only two objects
that we want to import. Now we click Edit. Over on the left, we now
have three different queries. Select dCountry. Verify names and data type. These are the steps. That name is fine. dProduct. We’re verifying data
types and names. The steps are fine. Now I can click Close & Apply,
and these two new tables will be loaded to
the data model. Click Close & Apply. Now look at this. When they were imported over
here in Power BI Desktop, it’s sensed the relationship
and built it automatically. But we want to verify
that the relationship is pointing to the correct
columns, and they are. Our next task is to create the
date table, or calendar table. Now last video, we saw
that Power Pivot had an automatic button to do that. And that automatic
button over in Excel looked through the date
column in fTransactions and automatically extracted
the correct number of days. We don’t have an automatic
date table over here in Power BI Desktop. But we can go over to
Tables, over to Modeling, and there’s a new
DAX table button. I’m going to click New Table. Up in the formula bar, I’m
going to call this dDate equal– and they named this
function smartly– calendar. Tab. It just needs the start
date and the end date. Well, we have to look through
the entire fTransaction date column and find the min date. fTransactions, down arrow to
Date, tab, close parentheses. Now that’s not
quite what we want, because if the data is
not the first of the year, we need to change it. The way a date table
works is that first column has to have every
single possible day from every single possible
year in the fact table. So I want to use
the date function to construct the correct
first date in the year. So we’re going to use
the date function. We need the year of the
min date, so I type year. Close parentheses. Now comma. Well, the month is
always going to be 1. Comma. The day, well, January
1st, so I put a 1. Close parentheses. So with that
construction right there, we have created a DAX
formula that will always look through the
fTransaction and find the first day of the
year for the minimum date in the transaction table. Now I’m going to copy of
this, come to the end, comma, control v, because
now, we don’t want min. We want to look through
the fTransaction and get the max date. But then the month is 12
and the day is 31, right? Now I’m going to hit Enter. Select the column. Data type, I’m going to
say, please data type, date. I could also add
some formatting. I’ll select this one. Click. So what we did
here is we actually created a table using
a DAX table function, and it was added
to the data model. Over here we can see
dDate table, Date column. Over in our relationships,
there’s our date table. Now we can create
a relationship, and then we’ll create
our attribute columns. I’m going to click and
drag over to fTransactions. So we have one to many
date relationship. Now we’ll go back over to
Table, or Data View, and then the Modeling ribbon
tab, Calculation group. These are our three options. Over in Power Pivot,
we can create measures and calculate the columns. But in Power Pivot, they
don’t have this button. But over here in Power BI
Desktop, we have all three. Now last video, the
automatic date table feature added the columns. We’re going to have to
manually create those. So I’m going to
click on New Column. Up in the formula bar, lets
type Month Number equal, and then the month function. dd, down arrow to
the date column. Tab. Close parentheses and enter. New column. We’ll call this month. And we would like to use
the text function like over in Excel, but that function
doesn’t exist in DAX. To create month name,
it’s called format. It works exactly the same way. dd will get our date tab. That’s the value. The row context– for
each row in this table, we’ll see the particular date. Comma. Format– in double quotes, the
custom number formatting mmm will show Jan, Fed,
Mar, and so on. End double quote, close
parentheses, and enter. Now just to show you– we didn’t
get to see this last video, but we know we have to sort
month using this button here by month number. But let’s go over
to Report View, and here’s our date table. Let’s just drag month into
the white blank area and drop. It defaults to the
visualization table. Uh, oh. That’s not going to work at all. It’s sorting alphabetically. So we come back over
to Data or Table View. Click on month. Sort by column, and I want
to select Month Number. That way, 1 to 12 will
automatically sort January to December correctly. Month Number. Back over to Report View. That’s much more polite. Back over to Data View. New column, and
we’ll create year. dd, down arrow,
there’s our date tab. Close parentheses and enter. Now we could create lots of
other attributes including fiscal year, fiscal quarter,
and things like that. And I have other videos
in my Excel series and the MSPTDA Advanced
Data Analysis series that shows how to do that. But for us, this date table,
unique list with all the dates, and our attribute
columns is what we need for our data model. We go over to Relationship View. That is looking good. Now we need to create
measures for total revenue, total cost of goods sold, total
gross profit, and total units. I’m going to go over
to Data or Table View, select my fTransaction table
up in Modeling, Calculations. Now we click New Measure,
just as we did last video. Total revenue. And watch this. For the calculated column we
just created in our date table, the assignment operator
was equal sign. That’s the same in Power Pivot. A calculated column
uses an equal sign. But a measure over
in Excel Power Pivot requires a colon
and an equal sign, but not over here
in Power BI Desktop. All of our formulas just
require an equal sign. Now we’re going to
have to use SUMX. That’s our iterator. Instead of adding
an extra column like we did last video in Power
Pivot, for line item revenue I want to do it
all in one measure. Because I want to
calculate the line item revenue for every row in
the fTransaction table, in the first argument
of our SUMX iterator, we type fTransactions. Tab, comma, expressions. That’s our whole formula. Now we are going to need to
round each line item revenue, so I use the round function. In the Number
argument of round, I need to take price from
the dProduct table. That means we’re looking it up. Multiply it by number
of units, and then 1 minus the revenue discount. Our exact match lookup function
in DAX is called RELATED. Because there’s a
relationship, I just tell my formula which
column in the lookup table has the thing I
want to go and get. So Retail Price. Close parentheses. For every line in the
fTransaction table, even though we’re in a
measure inside an iterator, that Related can
see the row context for every single
row in that table and always look up
the correct price. Now I multiply. Times fTransactions. There’s our units. Times, in parentheses,
1 minus fTransactions. And the actual number
of pennies of discount assigned to each line, we take
that and subtract it from 1. And that will give us the
net amount of our revenue. Now Number has our full
calculation for our revenue. Comma. We’re rounding to the
penny, so I put a 2. Close parentheses. That’s the full expression
that will calculate for every row in fTransaction. And then the sum part
will add every single one of those values. Come to the end. Close parentheses and enter. Now this is different
than Excel Power Pivot. In Excel Power Pivot,
we build the measure in the measure grid, and
we can see the result. If we look over here and
fTransactions, that’s the icon. That means this is a measure. We still need to add
some number formatting, so I’m going to add
some number formatting. What I like to do is
go over to Report View, and we want to come over
and create a multi row card. This will be like our
substitute for the measure grid. We’ll check total revenue,
and it appears in the card. That’s without any
filter context. That’s just the
grand overall total. Now let’s go back over
to Data or Table View. I make sure that my
fTransaction table is selected. Click on Total Revenue,
highlight formula, control C, escape. Now I’m going to click New
Measure, control V, Total COGS. And we need to
change two things. I don’t need Retail Price. I type in S for Standard Cost. So I’m looking up Standard Cost
instead of the Retail Price. And then I can delete all that. fTransactions. And we want Net Cost Equivalent. And that’s our formula
for Total COGS. Now maybe it’s faster
that you just type it out, but copying and pasting and
changing a few things usually works faster for me. Enter. And look at that. I have a rogue parentheses
and the little red squiggly. It’s like spell
check over and word. Backspace and enter. Now let’s add some
number formatting. Go over to Report View. Click on our card. And I’m going to
check Total COGS. And there it is. Back over to Data
Table View, make sure we have fTransactions. New Measure. Total Gross Profit, equal sign. And our convention for using
measures and other measures is we use a square bracket. So you type a square bracket. And internally it knows
that that’s the convention, so it gives you a list
of just the measures. Total Revenue, tab. Minus, square bracket,
up arrow, tab, enter. Number formatting. Over to Report View. There’s my total gross profit. I check, and there it is. Back over to Data Table View. fTransactions, New Measure. Total Units, and I’m going
to sum from the fTransaction the units column. Close parentheses and enter. I’m going to use Thousands
separator with zero decimals. Over to Report View,
we’ll check Total Units. And there are our DAX measures. If we go look at
Relationship View, we imported table,
created relationships. We have some measures. I need to hide some
columns now, Country Code all the way to Units. Notice we do not want people
dragging Units from the field list over in Report
View, because that would create an implicit measure. If we want a calculation, we
explicitly create a measure. So I’m going to hide
all of these columns. Hold Shift, clicked,
right click. And over here it’s not
hide from client tool, it’s hide in report view. Retail Price, Standard Cost,
right click, hide in report. Country Code, hide in report. Month Number, hide in report. And there we have finished
our data modeling. Now let’s go over and
create our reports. Report View. I’m going to double click
here and type Tests. Enter. Come down here, add a new sheet. We’ll call this Gross
Profit Analysis. Click the plus, double click. We’ll call that one Units
Analysis, and enter. Back to Gross Profit. On this page, we want to analyze
Gross Profit by year and month, product, and by country. Now in our first
visualization, I want to cross
tabulated table that has month and year,
and then gross profit. The visualization for
that is the matrix, so I’m going to
click the matrix. We can move it around,
and sometimes it’s tricky. You want to usually point
to the outside edge. And we don’t have
the same move cursor as we do in Excel and Word. I just see my
diagonal white arrow, and I can click and drag. Now from dDate, we’re going
to drag Month over to rows. And I see my months. Now we want Year in column. There’s the year. fTransactions. Notice over here in
Report View, all I see is the icon for my measures. I can check Gross Profit, and
it throws it down to the values. Now we can resize it. And for each visualization
that we create, if we look over here, this is fields. This is where we drag
and drop the fields, kind of like a pivot table. But unlike a pivot table where
you select different elements and then format, over
here in Power BI Desktop, that paint roller
is the format area. I click, and here’s all
of the different sections or parts of that visualization
that we can format. For example, I could go to grid. Come down to tech size 8. I’m going to increase it to 9. At any time, you can come
and format whichever element. Now in this visualization,
this is different than Excel. If I select May 2015,
it highlights it just as if it was a cross
tab conditional formatting. If I select the full year,
I can see the full year. Off to the side, and
it shows everything. All right. Our next visualization. And if I mistakenly keep the
first visualization selected and select Bar, it will
convert that one to a bar. That’s not what I want,
so control Z. Make sure and click in the white. Then come over and
select our bar. And this is going
to be our product. So from dProduct, I
want product in axis. And then Total Gross
Profit I’m going to check. And there we have our bars. Resize. So if this visualization
is selected, I can come over to
the paint roller. I want data labels, so
I’m going to click On. Notice this shows
up in millions, same with our axis down here. We had to do custom
formatting over in Excel. Now I actually want
to change these, because if I select 2015 in
this other visualization, will that’s amazing. It totally filters
the bar chart. But I don’t want these
0 millions over here. Deselect everything. Make sure Bar is selected. Then in data labels,
I click Expand. And under Units, I’m
going to try Thousands. Now if I come over to 2015,
that’s looking better. Select off to the side. Now I’m going to click in the
white and we want Country. And the visualization
we’re going use is Map. And there it is in dCountry,
we only have Country. I’m going to click. And then over in Total
Gross Profit, check. Total Gross Profit by Country. Now if I click United
States, look at that. It’s highlighting just
the certain amount within the context of the
full Product column height. Now, this gets even
cooler, because we actually have a choice. Do we want it to
highlight like this, or do I just want to see
the actual filtered columns? And the way we do that is
we select the visualization, up to Format, and we
click Edit Interactions. Because the map
is selected, I can choose for the
other visualizations how I want to click
in the map to effect the other visualizations. Now, currently, it’s highlight. But if I change it to
Filter, then it actually shows me the filtered column. Down here, Brazil, this is
another big boomerang country. World championships were
there a few years ago. There we go. Down to Australia. You’d think Australia would
be bigger for boomerang sales, but there we go. So you choose
Filter or Highlight. Up here for this cross
tab, we can either have it filtered or we
can not have it filtered. That would show the grand
totals all the time. I’m going to choose Filter. And you can select
each visualization and choose how you
want it to interact with the other visualizations. Now I’m going to select
the map, come over to Format, down to Title,
scroll down, and increase text size to 11. Same with our bar. And you could add some other
formatting if you’d like. All right. The visualizations
on this tab are done. Let’s go over to Units Analysis. All right. We want a line chart that
shows units by products over the years and months. So we come over. There’s our
visualization line chart. Now I want to have along
the horizontal axis, year and month. And there is a feature
inside of Power Pivot and in Power BI Desktop
that makes it convenient when you have multiple fields
you want to drag and drop. Now over in Power
Pivot, you have to highlight both
year and month, and then right click
Create Hierarchy. Now over here in
Power BI Desktop, it’s a totally different method
for creating a hierarchy. Watch this. You click and drag
month, and you simply drag it on top of year. And when you see that dotted
yellow line, you let go. Now I remember the first time
I did that by accident, I was like, what? But that’s how you do it over
here in Power BI Desktop. All right. So now we can simply drag our
hierarchy and drop it in Axis. Now we want Product
over in the Legend. And there’s our
Total Gross Profit. I’m going to check. Now the legend’s
at the top, so I’m going to come over
to Format, Legend, and let’s put it on the left. Click in the white. We want a Slicer. Check Category,
resize it, move it. So competition. Whoa, we have Gross Profit. I don’t want that. I’m going to select the
line chart, uncheck. Let’s select Total Units. That’s much better. And look at this. Here’s a great feature. We can hover at any year point. That tells us the year totals
for each one of those products that belong to the
competition group. I can click the eraser. In the upper right hand
corner of our chart, we have some interesting
buttons for our hierarchy. Go to the next level. If I click, that will
show me, for example, all of the October’s
for all of the years. There’s the total. Drill back up, expand all down. When I click this, that
shows me year and month. I can hover at any point
and see the totals. There’s also drill
down, if I select this. Or I don’t have
it selected, when I select on a particular month
and year, it just highlights. Click off to the side. But if I have drill down on, now
I can, when I start at the top, drill down by
clicking in the chart. Those are the unit totals for
products for the single month, October 2017. I can come over and
select my slicer. The competition group I
can see up in the legend. There are the
October 2017 totals. That is a pretty amazing
interactive chart. And that was a pretty
epic introduction to Power BI Desktop. We saw how to create various
interactive visualizations. We saw how to drag
and drop fields. We saw how to use the paint
roller to add formatting. This was our Unit Analysis
over on Gross Profit. We also, in Modeling, saw how
to use DAX formulas, Table, Column, and Measures. Over in Relationships,
we saw how to create our
relationships, hide columns. And back over in
home, if we ever need to get back and
edit our Power Query, there it is right there. Click, and there’s our
Power Query editor. Now next video, we will see how
to use this button right here where we can publish and
then view our Power BI reports online and share
on different devices. We’ll also see how to
share a Power Pivot report to powerbi.com. All right. If you like that video, be
sure to click that thumbs up. Leave a comment and subscribe,
because there’s always lots more videos to
come from excelisfun. All right. We’ll see you next E-DAB 10.

Comments 47

  • Nice video
    #knowledgeinflow

  • very cool

  • Mike learning Power BI its like dream come true 4 years ago!!! I remember asking you had tutorials on Power Pivot!

  • Great video as usual, thanks Sir.

  • Great tutorial! Thank you, Mike! Question: Is it possible to show "top 10" in the drilldown when you hover over the line chart? For example, if you want to know what was the biggest impact on the total…

  • Thanks again Mike this EXCELlent video. You are the best, you know that 😛

  • Hey Mike, thanks for the great video.

    To automatically populate a calendar based on the dates of the dataset there is also the DAX function CALENDARAUTO()
    To retrieve the month name I also use the syntax CalendarTbl[Date].[Month] instead of using the FORMAT function.
    There are just different ways of achieving the same thing

  • dear professor
    Hello. is it possible to find a function like Excel.Workbook for other type of file. such as DBASE IV files with .dbf extension

  • PowerBI is awesome! Thanks Mike!

  • Good video! One note. You don't have to click in the white space to deselect a filter or highlight. Just reselect the same item again and it will toggle off.

  • i wonder if your 1 dislike is the same person who always rushes to watch your video (seems like it always happen pretty early!). Power BI looks awesome!

  • Thank you Mike, I really liked the comparison between Excel and Power BI, of course this very dynamic, since both programs change over time. I remember Mynda Treacy showed a nice visual to compare this but I could not find it on her site. But I found this table somewhere else (with a good reference by the way, so that's polite…) : https://wmfexcel.com/2017/07/12/the-awesome-dashboard-course-by-mynda-is-closing/

  • Thanks Mike

  • Great comparison. I have no problem with MS offering Power BI for added visualizations and publishing. Obviously that want to profit from certain types of users. That is their right and its OK. BUT I do not think that Power Pivot and Power BI should have any differences in format, appearance, or available functions. MS should make them identical. MS should get its act together in providing calc engines for excel. If they need multiple calc engines that are contextually activated for different situations so be it. They have the smarts and financial resources to do it. Further, when they introduce a new function in Power BI it should also be available in Excel. Publishing options should be the ONLY difference between these products. Further, MS needs to stop patching Excel and provide full integration of legacy, DM and DA components. Office 365 is profitable enough to justify the investment. My real concern is that there will, over time, be a growing divide between Power Pivot in Excel and in Power BI. That will be a pain.

  • It's very nice ..
    I want to ask you, if I want to add same new column in every excel sheet then i need to import it in power pi.. how that?!

  • Great THANKS Mike

  • Thank you Mike, empowering 💪

  • Ooooo! Power BI! Cool! Thanks for this awesome video 🙂

  • Really needed this. Thank you Mr Girvin!

  • Thanks Mike

  • Wow ..Just amazing

  • Amazing.I Always learn From this Most professional You tube channel. Excel is Fun.Many Working Professional Watches Your Videos Carefully In India.

  • Thank you for this video, as always very well put together and insightful. A wonderful introduction to the capabilities of Power BI

  • amazing job

  • Thanks for this high class video , Professor 🙂

  • ExcelIsFun
    I need help: I get error #VALUE! in the following formula when I have blanks cells in range A1:A4?

    Version 1:
    =TRUNC(SUMPRODUCT(LEFT(A1:A3,FIND(" ",A1:A3)-1)*10^(MATCH(RIGHT(A1:A3,2),{"KB","MB","GB","TB"},0)*3))/(10^(INT((LEN(SUMPRODUCT(LEFT(A1:A3,FIND(" ",A1:A3)-1)*10^(MATCH(RIGHT(A1:A3,2),{"KB","MB","GB","TB"},0)*3)))-1)/3)*3)),2)&" "&INDEX({"KB","MB","GB","TB"},INT((LEN(SUMPRODUCT(LEFT(A1:A3,FIND(" ",A1:A3)-1)*10^(MATCH(RIGHT(A1:A3,2),{"KB","MB","GB","TB"},0)*3)))-1)/3))

    Version 2:
    =TRUNC(SUMPRODUCT(A1:A4*10^(MATCH(B1:B4,{"KB","MB","GB","TB"},0)*3))/(10^(INT((LEN(SUMPRODUCT(A1:A4*10^(MATCH(B1:B4,{"KB","MB","GB","TB"},0)*3)))-1)/3)*3)),2)&" "&INDEX({"KB","MB","GB","TB"},INT((LEN(SUMPRODUCT(A1:A4*10^(MATCH(B1:B4,{"KB","MB","GB","TB"},0)*3)))-1)/3))

  • Thank a million!!!

  • I like that you frequently pointed out differences between Excel PowerPivot and Power BI because although they can do the same thing they also have subtly different function names , syntax, and formatting options. Another great video…thanks!

  • Great comparison between Excel and Power BI

  • Thank you Mike… good thing you have the files we can download… thank you for taking time doing this for everybody.

  • amazing videos!

  • Thanks Mike. Very Interesting 🙂 🙂

  • make a power bi series guru. I follow guy in a cube for power bi. However, i would love to see some videos from you also.

  • Beautiful . thanks for sharing Power bi Fun.

  • I love the whole series! Seems like a refresher but I love re-learning!

  • Topics:

    1. (00:15) Introduction to Power BI and Interactive Visuals

    2. (01:14) Download Files

    3. (01:56) Overview of Power BI project

    4. (02:38) Excel PowerPivot or Power BI Desktop?

    5. (04:20) Power BI History

    6. (04:51) Different Versions of Power BI

    7. (06:33) Download Free Power BI

    8. (07:07) Open Blank File and name “.pbix” file.

    9. (07:39) First Look at Power BI Desktop User Interface.

    10. (08:45) Import Data into Power BI Desktop. Import many Excel files from a folder. Use Excel.Workbook Function.

    11. (15:21) Import Dimension tables from single Excel Workbook.

    12. (15:57) Create Relationships between Dimension & Fact Tables

    13. (16:11) Create Date Table with DAX Formulas. See the DAX Functions: CALENDAR, MIN, YEAR, DATE, MAX,

    14. (21:30) Create DAX Measures for Total Revenue, Total COGS, Total Gross Profit, and Total Units. See the DAX Functions, SUMX, ROUND, RELATED and SUM.

    15. (27:20) Hide Tables and Fields that are not used in Reports and Visualizations

    16. (28:10) Add new Tabs, Sheets, Pages (all synonyms)

    17. (28:26) Create Gross Profit Interactive Visualization

    18. (33:29) Create Units Analysis Interactive Visualizations.

    19. (33:39) Create Hierarchy

    20. (36:17) Summary

  • Hey Really nice and informative videos. Can please make Tableau tutorials as well.

  • thanks a lot, Mike, this video make fall in love with Power BI.

  • This is so cool.

  • Another great one Mike. Thanks for the complete intro to PBID and the comparison to Power Pivot. Thumbs up!

  • if I could attach the pic I would but the DAX formula says the function Related requires a fully qualified column reference while after selecting from the list dProduct[Retail Price] and completing the formula and pressing enter makes the [Retail Price] colour grey and the triangular yellow notification pop up stating the qualification problem aforementioned. Please guide what to do.

  • At (29:22) when I select Total Gross Profit my matrix only shows total value and no months or years. the headings vanish as I check Total Gross Profit.

  • For some weird reasons, I have issues with FORMAT.

    Example: '3/7/2019' is in dDate column. And I wanna get month name out. I tried with MonthName = MONTH([dDate]) and it returns 7.

    But when I wrap it with FORMAT([instruction], "mmm") it gives me Jan instead of Jul. Any advise?

  • Thanks again

  • Hello Mike , again i went to this lesson, that you showed us in detail. At the end, my matrix only shows 29:24 the total result, it doesn't show value of single month at all, would you suggest why this problem occurs. Another question, i know you also have finance lesson. But could you introduce to me a book to understand the term of finance that you are using in these lesson. For example net cost equivalent, revenue,…(i know these are basic, there are some that i search, but for sure that will have more terms), please, instruct me a book. Thank you so much.

Leave a Reply

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