Original subtitles

Welcome to this section on Power BI and working with Excel.

So no matter how well Power BI may meet your requirements, often you may well feel that you would want

to work with Excel.

Excel has got a tremendous amount of tools and capabilities that really are great for working with data

as well, and sometimes you just may find that things are easier in Excel.

So what we're going to explore in this section is just basically some of the ways that Power BI and

Excel can work together.

I'm going to take you through what currently is working with Power BI, but this is an area that Microsoft

continually evolves and changes and updates.

So you may find that they, as you go through in time, that things do update in this area.

Now what I have done is I've gone back to my data model example.

So you might remember earlier on in the course we created all of this data.

So we had a whole bunch of measures and calculated columns that we created.

So let's just have a look at some of the data just to refresh our memory.

So I'm going to create a table.

And you may remember that I used the subregion, which kind of gave me a list of countries that we've

got, and then we had some different fields that we were working with.

So, for example, we had our total sales, we had our total profit.

We were able to work with a profit ratio.

You may remember those figures.

We had things as well, such as the number of customers we had, what was our highest sale, and if

I've got a couple of averages, average sale.

Average sale per customer.

So we had quite a bit of data that had been calculated.

What we're going to do in this example, though, is we're going to see how we could work with Excel

from this.

And the first part of this is I need to actually publish this into the power BI service.

You're going to see that when we work with Excel, we're going to use the Power BI service to help us.

So I'm going to start off with this report.

It's just a very simple table, but we're going to publish it.

And you remember the first part that we need to do with our data model is actually we need to save this.

So what I'm going to do is I'm going to just save this.

So please just go into your file.

Just going to save.

You can see this one here.

I've called data model, but you can give it whatever name you want to, so I'm just going to click

on the save button on that.

So once you've saved this, this is going to be the name of the report.

The next part that we're going to do is we're going to publish.

So we're going to select our publish button.

And you remember from earlier on in the course, we had different workspaces.

Now for me, I'm going to use the training workspace, but you can use the my workspace if you want

to, but I'm going to put it into training.

So we're going to select training and I'm going to click on the select button.

Okay.

So you'll see that that now starts to publish up to the power BI service and it should succeed quite

quickly.

Okay, there we go.

We got success.

So we're going to go into the power BI service.

I'm going to bring up my browser.

Okay.

So we're back in the power BI service.

I'm on the home screen, so I'm going to go down to my workspaces.

If you published into the my workspace, please choose that one.

I'm just going to go to my training one and you can see now that I do have my data model, I've got

the data set and I do also have the little report.

So what I'm going to do is I'm going to open up the report and you should see that table that we've

just created being shown at the time.

So there we go.

We've now got this information.

And what you will see is that there's some different options that we can use in terms of being able

to analyze this with Excel.

So the first one that I want to show you is that we've got on the export is that you've got an analyze

in Excel option.

So we're going to select that.

And what it's going to do is it's going to open up the online Excel and it's going to create a connection

to this underlying Power BI dataset.

It's then going to allow us to use pivot tables to be able to create our reports.

So let's choose that analyze in Excel and it's going to tell us creating the Excel file and do we want

to open in Excel for the Web?

So we're going to select that and we're going to allow that to happen.

Okay.

You may get a couple of dialogs that you need to just get through.

Click.

Yes, you're going to allow this to happen.

And then you can see now that our pivot table fields are now available and we've got a traditional pivot

table.

Now, if you're not familiar with pivot tables, there really are great technology.

Basically, they work like rows, columns, values, very similar to the matrix that we did earlier

on in the course.

So let's say, for example, I wanted to see a list of all my customers.

You could just click on the customers and you'll see they'll go into my rows and now my Excel.

I've got actually a list of all my customers.

Let's say I wanted to see a list of all the sales that are for my customers.

Now, if I go down to my sales and I select my sales and I try and drag it, if I go to the values,

you see that if I drop it there, it says it cannot be placed in that area of the report.

Now, the reason for that is your numeric fields that are a part of your data actually cannot be used

in pivot tables.

The only fields that can be used in pivot tables are actually your measures.

And this is part of the reason why I went back to actually using this this data set, because we created

quite a lot of measures when we were going through this.

But please note, that's that's one of the limitations here, is that when you're working with pivot

tables, you have to be able to use measures.

If I choose measures, you're going to see this actually works perfectly.

I can use my.

Total sales, total profit and give my profit ratio.

And get my lowest sale for that customer.

Highest sale.

And you can see that my pivot table now builds up.

Now, please note as well, this is dynamic.

So when you refresh this using your pivot table, you will see that you will actually get new information

as as it comes along.

So you can go along to your refresh and choose your refresh options for this.

But this is one of the limitations that you do have, is that you do need to use measures for this.

You cannot actually use the actual data field that is in the reports.

So just be aware of that as a limitation.

I just wanted to do a quick update on the Analyze with Excel feature.

So you've just seen in the lesson that we were not able to use the numeric fields that were part of

the table.

However, that's now changed.

So if you look at the table now, you'll see that all our measures have actually now been included into

the actual data table.

Whereas with part of the lesson you saw previously, you would have seen that it had its own table called

measures.

And now if we use these numeric fields, let's just grab our customer again.

Let's pop the customer into our rows.

Let's say we want to know the total sales.

You'll see now that the sum of the sales will actually work if we use our profit, for example.

Also, let's say, for example, we wanted to see our average profit, for example, just pop in the

sum of sales to there and I can go across to my pivot table.

I could say summarize values, go to my averages and you'll see then that that would recalculate.

So just a quick little update just to show you.

You can now actually use those numeric fields in your pivot table, which I think can make a huge difference

to your analysis when you're working with Excel.

Okay.

We're going to continue the lesson.

Now, the next example that I want to look at is the ability to be able to output your table as data

that you can work with in Excel.

Now what you've got is if you go to the three ellipses, you'll see that there's more options.

And one of the options you've got is the ability to be able to export your data.

So when we select export data, you'll see that you get specific options.

You can do your data with your current layout, or you could do summarize data or even look at the underlying

data.

Now you can see that this has been turned off by the report author because often the underlying data

may have thousands and thousands of rows and they may not want you to be able to actually then export

large amounts of quantity.

So let's say, for example, we wanted to pick the summarize data.

Now you can see down in the file format that we've got different options.

So traditionally what we were able to do was we could export this to a CSV or we could export this to

an Excel file.

Now when you exported it, it then basically was exported.

At that point in time, there was no live connection that allowed you to refresh this data.

However, there's now a live connection option where you can now export your summarized data and then

as the data changes, you can have it refreshed in your Excel.

So let's have a look at an example of that.

We're going to choose with the live connection.

We're going to export this again to our Excel, and this will probably open up again our Excel.

So we're going to choose that to say open.

Okay, so my excel is now opened and you can see again, I'm getting some warnings about Protected view.

I'm going to enable that.

I'm going to enable the content.

But basically we now have the data in our Excel.

Okay?

So if we look at this, what it's done is it's actually created an Excel table and you can see that

I've got the name of my table, I've got the name of my field and I've actually got the data now that

has been brought into this.

You'll also see that you do get the ability to use your traditional Excel types of filters.

So this is a proper Excel table that you're working with.

You'll see that table design.

I've got a table name and you can actually use this now, say in creating a pivot table.

So if you said I wanted to insert a pivot table, say insert, pivot table from this because you're

going to say from the table, and we could now say that we're going to include this into a pivot table

itself.

Obviously, you're just working with the rows of data that you do have.

But if I go back in my sheet and I go back to this, you will see that with your table design, you're

able to refresh this.

And when you refresh it because it's got a live connection, any new updates should then be brought

through into the table.

At the time of putting this together, this is a relatively new feature, so please just be aware that

things may change with this.

And as I noted right at the beginning of the lessons, this area is something that Microsoft tends to

play around with quite a lot.

So you might find that it does change.

But there we go.

We've now got a table that has been created within Excel and we've even created a pivot table and we

could say that we would want to see, for example, our countries see a number of customers or average

sales, highest sale.

And there we go.

We've now created that pivot table.

And one of the things here is because we're using a table within Excel, we don't have the limitations

that we did with the other one where we were needing to use any measures.

With this one here we can use any field that was exported.

So just something to take into consideration.

Okay, so there we go.

There's a couple of options that you can use when you're analyzing your data with Excel.

We're going to conclude the lesson there.

I will see you in the next one.