Power Query add column from examples feature, allows you to add new columns to data model simply by providing one or more example values for the new columns. You can either create the new column examples from a selection or provide input based on all existing columns in the table.
Following are the scenario when you can use add columns from examples feature, suggested by Microsoft as follows:
- You know the data you want in your new column, but you are not sure which transformation, or collection of transformations, will get you there.
- You already know which transformations you need, but you are not sure what to select in the UI to make them happen.
- You know all about the transformations you need using a Custom Column expression in M language, but one or more of those expressions are not available in the UI.
Lets see how to use Add Columns from examples in Power Query.
Here we have a sample data-set named TransactionDetails as follow.
| TranId | CustId | TranCode | Code |
| 1 | 101 | EXTY-E53 | (00)1157 |
| 2 | 101 | EXTY-T56 | (00)525 |
| 3 | 102 | EXTY-B67 | (00)572 |
| 4 | 103 | EXTY-N57 | (00)363 |
| 5 | 104 | EXTY-M67 | (00)978 |
| 6 | 104 | EXTY-B78 | (00)858 |
| 7 | 103 | EXTY-B67 | (00)985 |
| 8 | 103 | EXTY-BE2 | (00)353 |
| 9 | 104 | EXTY-NM3 | (00)676 |
| 10 | 101 | EXTY-BN4 | (00)684 |
| 11 | 101 | EXTY-WE4 | (00)676 |
| 12 | 103 | EXTY-QW8 | (00)858 |
| 13 | 105 | EXTY-BV3 | (00)134 |
| 14 | 105 | EXTY-BN3 | (00)563 |
| 15 | 103 | EXTY-NM4 | (00)768 |
Now go to Power Query Editor, click on Transform data.
After that, you will see a Power Query Editor opens, now go to Add column tab.
There you will see a Add columns from examples drop-down, it has two option as follows:
- From All columns : use examples to create a new column in table.
- From Selection: use examples and current selection to create a new column in table.
From All columns
Lets select the first option that is From All columns from drop-down, as shown below.
Now you will see a new Column 1 appears to the right of the existing columns, if you have many columns in your data-set then you may need to scroll to see them all also you will note that as you have selected From All columns option then all the column’s header have been checked that means example values will be matched with all of these checked columns.
Copy values from column
Assume that you want to copy the Transcode column data to new column.
When you click on Column1 cell, you will see it displays you so many auto suggested values based on all the columns in a table, and you can also see there is a value from Transcode column.
Now when you just start typing EXTY example values, or you can say a sample values in the blank cells of Column 1, Power BI creates a rules and transformations to match your examples, and uses them to fill the rest of the column.
You can see it suggests you a value EXTY-53 based on value you entered, and when you select value from suggested list box it automatically fill all the values related to the selected column.
Now you can see column named has changed to TransCode-Copy and it has same values as Transcode.
Get text after delimiter
Lets say, you want to extract a value that exits after delimiter(-) in new column, means you want to see a value into new column as if actual value is “EXTY-E53” then show only “E53″.
So you can continue to provide examples to new column, lets click on first cell of Transcode-copy column and just keep the values only after delimiter (-), then hit enter keyword.
Once you hit enter, you can see it removes all the values before and including delimiter (-), and now column having a value that you want means values only after delimiter.
Also it renamed column as Text After Delimiter because Power Query has performed a Text after delimiter transformation.
Now, when you are satisfied, then select OKÂ to commit your changes
Once you click on OK button, you will see that new column is added into your table.
Also you can see a transformation steps is added by Power Query in Query Settings under applied steps.
Now you can rename the new column, lets double click on column header and rename it as NewTransCode.
Merge column values
Assume that, you want to see a TransId, and TransCode column values into new column.
Lets say, if TransId column has values as 1, and Transcode column has value as “EXTY-E53” then new column will have value as “1 EXTY-E53”, basically you want to merge these columns value into single column that will be a new column in table.
Lets add one more new column in table, using From all columns option.
After that, just enter a values for TransId and Transcode (put space between these values) in first cell of column as shown below.
You can see, it fills same values in new columns that is “1 EXTY-E53” which is wrong.
Therefore, you can keep continue and provides a sample values to column to get more accurate values for columns.
As value in first cell is correct based on value in TransId and Transcode column that is “1 EXTY-E53”, but value in second cell and onwards is not correct for second cell it should be “2 EXTY-T56”.
Lets click on second cell of new column and enter a correct value “2 EXTY-T56″ and hit enter.
By doing so Power BI re defined a rules and transformations to match your examples in more accurate way.
And you can see this time column having a correct values, which contains a merge of TransId and Transcode.
If you are satisfied with column values then you can click on OK button.
Once you click on OK button, a new column is added into table.
This is pretty simple if you do not get expected result by entering an example values check first cell if it has correct value then you can go and provide an example value for second cell, So that Power BI can improve it’s accuracy and create a more accurate role and transformation and return a correct result as you are expecting.
Similarly, you add new column and provide example values as per you requirement, also can explore capabilities on this feature.
From Selection
This option works same as From all columns does, but the only difference is that it uses an example and selected column to create a new column.
Lets select the CustId column from data preview. Then go to Add columns from examples and select From Selection.
Once you select from select option from drop-down. You can see, It suggests only a values based on CustId columns when you click on first cell.
Also you can see that CustId column’s header is checked as you have selected this column for this option.
Now you can provides an example values as you did for From All Columns option.
Once you are done with this, you can save you changes by clicking on Close & Apply button.
Also Read..
Import text using examples feature Power BI
Choose and Remove Columns from table
Data Profiling: Column Quality, Column Distribution & Column Profile
Fill values operation: fill down and fill up
