Add A Column from Example In Power QueryEditor

Introduction

 

Sometimes, it is not directly possible to transform the data/text from a huge data table. But, with Power Query, you can easily do it using Add Column from Examples feature. In the previous article, you can see how to add a custom column. Here in this article, I am going to show you how to add a column from example, to do so we have a feature in our Power Query Editor i.e. ‘Add Column from Examples’. Follow to below step to know where you can get this feature, how to use it, and how to perform the task that you want.

 

Step 1

 

To open the Power Query editor, click on ‘Transform data’ on the home page of ‘Power BI Desktop’.

 

 

 

Now go to ‘New source’ and import data from any source that you want. Here I am using the ‘Excel sheet’ that contains the data of ‘Employees’. So click on ‘Excel’ and give the path of the file and open it into the power query editor.

 

 

 

 

Step 2

 

Now select any column, I am selecting ‘Job’. Go to the ‘Add Column’ tab and click on it’s first drop-down option i.e. ‘Column From Example’ then click on ‘From Selection’.

 

 

Here I want to create a column same like ‘Job’ but I want its data in initial-cap. Once you click on ‘From Selection’ a new column will be added to your table.

 

 

Type the first sample value in this column like ‘Clerk’.

 

 

Now click ‘CTRL+ENTER’. You can see all the value of column ‘Job’ will be entered in this new column with ‘Initial-cap’ (as you entered the first value).

 

 

Step 3

 

Similarly, I am adding one more column, ‘HierDate’, having the value ‘MM/DD/YYYY’, and I will modify the format of the date in a new column like ‘YYYY-MM-DD’.

 

To do so, select the column ‘HierDate’ and go to the ‘Add column’ tab click on ‘Column from example’, then click on ‘From Selection’.

 

 

After entering one value and click ‘Ctrl+Enter’. Sometimes, you will find the below error.

 

 

If you find that error just enter one more value and click ‘CTRL+ENTER’.

 

 

To save these changes click on ‘OK’.

 

 

Now go to file option and click on ‘Close & Apply.

 

 

All changes will be saved and shown on the Power BI desktop window.

 

 

 

Now you can create a report from this table.

 

Summary

 

Follow these simple steps, and you will be able to add a column. In the next article, we will see how to use the ‘From all Column’ feature, and other features of the ‘Add column’ tab. So, stay with us. Thanks for reading.