Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
2 years ago
Solved

Custom Power Query Function to replace a Text in a column base on conditions

Dear all,   I am approaching in these days to the customized function in Power Query, I am really new and not familiar with this topic. My target is to reaplace a value in a column table based on ...
Need Help
  • Mic1979's avatar
    Mic1979
    2 years ago

    Finally I found the solution:

     

    let
    Change_Text_Column=(Input_Table as table) =>
    Table.ReplaceValue (
    Input_Table,
    each [#"Text"],
    each if [#"Text"]="3" then "12"
    else [#"Text"],
    Replacer.ReplaceText,
    {"Text"})

    in Change_Text_Column

     

    This is the results obtained invoking the function:

     

    Start     Text

    3           12

    4           4

    5           5

    7           7

    9           9

     

    The point for me now is to use it adding a column in a table adding a column.

     

    It seems not possible to use as input parameter the same table where I want to add the column by invoking the function. I have an error message.

     

    Thanks for your help.

shafiz_p's avatar
shafiz_p
Super User
2 years ago

Hi Mic1979 Use this code to create input parameter.

let
Change_Text_Column = (TableName,ColumnName,Old_V,New_V) => Table.TransformColumns(
TableName,
{{ColumnName, each Text.Replace(_, Old_V, New_V), type text}}
)
in
Change_Text_Column

Call this function into your table or wherever you want to use it. Remember that the Text.Replace function operates on text values, so your column should be of type text. Additionally, make sure to enclose the column name within double quotation marks, as well as the Old_V and New_V values.

 

 

If I answered your question, please mark it as a solution!!