Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have text in one column, which I want to replace in another column. I need to do this replacement for multiple columns so I created a custom function fxReplaceValue. Because I want to repeat this for various columns with different key words, I created a parameter for the column, e.g. [Summer]
let
Source = (qrySource) => let
Source = qrySource,
replaceSummer = Table.ReplaceValue(Source, each [Summer], each if Text.Contains([Value], "Summer", Comparer.OrdinalIgnoreCase) then [Value] else [Summer], Replacer.ReplaceValue,{"Summer"}),
replaceValSummer = Table.ReplaceValue(replaceSummer, each [Value], each if Text.Contains([Summer], "Summer", Comparer.OrdinalIgnoreCase) then "" else [Value], Replacer.ReplaceValue,{"Value"})
in
replaceValSummer
in
Source
What is the correct syntax to reference the desired column as a parameter?
let
Source = (qrySource, colReplacement) => let
Source = qrySource,
replaceSummer = Table.ReplaceValue(Source, each colReplacement, each if Text.Contains([Value], "Summer", Comparer.OrdinalIgnoreCase) then [Value] else colReplacement, Replacer.ReplaceValue,{colReplacement}),
replaceValSummer = Table.ReplaceValue(replaceSummer, each [Value], each if Text.Contains(colReplacement, "Summer", Comparer.OrdinalIgnoreCase) then "" else [Value], Replacer.ReplaceValue,{"Value"})
in
replaceValSummer
in
Source
Thank you for any advice!
Solved! Go to Solution.
let
Source = (qrySource,ColumnToCheck as text,Condition as text,ColumnToBeReplaced as text) => let
Source = qrySource,
replaceSummer = Table.ReplaceValue(Source, each Record.Field(_,ColumnToBeReplaced), each if Text.Contains(Record.Field(_,ColumnToCheck), Condition, Comparer.OrdinalIgnoreCase) then Record.Field(_,ColumnToCheck) else Record.Field(_,ColumnToBeReplaced), Replacer.ReplaceValue,{ColumnToBeRelaced}),
replaceValSummer = Table.ReplaceValue(replaceSummer, each Record.Field(_,ColumnToCheck), each if Text.Contains(Record.Field(_,ColumnToBeReplaced), Condition, Comparer.OrdinalIgnoreCase) then "" else Record.Field(_,ColumnToCheck), Replacer.ReplaceValue,{ColumnToCheck})
in
replaceValSummer
in
Source
regardless of efficiency, just amend your code.
I changed the function to append the [ColumnToBeReplaced] with the contents from [ColumnToCheck] if the [ColumnToBeReplaced] already has text.
let
Source = (qrySource, ColumnToCheck as text, Condition as text, ColumnToBeReplaced as text) => let
Source = qrySource,
replaceColValue = Table.ReplaceValue(Source, each Record.Field(_,ColumnToBeReplaced),
each if Text.Contains(Record.Field(_,ColumnToCheck), Condition, Comparer.OrdinalIgnoreCase) then
(if Record.Field(_,ColumnToBeReplaced)="" then Record.Field(_,ColumnToCheck)
else
Record.Field(_,ColumnToBeReplaced) & " " & Record.Field(_,ColumnToCheck))
else
Record.Field(_,ColumnToBeReplaced), Replacer.ReplaceValue,{ColumnToBeReplaced}),
eraseValue = Table.ReplaceValue(replaceColValue, each Record.Field(_,ColumnToCheck),
each if Text.Contains(Record.Field(_,ColumnToBeReplaced), Condition, Comparer.OrdinalIgnoreCase) then ""
else
Record.Field(_,ColumnToCheck), Replacer.ReplaceValue,{ColumnToCheck})
in
eraseValue
in
Source
let
Source = (qrySource,ColumnToCheck as text,Condition as text,ColumnToBeReplaced as text) => let
Source = qrySource,
replaceSummer = Table.ReplaceValue(Source, each Record.Field(_,ColumnToBeReplaced), each if Text.Contains(Record.Field(_,ColumnToCheck), Condition, Comparer.OrdinalIgnoreCase) then Record.Field(_,ColumnToCheck) else Record.Field(_,ColumnToBeReplaced), Replacer.ReplaceValue,{ColumnToBeRelaced}),
replaceValSummer = Table.ReplaceValue(replaceSummer, each Record.Field(_,ColumnToCheck), each if Text.Contains(Record.Field(_,ColumnToBeReplaced), Condition, Comparer.OrdinalIgnoreCase) then "" else Record.Field(_,ColumnToCheck), Replacer.ReplaceValue,{ColumnToCheck})
in
replaceValSummer
in
Source
regardless of efficiency, just amend your code.
I think I solved my own question by just changing the entries in the formula.
= fxReplaceValue(_NotesMech, "Summer")
Hi @MarkusEng1998,
Intersting, it should not really as you pass the parameter as text and intend to use it as column, unless relevant rows in the [Summer] column = "Summer". A correct way to address (if necessary) a column from text would be something like Record.FieldOrDefault(_, colReplacement), where you refer colReplacement as a column.
Cheers,
John
Hi @jbwtp , yes this formula worked, but did not return the desired results. I will try Daniels solution instead.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
10 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |