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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors