Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MarkusEng1998
Resolver II
Resolver II

Function uses parameter for a column

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

 

 

 

Parameter in a function 2022-08-22_14-31-41.jpg

 

What is the correct syntax to reference the desired column as a parameter?

Parameter in a function 2 2022-08-22_14-31-41.jpg

 

 

 

 

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!

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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.

View solution in original post

6 REPLIES 6
MarkusEng1998
Resolver II
Resolver II

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

 

wdx223_Daniel
Super User
Super User

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.

@wdx223_Danielthank you so much for the solution! 😁

 

It works perfectly!

MarkusEng1998
Resolver II
Resolver II

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.