how to add trimming and lowering directly in a nestedjoin method ?

[Anonymous]

Hello,

I have a step like this :

= Table.NestedJoin(REQ1, {"EMail"}, REQ2, {"Email"}, "REQ2", JoinKind.Inner)

I would like to optimize the result by trimming and lowering both columns EMail of REQ1 and Email of REQ2.

Is there a way I can add my Text.Trim(Text.Lower()) directly in this step or do I have to do it absolutely at an earlier step ?

Thanks for your help,

[KT_Bsmart2gethe]

Hi @Anonymous ,

Replace the code below with the provided code.

let
  _REQ1 = 
     Table.TransformColumns( REQ1, { { "EMail", each Text.Trim( Text.Lowering(_) ) } } ),
  _REQ2 =
     Table.TransformColumns( REQ2, { { "Email", each Text.Trim( Text.Lowering(_) ) } } )
in 
  Table.NestedJoin(_REQ1, {"EMail"}, _REQ2, {"Email"}, "REQ2", JoinKind.Inner)

Regards

KT 

[Anonymous]

Hello, thank you but this adds a step. And this is what I wanted to avoid. Unless I can do like the following ?

= Table.NestedJoin(Table.TransformColumns( REQ1, { { "EMail", each Text.Trim( Text.Lowering(_) ) } } ), {"EMail"}, Table.TransformColumns( REQ1, { { "Email", each Text.Trim( Text.Lowering(_) ) } } ), {"Email"}, "REQ2", JoinKind.Inner)

Ideally, I would like something like this :

= Table.NestedJoin(REQ1, Text.Trim(Text.Lower({"EMail"})), REQ2, Text.Trim(Text.Lower({"Email"}), "REQ2", JoinKind.Inner)

Is is possible ?

[KT_Bsmart2gethe]

Hi @Anonymous ,

Table.NestedJoin(
        Table.TransformColumns( REQ1, { { "EMail", each Text.Trim( Text.Lowering(_) ) } } ), 
        {"EMail"}, 
        Table.TransformColumns( REQ2, { { "Email", each Text.Trim( Text.Lowering(_) ) } } ), 
        {"Email"}, 
        "REQ2", 
        JoinKind.Inner
)