Choose columns and Remove columns are operations that allows you to define what columns you need to keep in your table and which ones it needs to remove.
Lets take a look at how to use the Choose columns and Remove columns operations step by step in Power Query.
Here we have a sample table named Employee as shown below.
| Id | Name | Code | Gender | Age |
| 1 | Raj Singh | ER45TY | M | 31 |
| 2 | Rozer Mac M | RTY64 | M | 35 |
| 3 | Mark Hanry | TYW45 | M | 38 |
| 4 | Billy Keyes | RYDBG | M | 35 |
| 5 | Sania M | FSGY3 | F | 31 |
| 6 | Rolly Maria | RZZM5 | F | 36 |
| 7 | Curt Angle | DR346 | M | 38 |
| 8 | Daniel | FAF33 | M | 40 |
| 9 | Sankalp Mishra | S3253 | M | 32 |
| 10 | Sumit Viays | FA46F | M | 42 |
| 11 | Sanjana Desuza | S46GS | F | 39 |
| 12 | Molly Jack | GG53 | F | 43 |
Choosing and Removing columns
Lets see the Choose Columns and Remove Columns to keep and remove columns from table Employee.
Choose Columns
When you select Choose columns from the Home tab, you have two options:
- Choose columns
- Go to Column
Lets see the Choose columns first, In Home tab, click on Transform Data.
You will see a Power Query Editor opens, as shown below.
You can see this table contains five columns Id, Name, Code, and Age.
Assume that you want to keep only Id, Name, and Age column in this table. You use a Choose Columns operation to select only specific columns in this.
In Power Query Editor, Inside Home tab > Expand Choose Columns drop down in the Manage Columns Group.
Then Select Choose Columns from context menu.
Once you select Choose Columns, the Choose columns dialog box appears, containing all the available columns in Employee table as shown below.
Now you can select all the columns that you want to keep and remove specific columns by clearing their associated check box.
As we need only Id, Name, and Age columns in this table, so just uncheck the other columns such as Code, and Gender in order to remove it from table, after that click on Ok Button as shown below.
Once you click on OK button, you will see it keeps only those columns that were checked such as Id, Name, and Age and unchecked columns are removed from table.
.
Also you can see that a steps for this change is generated in Query Settings as shown below.
Also note that, do not forget to save your changes in the end. Click on Close &Apply.
Lets see second option that is Go to Column in Choose Columns drop down.
In this option you can select the column that you would like to go in Power Query Editor Preview.
Lets see, Go to Manage columns group, Select Go to columns from Choose columns dropdown.
Now a Go to Column dialog box appears, containing the list of available columns in table.
Now you can select any column that you would like to go in Power Query Editor preview, this might be useful in case if you have many columns in table and you want to quickly see the preview of data in any specific column in table, also can filter the columns in table using search bar.
Lets select the Name column and click on OK button.
Once you click on OK button, you will see a Name column is highlighted in Power Query Editor preview as shown below.
Remove Columns
When you select Remove columns from the Home tab, you have two options:
- Remove columns
- Remove other Columns
Lets see the Remove Columns first, It removes the currently selected columns from table.
Assume that you want to remove Id column, then just select Id column in Query editor preview window.
Next, in home tab, expand the Remove Columns drop down in Manage group, then select Remove Columns as shown below.
Once you click on Remove columns, you will see that Id columns is immediately removed from table that you can see in Power Query Editor preview as shown below.
Now save your changes in the end. Click on Close &Apply.
Now we will see the second option that is Remove other Columns, it removes all the columns from the table except the selected ones.
Lets say you have a following table containing Id, Name, Code, Gender and Age column as shown below.
Now you need only Id, Name and Gender Column only, and except these columns you need to remove all other columns.
Then you can quickly use Remove other Columns operation. Just select the Id, Name, and Gender column in Power Query Editor preview, you can select multiple column with pressing ctrl + select.
After that select Remove Other Columns as shown below.
Once you select Remove Other Columns, you will see that all the columns are removed from the table except the selected ones.
Now save your changes in the end. Click on Close &Apply.
Also Read..
Group By the data using Power Query
Create Index column using Power Query
Import text using examples feature Power BI
Pivot columns – turn rows into columns
2,562 total views, 1 views today