[MUSIC PLAYING]
Welcome to E-DAB video number 10,
Data Analysis and Business Intelligence
Made Easy with Excel Power Tools.
And in this video, we want to see
how to use Excel and Power BI Desktop together.
Now there's three main things we'd like to do in this video.
What if we have a data model with imported tables,
relationships, DAX formulas, and hidden columns
already prepared in Excel?
What if we could just send it over to Power BI Desktop?
No problem.
That's easy to do.
Then, in step 2, we'll build our report
in Power BI Desktop using that data
model we imported from Excel.
Then, step 3 and 4 will actually take both files
and publish them to powerbi.com, which
will make it easy to share our reports with others.
Below this video, you can download the zip folder
and right click Extract All.
Now this is the Excel file with a data model.
But it is connected using Power Query to this source data file.
And we'll go look at this data model in just a second.
And we're going to send it automatically
to this empty Power BI Desktop start file.
Now here are the amazing 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 we're here in the Excel file with the data model.
So in Power Pivot, if I go over to the data model
and over to Diagram View, I can see
the imported tables, relationships, measures,
and hidden columns.
If we close the PowerPivot for Excel window,
back over to data queries and connections,
all of these queries are looking into the Excel source file.
Now the cool thing about everything in this Excel
workbook that's part of the data model,
we can bring it all into a Power BI Desktop file.
Now this Excel file does have reports on different tabs based
on that data model.
When we import everything into Power BI Desktop,
it's not going to take all the things on the sheets, just
everything from Power Query and Power Pivot.
Later, when we import this file into powerbi.com, then online,
we can look at our reports from the Excel workbook.
Now I'm going to close this Excel file.
Now here is the start Power BI Desktop file that I have open.
And notice, we don't have any tables,
nothing in Relationship View.
Here's how we can get our Power Query and Power Pivot data
model from Excel.
File, Import, and there it is--
Power Query, Power Pivot, Power View.
Now, Power View is another feature in Excel.
But we have Power Pivot and Power Query.
So I'm going to click, and there's the file
we were just looking at.
I'm going to double click.
Now this message is not so helpful.
It says, we don't work directly with Excel workbooks.
But we know how to extract useful content.
That just means Power Pivot and Power Query.
I'm going to click Start.
And there's a list.
Those are all the queries from Power Query.
Scroll down, there's the tables.
And here are our measures.
I'm going to click Close.
And this is absolutely amazing.
I'm going to go to Relationship View, and look at that.
All of the columns are hidden.
There's all of our measures, our relationships, and our tables.
We can also go up to external data and click Edit Queries.
Here's the Power Query editor.
Here's our queries.
If we select Sales, go over to Source, click our little icon,
we can see that Power Query is actually
pointing towards that E-DAB 10 Excel file source tables.
So when you download everything, if you
want it to point to your file on your computer,
you'll have to edit that step.
But notice it did import that connection.
Click Cancel.
Click Close.
Let's go over to Report View.
I'm going to double click, call it gross profit percentage.
I'm just going to quickly create something.
Click in the white.
And we want a combo chart right there, line and clustered
column.
From the date, we have a date hierarchy, shared axis.
From fsales, Total Sales, Column Values,
Gross Profit Percentage, Line Values.
Resize, expand all down.
Click in the white.
Clustered column.
Down in dproduct, Check Product, Total Sales.
That's our one page.
We could definitely create more pages.
Control+S. Now we want to close this.
And now we have an Excel file and a Power BI Desktop file.
I'm going to type powerbi.com into a browser.
If you have an account, sign in.
If you don't, sign up for free.
If we click this button, well, first, it
reminds you to download Power BI Desktop, because that's
where we can create on our computer various reports.
Now we want to scroll down here.
And I'm going to show you Power BI Pro.
And you should try the free 60 day trial.
Try free.
You can go ahead and sign up.
Now I'm going to sign in.
Now here we are at powerbi.com.
And we want to upload two files and try and share them.
We're going to try and upload them to a workspace.
If I click this arrow, we're going
to try to upload our two files to the GelBoomerang workspace.
Workspaces are convenient because you
can load multiple files, reports, and data sets,
and then share them with others.
If you want to create your own, click Create App Workspace.
Name the workspace.
Decide if you want it private or public.
And members can edit the BI content,
or they can just view it.
And here's where you share.
You add emails.
They are notified that they have access to the workspace.
All right, we're going to try and upload to the Gel
Boomerangs Workspace.
Now we're in this file here.
And of course, we have our data model.
And we have one page, but we could have many.
And they would all upload.
In Power BI Desktop, we simply click Publish.
It wants to know what workspace.
Select Gel Boomerangs.
Click Select.
Now we can click this link to jump over to powerbi.com.
Or, we could go to our website and log in.
I'm going to click this link.
Wow, and look at that.
Here it is online, shareable and everything.
I can click on a column and it works.
Click on the column again and deselect.
Now we can share a workspace, as we saw.
And that would share everything in the workspace.
But if we wanted to share just this report,
we can use this Share button.
I click.
I can enter email addresses, an email.
I can check this to send an email,
and then click Share to share that with those people.
Also, you can email this report link directly.
So we can share an individual report or a workspace.
Now I want to click on the Gel Boomerangs workspace.
Dashboards, we'll talk about what
those are in just a moment.
Reports, we have one Power BI Desktop file.
Workbooks, we haven't uploaded anything from Excel yet.
And data sets, that lists the data model from our Power BI
file.
We can also access the different things stored in a workspace
by clicking the dropdown arrow--
Reports, Data Sets.
Now we want to go upload a workbook.
Now we've opened this Excel file.
And there's two things that we can upload.
We can actually upload and view all of the sheets.
And it doesn't matter whether they were created with Excel
spreadsheet items or a picture was inserted,
or they were a report created from the data model.
We can upload and view these sheets.
We can also, over in data model, upload the complete data model.
The way we publish in Excel is we go to File, down to Publish.
And there's two options.
The first option, Upload, allows you
to view each tab in the workbook in Excel Online.
It also will upload the data model.
The second button, Export, uploads the data model only.
Now we have to make sure and select the workspace.
I'm going to select Gel Boomerangs,
and we're going to use this first option.
I'm going to click Upload.
And look at this down here, it's publishing.
Above the formula bar, workbook upload successful.
Go to Power BI.
Now you can click this button, or you
can log into the website.
Both will get you to the same place.
I'm going to click.
It brings us over to powerbi.com.
And look at that, it opened our Excel workbook in Excel Online.
I can click on any of the sheets.
And this is made from the data model.
And I can interact with the dashboard.
Over to year-to-date sales, select Kites.
And there, I filtered my report.
Now let's look at our workspace.
No dashboard yet, but we have a report.
That's our Power BI file.
Ah, there's our Excel file under Workbooks and Data Sets.
Now we have two data sets.
Now why are we uploading data sets, when in fact, we
have a report from Excel and our report from powerbi.com?
Well, we can share those.
But guess what?
From these two data sets, from either Excel or Power BI file,
I can click Create Report from either one of these.
And I can create a new Power BI report here in powerbi.com.
So let's try it from the Excel file.
I'm in to click.
And you've got to be kidding me.
Here's the visualizations from Power BI.
I'm going to select Column, Products, Fsales, Total Sales,
Total Cost of Goods Sold.
That's incredible.
I uploaded a data model from Excel to powerbi.com.
And now I'm building a report.
I can click Save up here, or I can use Control+S.
And this is going to create a new report.
We created this report here in powerbi.com
from that Excel data set.
I'm going to name it, click Save.
Now when we look at our complete Gel Boomerangs workspace,
I can click the dropdown now.
We still don't have any dashboards.
But look at that, we have two reports--
the one we just created, the one we
uploaded from Power BI Desktop.
There's our Excel workbook.
There's our data sets.
I can also click on Gel Boomerangs, Reports--
there's two of them now--
Workbooks, and Data Sets.
There's two data sets.
Now we're going to talk about dashboards.
Now, dashboard is a word we use all the time.
We use it in Excel when we build a bunch
of visualizations and pivot tables on a sheet.
We use it over in Power BI Desktop
to describe what we do on a tab.
But over here in Power BI Online,
it has a very specific meaning, and it is amazing.
We can go to any one of our reports
and select the entire page, or an individual visualization,
and pin it to a dashboard.
That means we can pull from this Power BI file or this Excel
workbook file, and pin elements from each into a single place.
I'm going to start over here.
Now we have a single sheet here.
And if I want to pin the entire sheet,
I come up to Pin Live Page.
Now we're going to name this dashboard E-DAB-10, Pin Live.
I'm going to go over to the workbook.
I'm in Excel Online.
Click on average sales report.
We want to select just the chart in this worksheet.
Come up, and it's up in the corner here, Pin.
I'm going to click Pin.
Yes, I want to send it to E-DAB and pin.
Now I have this dashboard I can select.
And look at that.
It's interactive.
This is from the Power BI file.
And look at that.
It's not interactive.
But if I select it, it'll jump over to that Excel workbook.
And you can go through the different reports
and pin as many different things as you want to this dashboard.
All right, that was a lot of fun with Excel and Power BI.
We started off in Power BI Desktop.
Learning about the import from Excel,
we can go get Power Query and Power Pivot.
Then, once we have the Excel data model in Power BI Desktop,
we created a tab, published this in Excel.
We published using this button here.
And then over in powerbi.com, we learned about workspaces.
We have reports from Power BI, some files from Excel,
data sets that we can work with.
And we can pin things to dashboards.
Now over here at the Excel Is Fun channel here at YouTube,
you just completed this entire playlist of 10 videos
all about Excel, data analysis, and BI basics.
Now the next class you move on to
is MSPTDA, advanced data analysis.
19 advanced data analysis videos and counting.
All right, if you liked that video,
be sure to click that thumbs up, leave a comment, and subscribe.
Because there's always lots more videos to come from Excel
Is Fun.
We'll see you next MSPTDA video.
[MUSIC PLAYING]