Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1aaaaaaaaa
2bbbbbbbb
3cccccccccc
4dddddddd

 

From         To

19
28
37

 

 

 

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

aaaaaaaaa9
bbbbbbbb8
cccccccccc7
dddddddderror

 

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?

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

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

View solution in original post

8 REPLIES 8
jbwtp
Memorable Member
Memorable Member

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

Anonymous
Not applicable

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
    BulkReplace

 I had previosuly read the documentation of Record.Field() but couldn't get how to use till you gave the example.

Vera_33
Resident Rockstar
Resident Rockstar

Hi @Anonymous 

 

What's the error message? I am not sure why you need to convert the join as a custom function, you can simply try to bring the value otherwise

Vera_33_0-1664862348671.png

try TAB_FROM_TO[To]{List.PositionOf(TAB_FROM_TO[From], [Original A])} 
otherwise [Original A] & "_NOT FOUND"
Anonymous
Not applicable

Hello @Vera_33 ,

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.

Anonymous
Not applicable

:

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.


Yes, @Vera_33 .  I just don't know how to write such function 😞

Struggled here all day long trying to replace the 'hardcoded' names with variables but didn't succed.

Hi @Anonymous 

 

I see, here is one way, though TAB_FROM_TO is not used, but easier to explain...

(TAB_ORIGIN as table,
TAB_FROM_TO as table,
COL_LEFT as text,
COL_RIGHT as list,
COL_EXTRACTED as list) =>
let
    #"Added Custom" = Table.AddColumn(TAB_ORIGIN, "Custom", each
        [original = Record.FieldValues(Record.SelectFields(_, {COL_LEFT})){0}?,
        a=try COL_EXTRACTED{List.PositionOf(COL_RIGHT,  original)} otherwise original&"_not found"][a]),
        
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{COL_LEFT}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", COL_LEFT}})
in
    #"Renamed Columns"

 

Anonymous
Not applicable

Hello @Vera_33,

It returned 'not found' for all records.

I guess it has to do with the fact that there is no reference to the lookup table, isn't it?

Anotação 2022-10-07 141032.png

 

 

 

 

 

 

lookup table:

Anotação 2022-10-07 141409.png

 

 

 

 

 

 

 

function call:

let
    Fonte = Excel.CurrentWorkbook(){[Name="BASE"]}[Content],
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Original A", type text}, {"Original B", type text}}),
    BulkReplace = BulkReplace3 ( #"Tipo Alterado", DE_PARA, "Original A", {"De"}, {"Para"} )
in
    BulkReplace

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors