Column Reference with text string

[rpiboy_1]

I've written a function to peform condtional text replacement over multiple columns. However, when I try to write the code as a tru function I'm running into issues with a Column Reference, where my code requires a [ColumnName], but in functions you can't pass a Column, you can only pass a text string as the ColumnName. How do I convert the text string to the proper format to call the Column? I thought I had come up with a solution, but it does not work.

(tbl as table, ReplaceColumn as text, ConditionalColumn as text)=>
let
    #"ColumnAsList" = Table.FromList( Table.Column(tbl, ConditionalColumn) ),
    #"Replaced Value" = Table.ReplaceValue(
        tbl,
        each ReplaceColumn,
        each if #"ColumnAsList"[Column1] = 2 //[Room Data status code] = 2
            then null
            else if #"ColumnAsList"[Column1] = 0//[Room Data status code] = 0
            then ""
            else Record.Field(_, ReplaceColumn),//[Room Data Status],
        Replacer.ReplaceValue,
        {ReplaceColumn})
in
    #"Replaced Value"

The arguement 'ConditoinalColumn' is the string in question.  You can see where in #"ColumnAsList" I thought I could get the column from the table as a list, then convert it to a single column table. Then I assumed I'd be able to reference the default 'Column1' from this 'temporary' table in the #"Replaced Value" step. However, that does not seem to work.

Not that this query works fine, when it is in context and targeting a specific column, in which case I can hard code the column reference as appropriate with [ColumnName].

Appreciate any suggestions!

[watkinnc]

In your function parameters, try changing to  (til as table, valueToSearch as record, ReplaceColumn as text)

Then change "each ReplaceColumn" with "each ColumnToSearch". Leave your "Replacer.ReplaceValue({ReplaceColumn})" as is.

Seems that your issue is that you are using each  with a function that returns "some text value", but each has to be used with a record, if I know my M correctly, so keep that in mind if my solution gives you any trouble.

--Nate

[rpiboy_1 · Accepted solution]

The final solution ended up being much, much simpler. I realized it was way easier to insert a Table.RenameColumns function at the start, and rename the two incoming columns to values that I control, go through my Find/Replace function. Then rename the columns back to what they were at the start. I also had to clean-up my data type coming out of the Find/Replace function. Is there a method to direclty reference the Record, maybe, but this is pretty elegant and straight-forward.

(tbl as table, ReplaceColumn as text, ConditionalColumn as text)=>
let
    #"RenameReplaceColumn" = Table.RenameColumns(tbl,{{ReplaceColumn, "ReplaceColumnName"}, {ConditionalColumn, "CondColumnName"}}),
    #"Replaced Value" =
        Table.ReplaceValue(
            #"RenameReplaceColumn",
            each [ReplaceColumnName],
            each if [CondColumnName] = 2 
                then null
                else if [CondColumnName] = 0
                then ""
                else Record.Field(_, "ReplaceColumnName"),
            Replacer.ReplaceValue,
            {"ReplaceColumnName"}
        ),
    #"ChangeTypeText" = Table.TransformColumnTypes(#"Replaced Value", {{"ReplaceColumnName", type text}}),
    #"RenameReplaceColumn1" = Table.RenameColumns(#"ChangeTypeText",{{"ReplaceColumnName", ReplaceColumn}, {"CondColumnName", ConditionalColumn}})
    
in
    #"RenameReplaceColumn1"