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
Get FREE Advanced Excel Exercises with Solutions!
Hi
There is no csv file to download.
Br,
Thanks for mentioning, Ehsan! Pardon our mistake, we’re uploading it within next 24 hours!
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