Forum Discussion
How can I use a conditional statement in a custom function referencing a specific column?
Hello!
I created a custom function to replace values based on a table merge and return the "original value" & "_NOT FOUND" when there is no match.
I recorded the steps and then started replacing the hardcoded names with variables, however, I'm struggling to replace [
[Original A], which is in step TO_2.
If I smply try to replace it with COL_LEFT, I get the error "Couldn't apply the operator & to List and Text"
Original A Original B
| 1 | aaaaaaaaa |
| 2 | bbbbbbbb |
| 3 | cccccccccc |
| 4 | dddddddd |
From To
| 1 | 9 |
| 2 | 8 |
| 3 | 7 |
let BulkReplace =
(TAB_ORIGIN as table,
TAB_FROM_TO as table,
COL_LEFT as list,
COL_RIGHT as list,
COL_EXTRACTED as list ) =>
let
MERGE = Table.NestedJoin(TAB_ORIGIN , COL_LEFT, TAB_FROM_TO, COL_RIGHT,"New"),
EXPAND = Table.ExpandTableColumn(MERGE, "New", COL_EXTRACTED, {"TO"}),
TO_2 = Table.AddColumn(EXPAND, "TO_2", each if [TO] = null then [Original A] & "_NOT FOUND" else [TO]),
REMOVE = Table.RemoveColumns(TO_2,{"Original A", "TO"}),
RENAME = Table.RenameColumns(REMOVE,{{"TO_2", "Original A"}})
in
RENAME
in
BulkReplace
Current result:
Original B Original A
| aaaaaaaaa | 9 |
| bbbbbbbb | 8 |
| cccccccccc | 7 |
| dddddddd | error |
I expected the value "4_NOT FOUND" instead of error.
I understand that concatenation can only happen with equal data types, but doesn't the keyword each would be enough to loop through the items of the list and do the trick?
What am I missing here?
Hi Anonymous,
Just replace COL_LEFT in TO_2 to Record.Field(_ , COL_LEFT{0}). You are passing the list(with column names) , the first item should be the name of the column that you want to add the "_not_found" to.
Cheers,
John
8 Replies
Hi Anonymous,
Just replace COL_LEFT in TO_2 to Record.Field(_ , COL_LEFT{0}). You are passing the list(with column names) , the first item should be the name of the column that you want to add the "_not_found" to.
Cheers,
John
Thanks, jbwtp !
Inspired by your use of Record.Field() I tweaked the code a little and achieved my goal.
Instead of passing a list to the function I passed a 'text' containing the column name. This is beacuse my objective is to add "_NOT FOUND" to the column I'm passing:
let BulkReplace = (TAB_ORIGIN as table, TAB_FROM_TO as table, COL_LEFT_NAME as text, COL_RIGHT_NAME as text, COL_EXTRACTED as text) => let MERGE = Table.NestedJoin(TAB_ORIGIN , {COL_LEFT_NAME}, TAB_FROM_TO, {COL_RIGHT_NAME},"New"), EXPAND = Table.ExpandTableColumn(MERGE, "New", {COL_EXTRACTED}, {"TO"}), TO_2 = Table.AddColumn(EXPAND, "TO_2", each if [TO] = null then Record.Field(_, COL_LEFT_NAME) & "_NOT FOUND" else [TO]), REMOVE = Table.RemoveColumns(TO_2,{ COL_LEFT_NAME , "TO"}), RENAME = Table.RenameColumns(REMOVE,{{"TO_2", COL_LEFT_NAME }}) in RENAME in BulkReplaceI had previosuly read the documentation of Record.Field() but couldn't get how to use till you gave the example.
Hello Anonymous ,
Thank you very much for your help 🙂
What's the error message?
"We cannot apply the operator & to List and Text
Details:
Operator=&
Left=List
Right=_NOT FOUND"
"I am not sure why you need to convert the join as a custom function"
I have literally a dozen columns in the table in which I need to perform a 'bulk find and replace' using lookup tables. As the process of (1) merging, (2) expanding, (3) creating a custom column to return original value instead of null, (4) deleting the temporary columns and (5) renaming the custom column is going to be applied over and over, I thought it was wise to encapsulate it all in a function.So, I can keep the script on the main table clean and easy to explain to my boss.
"you can simply try to bring the value otherwise"
Yes, and your approach with 'try / otherwise' interested me, and it reduced what I first needed 5 steps to, (1) create a custom column, (2) delete the original, (3) rename the custom column.
Could I make it clear why a 'custom function' rather than just a 'custom column' was my choice?
Hi Anonymous
I mean your original solution was converting your steps to a custom function, and why it was not working when you try to replace hard coded column with COL_LEFT was because this COL_LEFT is a list when you defined.
Are you going to replace the values in 1 column at a time? If yes, you can convert this custom column approach to a custom function.