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.

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

Get and Transform Data from Excel Workbook

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

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

Get and Transform Data from Excel Workbook

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

This is the dataset after removing the top rows.

Get and Transform Data from Excel Workbook

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

This is the transformed dataset.

Get and Transform Data from Excel Workbook

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

This is the output.

Get and Transform Data from Excel Workbook


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.

Get and Transform Data from Text/CSV File

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

  • In the dialog box, click Transform Data.

Get and Transform Data from Text/CSV File

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

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

Get and Transform Data from Text/CSV File

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

Transform “Teni”.

Get and Transform Data from Text/CSV File

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

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

Get and Transform Data from Text/CSV File

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

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

Get and Transform Data from Text/CSV File

 

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

Get and Transform Data from Text/CSV File

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

This is the output.

Get and Transform Data from Text/CSV File


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.

Using Table/Range Feature to Transform Data

  • Remove the Duplicates in the dataset.

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

Using Table/Range Feature to Transform Data

  • The Duplicate Row was removed.

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

Using Table/Range Feature to Transform Data

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.

Getting Data from Web to Transform It Using Power Query

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

Getting Data from Web to Transform It Using Power Query

  • Remove Column1.

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

Getting Data from Web to Transform It Using Power Query

Column1 was removed.

  • Follow the same steps to remove Column2.

Getting Data from Web to Transform It Using Power Query

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

Getting Data from Web to Transform It Using Power Query

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

This is the output.

Getting Data from Web to Transform It Using Power Query

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

Get FREE Advanced Excel Exercises with Solutions!
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

4 Comments
  1. Hi
    There is no csv file to download.

    Br,

  2. what do I do if I use get data and my totals does not want to sum and I can’t change it to decimal or numbers without getting errors

    a team mate sitting in another country takes the same file and then it works for him but mine does not work

    • Hello Marissa Coetzee,

      Sorry to hear your problem. This issue may caused due to regional settings or data formatting differences. Make sure your regional settings in Excel match those of your teammate.
      Also, check for any leading/trailing spaces or non-numeric characters in your data. Cleaning your data using the Text to Columns or Find & Replace features might help.
      Or you can use the VALUE function to convert the values in number then use the Get Data option.

      Clean your data then use proper data format before using the Get Data option.
      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo