All language subtitles for 19. PRO TIP Importing Models from Excel to Power BI

All right.

Time to share on my favorite pro tips importing entire models directly from Excel so in power b I got

this import menu that we haven't really talked about with the option to import Excel workbook contents.

And note this is different from using the Get Data option and pointing to an Excel workbook.

This is actually importing additional information about an entire model from Excel into power RBI.

So what's incredibly helpful about this is that this import 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 that query editing procedures all of the Applied steps the data modeling details like the

relationships hierarchies field settings and formats etc..

And then last but not least all of the calculated columns and calculated tax measures that you've added

to your model in excel so quick protip here Power Pivot in Excel does actually include some features

some really helpful features that power RBI does not.

So some additional filtering options a little bit more of a user friendly tax interface for example.

But the fact is if you're more comfortable building models in the Excel environment which is covered

in my power query Power Pivot and Dex course then go ahead and continue to build there within that environment

and then import those models into power by further reporting and visualization phase.

You'll end up at the exact same place.

It's really just which environment you're more comfortable in.

So sit back relax for a minute.

Just watch this demo.

I'm going to show you what this looks like when you actually import a full data model from Excel into

power by all right.

So I actually want to start in Excel for this one.

This is my food mart data model complete file.

Is probably bringing back some flashbacks to students who were in my last course.

And basically we've got a data model here with a bunch of look up and data tables got a diagram view

which we go should show us all of the relationships in place got parameters and disconnected tables

as well.

So it's pretty robust model.

And on top of that if I manage my measures we've got all of these tax measures that we created which

would be a huge huge headache to have to recreate from scratch and power.

Luckily I won't have to.

Now one thing to note here I also have these additional pivot tabs with things like conditional formatting

and data bars and icon sets.

Now there is no equivalent pivot view quite like this and powered by there's a matrix visual that's

similar but not quite the same.

So these views these actual pivots these additional tabs will not transfer over.

So I just want to caveat that.

So from here I'm going to open up a blank brand new power be file so that I can import this model directly

into it.

So here we go blank file untitled and now again instead of going to get data from Excel I'm going to

choose this top left main menu and import Excel workbook contents.

Big difference here.

And when I do that I can navigate to my food Mark data model complete file gives me this little note

that says hey we don't work directly with Excel workbooks but what we can do is extract that content

so that you can use it right here and part B desktop.

And so I read this and it sounds great.

Let's go and start the process and we get in there which is pretty straightforward.

It says we can't do it.

And next year your model is open in excel.

So easy fix.

Let's go ahead and close the Excel version.

Don't need to save any changes and let's go and retry that area.

So now it says OK we've got got some tables that exist in that original workbook.

In other words they were created with actual cell ranges in excel.

And it just says you want to copy the data and create a version right here in the power by desktop environment

or keep this remote connection back to the Excel workbook which sounds like kind of a nightmare.

So I'm going to say let's take the data from that price threshold and age threshold table and let's

copy it so that exists here as well.

Now it runs through all these fancy things.

That was real time.

That was about I don't know five or 10 seconds and check it out it's migrated all of these items different

queries different data model tables all these KPI is measures and we press close obviously there's nothing

in our report view because there's no equivalent to the report view in Excel.

But if we go to data check it out all of our tables exist here all of our fields including all of these

calculated measures think there's a bunch here my transactions table Yeah they've all carried through

all of our measures in the relationships view we can see that our data model with all of its table relationships

is still intact it got a little bit scrambled so it doesn't look quite as nice and clean but we can

go ahead and just rearrange things as we see fit.

So some of these details aren't really going to make sense quite yet until we dive into the data modeling

and the DAX sections of the course.

But I wanted to just give you a little preview and let you know that there is this option to pull a

fully baked model from Excel straight into power by.