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.


Download Practice Workbook


4 Suitable Examples to Get and Transform Data in Excel

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.

Get and Transform Data 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.

Get and Transform Data from Excel Workbook

  • 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.

Get and Transform Data from Excel Workbook

  • 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.

Get and Transform Data from Excel Workbook

  • 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.

Get and Transform Data from Excel Workbook

  • 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.

Get and Transform Data from Excel Workbook


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.

Get and Transform Data from Text/CSV File

  • 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.

Get and Transform Data from Text/CSV File

  • 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.

Get and Transform Data from Text/CSV File

  • 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”.

Get and Transform Data from Text/CSV File

  • 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.

Get and Transform Data from Text/CSV File

  • 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.

Get and Transform Data from Text/CSV File

  • 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.

Get and Transform Data from Text/CSV File

  • 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.

Get and Transform Data from Text/CSV 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.

Using Table/Range Feature to Transform Data

  • 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.

Using Table/Range Feature to Transform Data

  • 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.

Using Table/Range Feature to Transform Data

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


4. Getting Data from Web to Transform It by Using 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.

Getting Data from Web to Transform It Using Power Query

  • 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.

Getting Data from Web to Transform It Using Power Query

  • Then, we will remove Column1.

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

Getting Data from Web to Transform It Using Power Query

  • Here, you can see that Column1 has been removed.

  • Now, do the same steps to remove Column2.

Getting Data from Web to Transform It Using Power Query

  • 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.

Getting Data from Web to Transform It Using Power Query

  • 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.

Getting Data from Web to Transform It Using Power Query


Why Should You Use Get and Transform?

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.

Limits of Get and Transform Data

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.


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. And, visit ExcelDemy for many more articles like this. Thank you!

Arin
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo