In this section you're going to learn how to use "Get and Transform" from the data tab to import, clean,
and transform your data.
Get and transform was first introduced in Excel 2016.
It replaced a previous Excel add-in which was called Power Query.
To learn all about power query, you're going to need a complete course.
The aim of this section is to get you started so you can accomplish simpler tasks and get a good feeling
about what it can do. Power query was a free add-in that was first introduced in Excel 2010. In Excel 2013
it was still an add-in but it had improved a lot. Because it was such a success
Microsoft decided to include it in core Excel. In Excel 2016
They put it in the Data tab and they called it Get and Transform.
To this day, many people still refer to Get and Transform as Power Query.
They are basically the same thing. With power query you can connect to different data sources.
For example, text files, data on the web, tables in the same workbook or other workbooks, connect to a folder
or different databases like SQL and Access, get data from Azure and many other sources.
Once you connect then you get to clean and prepare the data.
So it's in the right format for further analysis.
You basically get data and you transform it the way you need.
Another great feature of power query
is that you can import from multiple files and merge them together.
The end result is either a data table in Excel, a pivot table, or data imported in Power Pivot.
Now you might ask: Why use per query? Why not just use formulas or Excel features like Flash Fill, sort, search
and replace?
Sure you can. When to use what, depends on the extent of the data transformation that you need to do.
Power query just offers you another way to change your data into a proper dataset.
It's great for cases where the transformation is more complex and needs a lot of steps.
Also if you have a lot of data to transform. You're going to see examples in this section that will highlight
the advantages of power query over standard Excel features.
You might have heard about Power Pivot and PowerBI.
What are these?
With Power Pivot you can create relationships between different datasets. And you can also make calculations over
Big Data.
Something you wouldn't be able to do in a normal spreadsheet.
So if you have big data on which you need to make calculations on. What you're going to do first is to
connect to the data with power query which you can use to transform data into a proper dataset,
if you need to. Then you can use Power Pivot to create relationships between the different datasets.
And add new calculated fields.
You can then create visualizations or summary tables in Excel or in PowerBI.
The advantage of using PowerBI reports over Excel is that PowerBI can handle a lot of data without
problems.
It also gives the reports a level of interactivity we can't achieve with normal Excel features.
It's also cloud based and users don't need to have Excel to view the reports.
Power Pivot and PowerBI are two very extensive topics that require their own courses.
I had to mention them here though so you have a good understanding about the full spectrum of these
Excel Power Tools.
Coming back to Power Query or Get and Transform.
You can get very advanced or stay simple.
Here's the best part. You can achieve a lot with Power Query's
basic features.
Let's start doing just that.