Power BI Column From Examples
By David Rohlfs
Column from examples is a simple way to avoid using functions and to avoid using DAX or investigating the M code that you hope should work for what you need. It allows the user to make changes in a preview column and Power BI will recognize those changes to format the whole column. Column from Examples is very helpful for the report builder to simplify the amount of work needed to create new columns without a deep knowledge of column transformations.
Some instances where Column from Examples can help:
- Fill in all blank cells in a column quickly
- Splice in wording in between data
- Fix capitalization errors
- Aggregate data
In this example, we use Column from Examples to fill in empty cells with the text “Not Indicated” while allowing the cells that are not null (the valid data) to remain as they are.
After loading the data into PBI we go into the Transform Data Query.
Next, you click on Add Column, then click on the dropdown for Column from Examples.
You can use the dropdown to choose either “From All Columns” or “From Selection”.
With the header of the column you would like to change selected, we will use the From Selection option.
On the right of your screen, you should see a preview open automatically. Inside of it you can type what you would like the blank cells to have in them and type in the cells you would like to stay the same, making sure to press Enter on the cells you want PBI to refer to. You only need to do one of each Separate example.
Once you check that PBI is doing what you would like to, you can click OK just above the preview column.
Finally, you can Rename the Column to “Match the Data” and click Close and Apply.
If you have any questions or thoughts, please leave a comment!
Leave a Reply
You must be logged in to post a comment.