Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,490 --> 00:00:06,980
Now when it comes to connecting and transforming data the query editor is our cockpit and power be I
2
00:00:07,160 --> 00:00:08,090
from the Home tab.
3
00:00:08,090 --> 00:00:13,630
You can click edit queries to launch the query editor and there's a lot going on here.
4
00:00:13,640 --> 00:00:17,960
We're going to be spending most of our time in this section right here in the editor itself.
5
00:00:18,380 --> 00:00:23,570
But let's take a minute to break down the components of this interface right up there at the top.
6
00:00:23,570 --> 00:00:25,940
We've got our query editing tool box.
7
00:00:25,940 --> 00:00:31,790
This is where we'll find all of the tools that we'll use to shape and transform our data.
8
00:00:31,970 --> 00:00:34,280
Right beneath that you'll see a formula bar.
9
00:00:34,280 --> 00:00:36,770
And it's got some unfamiliar code in there.
10
00:00:36,770 --> 00:00:40,010
This is called M code and I didn't write it.
11
00:00:40,070 --> 00:00:46,460
This is code that's automatically generated as you apply different procedures or steps to your table.
12
00:00:46,610 --> 00:00:53,810
For those of you who are advanced excel users this is exactly what VBA does as it automatically generates
13
00:00:54,080 --> 00:00:57,410
visual basic code while you record a macro.
14
00:00:57,410 --> 00:01:02,750
Now don't get nervous because you don't need to even know and code to use the query editor.
15
00:01:02,810 --> 00:01:07,450
In fact we really won't be touching em code at all in this course.
16
00:01:07,520 --> 00:01:13,670
You can write custom code from scratch and you can edit the code that's automatically generated for
17
00:01:13,670 --> 00:01:19,220
you but you don't need to in order to use all of the tools that we're going to cover in this course.
18
00:01:19,280 --> 00:01:24,620
Now on the left side of the query Ed we've got our queries or query list here you can see that I've
19
00:01:24,620 --> 00:01:29,120
got nine individual connections or data queries in place.
20
00:01:29,120 --> 00:01:31,570
You can click through to see previews of your data.
21
00:01:31,670 --> 00:01:34,410
You can edit the properties change the applied steps.
22
00:01:34,520 --> 00:01:39,500
Basically any modification that you want to make to an existing query you can do so here.
23
00:01:39,560 --> 00:01:45,600
And as you select individual queries you'll see this data preview in the middle of the screen update.
24
00:01:45,680 --> 00:01:51,560
This is where we can spot check and queue our data and do things like change column types and headers
25
00:01:52,010 --> 00:01:52,790
on the right side.
26
00:01:52,790 --> 00:01:58,100
We've got our properties pane most important thing we call out here is the table name.
27
00:01:58,130 --> 00:02:03,560
Now it's really important to be strategic and clear with your table names right off the bat because
28
00:02:03,560 --> 00:02:08,180
it can be a pretty big headache to change them later on especially if you've already referenced them
29
00:02:08,510 --> 00:02:11,180
in multiple calculated fields and measures.
30
00:02:11,180 --> 00:02:12,530
So rule of thumb.
31
00:02:12,650 --> 00:02:16,540
Be strategic and careful about your table names from step 1.
32
00:02:16,790 --> 00:02:20,320
And then last but certainly not least the applied steps.
33
00:02:20,320 --> 00:02:22,370
Pain here in the lower right.
34
00:02:22,370 --> 00:02:27,650
This is really really powerful because what's happening here is that every time you make a change to
35
00:02:27,650 --> 00:02:34,100
your data every time you apply some sort of shaping or transformation operator power be I will record
36
00:02:34,190 --> 00:02:38,430
an applied step using that M code and add it to the list here.
37
00:02:38,570 --> 00:02:43,940
And what that means is that every time this connection is refreshed power b I will run through that
38
00:02:43,940 --> 00:02:48,460
same set of applied steps to shape and transform your data.
39
00:02:48,530 --> 00:02:54,320
And that's a great way to automate things like data cleansing or ETF processes.
40
00:02:54,320 --> 00:02:59,920
You don't want to end up running the same processes manually over and over and over every time you connect
41
00:02:59,920 --> 00:03:00,940
to the data.
42
00:03:01,070 --> 00:03:03,290
This automates that process for you.
43
00:03:03,290 --> 00:03:06,830
You record it once and then you're good to go moving forward.
44
00:03:06,830 --> 00:03:11,040
But perhaps the most helpful thing is that none of this is set in stone.
45
00:03:11,120 --> 00:03:15,150
You click on the X next to any individual step to remove it.
46
00:03:15,230 --> 00:03:20,810
You can click through each of your applied steps to actually visually see the impact that it's having
47
00:03:20,930 --> 00:03:27,380
on your table or you can click the gear icon on the right to modify or edit individual steps in the
48
00:03:27,380 --> 00:03:28,840
process.
49
00:03:28,910 --> 00:03:35,180
Now moving on into the query editing tools want to take a minute to introduce the three primary tabs
50
00:03:35,510 --> 00:03:37,280
or categories.
51
00:03:37,280 --> 00:03:38,990
First we've got our home tab.
52
00:03:39,050 --> 00:03:45,620
This includes some general settings data source settings parameters table properties etc. as well as
53
00:03:45,620 --> 00:03:53,060
some of the most common table transformation tools the next tab transform includes additional tools
54
00:03:53,060 --> 00:03:56,210
that allow you to modify your existing columns.
55
00:03:56,330 --> 00:03:58,160
You can split or group columns.
56
00:03:58,280 --> 00:04:02,280
You can transpose your table extract text et cetera.
57
00:04:02,510 --> 00:04:10,100
And then finally the add column tools actually create brand new columns in your table and you can define
58
00:04:10,100 --> 00:04:17,300
those new columns based on things like conditional rules or text operations calculations date operators
59
00:04:17,440 --> 00:04:24,080
etc. From here on out we're going to jump into demo mode actually get our hands dirty and practice working
60
00:04:24,080 --> 00:04:25,320
with these tools.
61
00:04:25,340 --> 00:04:26,430
Got a lot to cover.
62
00:04:26,540 --> 00:04:27,230
So let's dive in.
6614
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.