1
00:00:04,500 --> 00:00:11,430
In this section you're going to learn how to use "Get and Transform" from the data tab to import, clean,

2
00:00:11,520 --> 00:00:13,610
and transform your data.

3
00:00:13,720 --> 00:00:18,150
Get and transform was first introduced in Excel 2016.

4
00:00:18,150 --> 00:00:21,860
It replaced a previous Excel add-in which was called Power Query.

5
00:00:22,110 --> 00:00:26,420
To learn all about power query, you're going to need a complete course.

6
00:00:26,460 --> 00:00:32,700
The aim of this section is to get you started so you can accomplish simpler tasks and get a good feeling

7
00:00:32,729 --> 00:00:40,860
about what it can do. Power query was a free add-in that was first introduced in Excel 2010. In Excel 2013

8
00:00:40,860 --> 00:00:46,410
it was still an add-in but it had improved a lot. Because it was such a success

9
00:00:46,470 --> 00:00:52,080
Microsoft decided to include it in core Excel. In Excel 2016

10
00:00:52,080 --> 00:00:56,610
They put it in the Data tab and they called it Get and Transform.

11
00:00:56,610 --> 00:01:01,740
To this day, many people still refer to Get and Transform as Power Query.

12
00:01:01,770 --> 00:01:07,720
They are basically the same thing. With power query you can connect to different data sources.

13
00:01:07,860 --> 00:01:16,410
For example, text files, data on the web, tables in the same workbook or other workbooks, connect to a folder

14
00:01:16,890 --> 00:01:24,070
or different databases like SQL and Access, get data from Azure and many other sources.

15
00:01:24,090 --> 00:01:27,840
Once you connect then you get to clean and prepare the data.

16
00:01:27,870 --> 00:01:31,540
So it's in the right format for further analysis.

17
00:01:31,560 --> 00:01:35,990
You basically get data and you transform it the way you need.

18
00:01:36,020 --> 00:01:37,280
Another great feature of power query

19
00:01:37,280 --> 00:01:42,510
is that you can import from multiple files and merge them together.

20
00:01:42,510 --> 00:01:50,210
The end result is either a data table in Excel, a pivot table, or data imported in Power Pivot.

21
00:01:50,340 --> 00:01:57,390
Now you might ask: Why use per query? Why not just use formulas or Excel features like Flash Fill, sort, search

22
00:01:57,390 --> 00:01:58,580
and replace?

23
00:01:58,590 --> 00:02:05,890
Sure you can. When to use what, depends on the extent of the data transformation that you need to do.

24
00:02:05,910 --> 00:02:11,540
Power query just offers you another way to change your data into a proper dataset.

25
00:02:11,550 --> 00:02:17,480
It's great for cases where the transformation is more complex and needs a lot of steps.

26
00:02:17,490 --> 00:02:24,080
Also if you have a lot of data to transform. You're going to see examples in this section that will highlight

27
00:02:24,080 --> 00:02:28,110
the advantages of power query over standard Excel features.

28
00:02:28,230 --> 00:02:31,030
You might have heard about Power Pivot and PowerBI.

29
00:02:31,170 --> 00:02:33,420
What are these?

30
00:02:33,480 --> 00:02:38,820
With Power Pivot you can create relationships between different datasets. And you can also make calculations over

31
00:02:38,820 --> 00:02:39,470
Big Data.

32
00:02:39,630 --> 00:02:44,280
Something you wouldn't be able to do in a normal spreadsheet.

33
00:02:44,280 --> 00:02:49,800
So if you have big data on which you need to make calculations on. What you're going to do first is to

34
00:02:49,800 --> 00:02:55,910
connect to the data with power query which you can use to transform data into a proper dataset,

35
00:02:56,040 --> 00:03:02,910
if you need to. Then you can use Power Pivot to create relationships between the different datasets.

36
00:03:02,910 --> 00:03:05,090
And add new calculated fields.

37
00:03:05,190 --> 00:03:10,190
You can then create visualizations or summary tables in Excel or in PowerBI.

38
00:03:10,480 --> 00:03:17,970
The advantage of using PowerBI reports over Excel is that PowerBI can handle a lot of data without

39
00:03:17,970 --> 00:03:18,920
problems.

40
00:03:18,930 --> 00:03:26,080
It also gives the reports a level of interactivity we can't achieve with normal Excel features.

41
00:03:26,130 --> 00:03:32,040
It's also cloud based and users don't need to have Excel to view the reports.

42
00:03:32,060 --> 00:03:38,020
Power Pivot and PowerBI are two very extensive topics that require their own courses.

43
00:03:38,050 --> 00:03:43,860
I had to mention them here though so you have a good understanding about the full spectrum of these

44
00:03:43,890 --> 00:03:45,660
Excel Power Tools.

45
00:03:45,660 --> 00:03:48,630
Coming back to Power Query or Get and Transform.

46
00:03:48,810 --> 00:03:51,610
You can get very advanced or stay simple.

47
00:03:52,110 --> 00:03:56,540
Here's the best part. You can achieve a lot with Power Query's

48
00:03:56,550 --> 00:03:58,190
basic features.

49
00:03:58,230 --> 00:03:59,810
Let's start doing just that.