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’.
 
Add Column From Example In Power QueryEditor
 
Add Column From Example In Power QueryEditor
 
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.
 
Add Column From Example In Power QueryEditor
 
Add Column From Example In Power QueryEditor
 
Add Column From Example In Power QueryEditor
 
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’.
 
Add Column From Example In Power QueryEditor
 
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.
 
Add Column From Example In Power QueryEditor
 
Type the first sample value in this column like ‘Clerk’.
 
Add Column From Example In Power QueryEditor
 
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).
 
Add Column From Example In Power QueryEditor
 
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’.
 
Add Column From Example In Power QueryEditor
 
After entering one value and click ‘Ctrl+Enter’. Sometimes, you will find the below error.
 
Add Column From Example In Power QueryEditor
 
If you find that error just enter one more value and click ‘CTRL+ENTER’.
 
Add Column From Example In Power QueryEditor
 
To save these changes click on ‘OK’.
 
Add Column From Example In Power QueryEditor
 
Now go to file option and click on ‘Close & Apply.
 
Add Column From Example In Power QueryEditor
 
All changes will be saved and shown on the Power BI desktop window.
 
Add Column From Example In Power QueryEditor
 
Add Column From Example In Power QueryEditor
 
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.