Forum Discussion

rpiboy_1's avatar
rpiboy_1
Helper V
3 years ago
Solved

Column Reference with text string

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 ...
Need Help
  • rpiboy_1's avatar
    rpiboy_1
    3 years ago

    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"



Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
rpiboy_1
Helper V
3 years ago

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"