All language subtitles for 033 PRO TIP Importing Excel Models_en

Instructor: All right,

so we've got another pro-tip lecture here

and this time, we're gonna talk

about importing entire models directly from Excel.

So in Power BI, we've got this import menu

that we really haven't talked about yet

and you have this option to import Excel workbook contents.

And note that this is different

from using the get data option

and pointing to an Excel workbook.

What this is doing is actually importing

additional information about the entire model

from Excel into Power BI.

So what's incredibly helpful about this

is that this process preserves

just about all of the information about your model.

It transitions information about the data source connections

and queries that are in place, all the file paths,

it maintains the query editing procedures,

all of the applied steps, the data modeling details,

like the relationships and hierarchies

and field settings and formats and et cetera, et cetera.

And then last but not least, all of your calculated columns

and DAX measures are also imported

from your Excel model into Power BI.

So quick pro tip here,

if you're more comfortable building models

in the Excel environment, which is covered

in Maven's Power Query, Power Pivot, and DAX course,

then go ahead and continue to build there

within that environment and then just import those models

into Power BI for the reporting and visualization phase.

You'll end up in the exact same place.

Honestly, it's really just which environment

you're more comfortable in.

All right, so sit back,

relax for a minute and just watch this demo.

I'm gonna show you what it looks like

when you actually import a full data model

from Excel into Power BI.

So this is the FoodMartDataModelCOMPLETE file

from the Power Query, Power Pivot, and DAX course.

And basically here, we've got a fully built data model

with a whole bunch of tables here,

lookup tables and data tables.

We can jump over into the diagram view here.

That'll show us all of our relationships,

our table relationships in one place, right?

We've got all these lookups.

You can see that we've got our parameters

and our disconnected tables as well.

So again, it's a pretty robust model here.

And then on top of that, we've also got all

of our measures that we have created.

And this would be a huge, huge headache

if we had to recreate all of these different measures

from scratch in Power BI.

And luckily with this functionality,

we won't have to do that.

Now, one thing to note here, as part of this workbook,

we've added all sorts of pivot tables with things

like conditional formatting and data bars and icons.

And there's really no equivalent Power Pivot view

quite like this in Power BI.

There's a matrix visual that's similar

but not quite the same.

So all of these views, the actual pivot tables,

these additional tabs down here, none of that stuff

will actually transfer over.

So I just wanna make sure that you're clear on that first.

All right, so from here, I'm gonna go and open up

a brand new Power BI file

so that we can import this model directly into it.

All right, so I've launched my brand new workbook,

and I'm just gonna close out of this menu here.

And again, we are not getting data from the Get Data menu.

We actually wanna come up to File here

and we are going to import data

from Power Query, Power Pivot, or Power View.

And here is my FoodMartDataModelCOMPLETE file

and click on this, Excel workbook contents.

It says we don't directly work with Excel workbook contents

but we know how to extract the useful content

so that you can work with it in Power BI Desktop.

And again, that's great, right?

We don't necessarily need all

of those pivot table views and everything like that.

We really just wanna get all

of the modeling in DAX and calculated columns

and all of that stuff imported into Power BI.

So click Start.

Oh, and we get an error message.

Migration failed.

It's already open in another program.

So let's head back over and we'll close out of Excel first.

All right, so we'll just close out of this

and let's hit Retry.

All right, there we go.

So now it says, okay,

we've got some tables that exist in that original workbook.

In other words,

they were created using actual cell ranges in Excel.

And it's just saying, hey, do you wanna copy the data

or keep the connection between Power BI and Excel?

And because I just wanna import everything

into Power BI, you know, we're gonna copy the data instead

of trying to keep some sort of connection open.

So Power BI's running through all of their steps

in order to import that fully built model into Power BI.

And that went pretty quickly, right?

And check it out.

It's migrated all of these different tables over.

We've got these data model connections and items,

KPIs and measures.

We've got 48 here, a pretty exhaustive list here

of everything that was imported over

into our data model.

Back in our report view,

we can see that we've got all

of the tables from the model that were added.

Jump over into our data view.

Same thing here, like calendar table, right?

We've got all of our different months and calculations.

Same thing.

Our relationship view or our model view here.

We'll scroll over a little bit.

Can collapse these panes so we can see it.

Again, it's imported our data model as well.

No, it's a little bit messy.

We can go through and reorganize things

but the point here is that it's imported all

of the tables and all of our calculations.

If I head back over here

and we start looking at these measures,

six-month rolling profit, click in.

We've got all of those great DAX measures that we created

in Excel right here in Power BI.

So some of these details

aren't really gonna make sense quite yet

until we dive into the data modeling

and DAX sections of this course.

But again, I just wanna give you a little preview here

and let you know

that this is an option to be able

to pull a fully baked model from Excel right into Power BI.