Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 f...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago
    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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