Record.FieldOrDefault (official documentation here) is a seldom-used function in Power Query. But here is one handy use of this formula i.e. to make multiple full word replacements in a column in a single step
We have this Data
| ColumnName |
| Power BI |
| DAX |
| Power Query |
| Tennis |
| Apples |
| Bananas |
| SVGs |
We want to replace few rows only i.e
1) Replace “Power BI” with “BI tool”
2) Replace “Tennis” with “Sport”
3) Replace “DAX” with “Language”
We can add a new custom column as
=Record.FieldOrDefault(
[
Power BI="BI tool",
Tennis="Sport",
DAX="Language"
],
[ColumnName],[ColumnName])i.e. Just store the “Word to Replace” and “their replacements” in a record
LIMITATIONS:
You can only make full word/cell replacement with this technique. If you want to replace multiple words inside a sentence, a different technique needs to be used
