Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,630 --> 00:00:00,840
All right.
2
00:00:00,840 --> 00:00:08,370
Time to share on my favorite pro tips importing entire models directly from Excel so in power b I got
3
00:00:08,370 --> 00:00:14,420
this import menu that we haven't really talked about with the option to import Excel workbook contents.
4
00:00:14,490 --> 00:00:20,760
And note this is different from using the Get Data option and pointing to an Excel workbook.
5
00:00:20,760 --> 00:00:27,620
This is actually importing additional information about an entire model from Excel into power RBI.
6
00:00:27,690 --> 00:00:34,350
So what's incredibly helpful about this is that this import process preserves just about all of the
7
00:00:34,350 --> 00:00:36,010
information about your model.
8
00:00:36,090 --> 00:00:41,880
It transitions information about the data source connections and queries that are in place all the file
9
00:00:41,880 --> 00:00:42,800
paths.
10
00:00:42,900 --> 00:00:48,810
It maintains that query editing procedures all of the Applied steps the data modeling details like the
11
00:00:48,810 --> 00:00:53,020
relationships hierarchies field settings and formats etc..
12
00:00:53,250 --> 00:00:58,740
And then last but not least all of the calculated columns and calculated tax measures that you've added
13
00:00:58,740 --> 00:01:06,270
to your model in excel so quick protip here Power Pivot in Excel does actually include some features
14
00:01:06,540 --> 00:01:10,050
some really helpful features that power RBI does not.
15
00:01:10,050 --> 00:01:16,080
So some additional filtering options a little bit more of a user friendly tax interface for example.
16
00:01:16,080 --> 00:01:21,300
But the fact is if you're more comfortable building models in the Excel environment which is covered
17
00:01:21,300 --> 00:01:27,180
in my power query Power Pivot and Dex course then go ahead and continue to build there within that environment
18
00:01:27,570 --> 00:01:33,510
and then import those models into power by further reporting and visualization phase.
19
00:01:33,510 --> 00:01:35,410
You'll end up at the exact same place.
20
00:01:35,430 --> 00:01:38,860
It's really just which environment you're more comfortable in.
21
00:01:38,970 --> 00:01:40,810
So sit back relax for a minute.
22
00:01:40,860 --> 00:01:42,240
Just watch this demo.
23
00:01:42,240 --> 00:01:47,580
I'm going to show you what this looks like when you actually import a full data model from Excel into
24
00:01:47,580 --> 00:01:49,820
power by all right.
25
00:01:49,820 --> 00:01:52,370
So I actually want to start in Excel for this one.
26
00:01:52,370 --> 00:01:55,650
This is my food mart data model complete file.
27
00:01:55,670 --> 00:02:00,680
Is probably bringing back some flashbacks to students who were in my last course.
28
00:02:00,680 --> 00:02:08,300
And basically we've got a data model here with a bunch of look up and data tables got a diagram view
29
00:02:09,200 --> 00:02:15,860
which we go should show us all of the relationships in place got parameters and disconnected tables
30
00:02:15,890 --> 00:02:16,400
as well.
31
00:02:16,790 --> 00:02:19,050
So it's pretty robust model.
32
00:02:19,250 --> 00:02:26,150
And on top of that if I manage my measures we've got all of these tax measures that we created which
33
00:02:26,150 --> 00:02:30,620
would be a huge huge headache to have to recreate from scratch and power.
34
00:02:31,550 --> 00:02:33,620
Luckily I won't have to.
35
00:02:33,620 --> 00:02:39,500
Now one thing to note here I also have these additional pivot tabs with things like conditional formatting
36
00:02:39,500 --> 00:02:41,930
and data bars and icon sets.
37
00:02:41,930 --> 00:02:47,850
Now there is no equivalent pivot view quite like this and powered by there's a matrix visual that's
38
00:02:47,870 --> 00:02:49,800
similar but not quite the same.
39
00:02:49,820 --> 00:02:55,590
So these views these actual pivots these additional tabs will not transfer over.
40
00:02:55,970 --> 00:02:57,980
So I just want to caveat that.
41
00:02:57,980 --> 00:03:04,460
So from here I'm going to open up a blank brand new power be file so that I can import this model directly
42
00:03:04,460 --> 00:03:04,860
into it.
43
00:03:06,850 --> 00:03:15,010
So here we go blank file untitled and now again instead of going to get data from Excel I'm going to
44
00:03:15,010 --> 00:03:20,910
choose this top left main menu and import Excel workbook contents.
45
00:03:20,920 --> 00:03:22,560
Big difference here.
46
00:03:22,560 --> 00:03:28,060
And when I do that I can navigate to my food Mark data model complete file gives me this little note
47
00:03:28,060 --> 00:03:34,120
that says hey we don't work directly with Excel workbooks but what we can do is extract that content
48
00:03:34,390 --> 00:03:37,250
so that you can use it right here and part B desktop.
49
00:03:37,330 --> 00:03:39,970
And so I read this and it sounds great.
50
00:03:40,110 --> 00:03:46,200
Let's go and start the process and we get in there which is pretty straightforward.
51
00:03:46,210 --> 00:03:47,240
It says we can't do it.
52
00:03:47,260 --> 00:03:50,380
And next year your model is open in excel.
53
00:03:50,380 --> 00:03:52,200
So easy fix.
54
00:03:52,210 --> 00:03:55,140
Let's go ahead and close the Excel version.
55
00:03:55,150 --> 00:04:00,420
Don't need to save any changes and let's go and retry that area.
56
00:04:00,450 --> 00:04:05,970
So now it says OK we've got got some tables that exist in that original workbook.
57
00:04:06,060 --> 00:04:09,590
In other words they were created with actual cell ranges in excel.
58
00:04:09,870 --> 00:04:15,000
And it just says you want to copy the data and create a version right here in the power by desktop environment
59
00:04:15,600 --> 00:04:20,700
or keep this remote connection back to the Excel workbook which sounds like kind of a nightmare.
60
00:04:20,730 --> 00:04:26,250
So I'm going to say let's take the data from that price threshold and age threshold table and let's
61
00:04:26,250 --> 00:04:28,700
copy it so that exists here as well.
62
00:04:29,970 --> 00:04:33,040
Now it runs through all these fancy things.
63
00:04:33,380 --> 00:04:34,220
That was real time.
64
00:04:34,220 --> 00:04:40,550
That was about I don't know five or 10 seconds and check it out it's migrated all of these items different
65
00:04:40,550 --> 00:04:49,300
queries different data model tables all these KPI is measures and we press close obviously there's nothing
66
00:04:49,300 --> 00:04:53,590
in our report view because there's no equivalent to the report view in Excel.
67
00:04:53,830 --> 00:05:02,530
But if we go to data check it out all of our tables exist here all of our fields including all of these
68
00:05:02,530 --> 00:05:08,950
calculated measures think there's a bunch here my transactions table Yeah they've all carried through
69
00:05:09,430 --> 00:05:15,850
all of our measures in the relationships view we can see that our data model with all of its table relationships
70
00:05:16,030 --> 00:05:21,850
is still intact it got a little bit scrambled so it doesn't look quite as nice and clean but we can
71
00:05:21,850 --> 00:05:25,240
go ahead and just rearrange things as we see fit.
72
00:05:25,270 --> 00:05:30,130
So some of these details aren't really going to make sense quite yet until we dive into the data modeling
73
00:05:30,160 --> 00:05:32,050
and the DAX sections of the course.
74
00:05:32,050 --> 00:05:36,940
But I wanted to just give you a little preview and let you know that there is this option to pull a
75
00:05:36,940 --> 00:05:39,730
fully baked model from Excel straight into power by.
8074
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.