Home » Choose and Remove columns in Power Query

Choose and Remove columns in Power Query

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

Merge Queries

Import text using examples feature Power BI

Pivot columns – turn rows into columns

Split Columns By Positions

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 828 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.