How to Get and Transform Data in Excel (4 Suitable Examples)

If you are looking for ways to get and transform data in Excel, then this is the right place for you. Sometimes, we need to get a dataset from external sources such as excel workbook, text/CSV, web, etc. We can get this dataset and transform it into an Excel worksheet by following some simple steps. Here, you will find 4 easy ways to get and transform data in Excel.

What Is Get and Transform Data?

You can use Get & Transform in Excel to import or connect to external data and then modify it in ways that match your needs, such as removing a column, changing the data type, or merging tables. After that, you may use Excel to produce charts and reports based on your query. You can update the data by refreshing it on a regular basis.

Using the Get & Transform group we can import and edit different kinds of external datasets. Here, you will find ways to get external data from Excel Workbook, Text/CSV, Web, and Table/Range and then transform them into a new dataset.

1. Get and Transform Data from Excel Workbook

To get and transform data from the Excel workbook follow the given steps below.

Steps:

First, go to the Data tab >> click on Get Data >> click on From File >> select From Excel Workbook.

After that, the Import Data box will open.
Then, select your desired file. Here, we selected the Excel file named “19 jun 2022”.
Finally, click on Import.

Now, the Navigator box will open.
Then, we selected Sheet1 from the “19 jun 2022” file.
After that, click on Transform Data.

Then, the PowerQuery Editor window will appear.
After that, we will remove the first row.

Now, click on Reduce Rows >> go to Remove Rows >> click on Remove Top Rows.

Then, the Remove Top Rows box will open.
Now, in the Number of rows box type 1.
Finally, press OK.

This is the dataset after removing the top rows.

Now, to set the First row as a Header click on Use First Row as Headers.

This is the transformed dataset. You can see the steps in the APPLIED STEPS box.

Now, to save the dataset in Excel click on Close & Load.
Then, click on Close & Load.

Finally, you will get the transformed dataset from Excel Workbook to a new sheet.

2. Get and Transform Data from Text/CSV File

In the second method, we will get and transform data from the Text/CSV File. Follow the given steps below to do it on your own.

Steps:

First, go to the Data tab >> click on Get Data >> click on From File >> select From Text/CSV.

After that, the Import Data dialog box will pop up.
Then, select your desired file. Here, we selected the Text file named “19 jun 2022”.
Finally, click on Import.

Now, a dialog box will pop up.
Then, click on Transform Data.

Now, we will edit the names “andrew” and “emma” and will capitalize the first letter.

First, from the Transform tab >> select Text Column >> Click on Format >> select Capitalize Each Word.

Now, you can find the data transformed as “Andrew” and “Emma” and can see the steps in steps in APPLIED STEPS.

After that, we will transform the data into “Teni”.

Now, go to the Home tab >> click on Replace Values.

Then, the Replace Values will open.
After that, in the Value to Find box type “Teni” and in the “Replace With” box type “Tenis”.
Finally, click on OK.

Now, you can see the data transformed as “Tenis”.
After that, we will Replace the data “Teniss”.

Now, go to the Home tab >> click on Replace Values.

Then, the Replace Values will open.
After that, in the Value to Find box type “Teniss” and in the Replace With box type “Tenis”.
Finally, click on OK.

Here, you can see the transformed dataset and the steps in the APPLIED STEPS box.

Now, to save the dataset in Excel click on Close & Load.
Then, click on Close & Load.

Finally, you will get the transformed dataset from the Text file.

3. Using Table/Range Feature to Transform Data

We can also get data from Table/Range and transform the data in Excel. Here, we have a range of data containing the Name, Age, Group, and Sports of some students. We will import this data in Excel and transform it. To do it on your own follow the steps given below.

Steps:

First, click on any cell in the table. Here, we clicked on Cell C6.

Then, go to the Data tab >> click on Get Data >> click on From Other Sources >> select From Table/Range.

Now, we will remove the Duplicates in the dataset.

First, go to the Home tab >> click on Reduce Rows >> Click on Remove Rows >> select Remove Duplicates.

Here, you can see that the Duplicate Row has been removed.

Now, to save the dataset in Excel click on Close & Load.
Then, click on Close & Load.

Finally, you will get the transformed dataset using the Table/Range Feature.

4. Transforming Data from the Web Through Excel Power Query

For the final method, we will get data from the Web and then transform it in Excel using Power Query. Follow the given steps below to do it on your own.

Steps:

First, go to the Data tab >> click on Get Data >> click on From Other Sources >> select From Web.

Now, the From Web dialog box will open.
Here, in the URL box insert the URL of the website you want to use.
Then, press OK.

Now, a Navigator dialog box will open.
Here, we selected Table 0 from the website we used. You can select any table according to your preference.
After that, click on Transform Data.

Then, we will remove Column1.

Now, go to the Home tab >> click on Manage Columns >> Click on Remove Columns >> select Remove Columns.

Here, you can see that Column1 has been removed.

Now, do the same steps to remove Column2.

Then, we will set the First Row as Header.
Now, go to the Home tab >> click on Use First Row as Headers.

Here, you can see the transformed dataset and the steps in the APPLIED STEPS box.

Now, to save the dataset in Excel click on Close & Load.
Then, click on Close & Load.

Finally, you will get the transformed dataset from the Web.

Why Should You Use Get and Transform in Excel?

We should use Get and Transform Feature in Excel because of many reasons. It can make many works easy to do such as:

Using Get and Transform we can import an entire folder of text files into a single data table.
It can convert exported files into an easy-to-read layout.
Using Get and Transform we can load millions of rows into Power Pivot conveniently.

Limitations of Get and Transform Data in Excel

When you try to export more than a million rows from Excel, it tends to hit its limits.

In circumstances when we have used Get & Transform to transform millions of rows, the only option to send out ungrouped rows is to use time-consuming hacks or workarounds.
We have also discovered that deploying Get & Transform queries to numerous users can be unreliable, especially if you employ various data sources and joins.

Finally, Excel is not designed for complex data modeling. You can do linear regressions rapidly, but you’ll need to utilize a more rigorous platform after that.

Download Practice Workbook

Get and Transform Data.xlsx

Get & Transform Data.txt

Conclusion

So, in this article, you will find 4 ways to Get and Transform data in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. Thank you!

<< Go Back to Transform Data in Power Query | Power Query Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Get and Transform Data in Excel (4 Suitable Examples)

What Is Get and Transform Data?

1. Get and Transform Data from Excel Workbook

2. Get and Transform Data from Text/CSV File

3. Using Table/Range Feature to Transform Data

4. Transforming Data from the Web Through Excel Power Query

Why Should You Use Get and Transform in Excel?

Limitations of Get and Transform Data in Excel

Conclusion

Leave a reply Cancel reply