Custom function: pass a column as a parameter

[bd92]

Hello,

I created a simple custom function that allows to replace values in a column. I need to reuse it in different queries, that don't share the same column name.

Let say that I want to replace "Hello" by "good morning", and "Bye" by "Ciao", in these two different queries, that have a different column name:

Query1:

My column

Hello

Bye

Hello

Query2:

Another column

Hello

Bye

Bye

So, I created this custom function named myFunction:

let Multiple = (mySource as table, columnName as text) =>
let
    #"ReplaceType"= Table.ReplaceValue(mySource,each [columnName],each if Text.Contains([columnName],"Hello",Comparer.OrdinalIgnoreCase) then "good morning" else [columnName],Replacer.ReplaceText,{columnName}),
    #"ReplaceType2"= Table.ReplaceValue(#"ReplaceType",each [columnName],each if Text.Contains([columnName],"Bye",Comparer.OrdinalIgnoreCase) then "Ciao" else [columnName],Replacer.ReplaceText,{columnName})
in
    #"ReplaceType2" in Multiple

And invoking this function (in this example with "My column" as a parameter, for Query1):

= myFunction(Query1, "My column")

 But it doesn't work, nothing get replaced.

If I hardcode the real column name in myFunction (and remove the second parameter columnName), then it works fine. But that solution isn't enough for me, knowing that I want to reuse this function with the column name as a parameter.

Any idea how I can solve this?

[retamapark]

I am experiencing the same issue.  I have a report that shows a date one day after the date of the activity.  For example, the report is run in the middle of the night, say on May 5, 2020, showing activity for May 4, 2020.  I want to write a custom Power Queryfunction that will subtract one day.  It works if the only parameter is a table name.  If I add a date field as a second parameter, no changes. 

This Works.

(tablename as table)=>
let
// Datefield must be named "Date"; text in replacer must be "Date"

PreviousDate = Table.ReplaceValue(tablename, each [Date], each Date.AddDays([Date], -1) , Replacer.ReplaceValue,{"Date"})

in

PreviousDate

This does not work.

(tablename, datefield as date)=>
let

PreviousDate = Table.ReplaceValue(tablename, each (datefield), each Date.AddDays((datefield), -1) , Replacer.ReplaceValue,{"Date"})

in

PreviousDate

Any ideas as to what is going on.  If interested, can test with a simple dataset, one column of dates.  Thanks.

[retamapark]

Took a different approach that works.

1.  In the function, change the type of the date field to number.

2.  Subtract 1 from the date field as number.

3.  Change the type back to date.

(tablename, date)=>
let
DateToNumber = Table.TransformColumnTypes(tablename,{{date, type number}}),
PreviousDate = Table.TransformColumns(DateToNumber, {{date, each _ - 1, type number}}),
TypeDate = Table.TransformColumnTypes(PreviousDate,{{date, type date}}),
Result = TypeDate
in

Result

[v-xicai]

Hi @bd92 ,

If you need to replace some values, you can click on the column, then right click to choose "Replace Values".

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bd92]

Hi,

The reason why I don't do a simple "replace values" is because I need two things not possible with this:

• I need to replace the entire cell content, and not only the value it self.
  For example, if the content is "Hello sir", I want it to become "good morning" and not "good morning sir"
• I need to reuse this function many times, with a column name different in many queries