How to Get and Transform Data in Excel – 4 Examples

Example 1- Get and Transform Data from an Excel Workbook

Steps:

• Go to the Data tab >> click Get Data >> click From File >> select From Excel Workbook.

• In the Import Data box, select a file. Here, “19 jun 2022”.
• Click Import.

• In the Navigator box, select a file and a sheet. Here, Sheet1 in “19 jun 2022”.
• Click Transform Data.

• In the PowerQuery Editor window, remove the first row.

• Click Reduce Rows >>  Remove Rows >>  Remove Top Rows.

• In the Remove Top Rows box enter 1 in Number of rows.
• Click OK.

• This is the dataset after removing the top rows.

• To set the First row as Header, click Use First Row as Headers.

• This is the transformed dataset.

• Save the dataset in Excel and click Close & Load.
• Click Close & Load.

This is the output.

Example 2 – Get and Transform Data from a Text/CSV File

Steps:

• Go to the Data tab >> click Get Data >> click From File >> select From Text/CSV.

• In the Import Data dialog box, select a file. Here, “19 jun 2022”.
• Click Import.

• In the dialog box, click Transform Data.

• Edit the names “andrew” and “emma” : Capitals in the first letter.

• In the Transform tab >> select Text Column >> Click Format >> select Capitalize Each Word.

• “Andrew” and “Emma” are displayed, as well as the APPLIED STEPS.

• Transform “Teni”.

• Go to the Home tab >> click Replace Values.

• In Value to Find, enter “Teni” and in “Replace With”, enter “Tenis”.
• Click OK.

• “Tenis” is displayed.
• Replace “Teniss”.

• Go to the Home tab >> click Replace Values.

• In Value to Find, enter “Teniss” and in Replace With, enter “Tenis”.
• Click OK.

• The transformed dataset is displayed, as well as the APPLIED STEPS.

• Save the dataset and click Close & Load.
• Click Close & Load.

This is the output.

Example 3. Using the Table/Range Feature to Transform Data

Steps:

• Click any cell in the table. Here, C6.

• Go to the Data tab >> click Get Data >> click From Other Sources >> select From Table/Range.

• Remove the Duplicates in the dataset.

• Go to the Home tab >> click Reduce Rows >> Click Remove Rows >> select Remove Duplicates.

• The Duplicate Row was removed.

• Save the dataset and click Close & Load.
• Click Close & Load.

This is the output.

Example 4 – Transforming Data from the Web Using the Excel Power Query

Steps:

• Go to the Data tab >> click Get Data >> click From Other Sources >> select From Web.

• In the From Web dialog box, enter the URL of the website in the URL box.
• Click OK.

• In the Navigator dialog box, select a table (here, Table 0) from the website.
• Click Transform Data.

• Remove Column1.

• Go to the Home tab >> click Manage Columns >> click Remove Columns >> select Remove Columns.

• Column1 was removed.

• Follow the same steps to remove Column2.

• Set the First Row as Header.
• Go to the Home tab >> click Use First Row as Headers.

• You will see the transformed dataset, as well as APPLIED STEPS.

• Save the dataset and click Close & Load.
• Click Close & Load.

• This is the output.

Read More: Difference Between Load and Transform Data in Excel

Download Practice Workbook

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