Forum Discussion
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 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!
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"
2 Replies
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
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"
