how to check if a column contains any values present in a column of different table

[rishabjain237]

Hi #community,
Need your expertise on Power Query.
I have table named Master append which has a column named Email ID
another table named List of domains has a column named Tech Generic
Tech Generic contains email domains such as "@gmail.com",",ac.it" etc.....
I have 15 Lakh Email IDs and 1400 Email Domains.
I want to create a new column in Master append which says "Generic" if a Email ID contains any of the tech Generic Value or else it says "Business".
Example:
I have tried :
= Table.AddColumn(#"Renamed Columns6", "Custom",
each if Text.Contains(Text.Lower([lead.email_address]), Text.Lower(#"List of Domains"[Tech Generic])) then "Generic" else "Business")
but it through error in the values
@Daniel29195 @Idrissshatila @Ritaf1983 
request for your assistance,

Thanks and Regards,
Rishab Jain

[v-xinruzhu-msft]

Hi @rishabjain237 

Thanks for the solution @Vijay_A_Verma  offered and I want to offer some more information so that you can refer to.

You can create a new custom column and input the following code.

List.Accumulate(
    List.Numbers(0, Table.RowCount(#"List of Domains")), 
   "Business", 
    (state, current) => if 
    Text.Contains(Text.Lower([lead.email_address]),Text.Lower(#"List of Domains"[Tech Generic]{current})) then "Generic" else state)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Vijay_A_Verma]

In a custom column, put following

if List.Contains(#"List of domains"[Tech Generic], Text.AfterDelimiter([E Mail ID], "@"), (x,y)=>Text.Contains(x, y, Comparer.OrdinalIgnoreCase)) then "Generic" else "Business"

Since you are having a large number of rows, hence buffer this first and then use this buffered list. Complete sample code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk7Jc0jPzU3US87PVYrViVYqSywDCiRm5sBF0rISkxwqESrSMrLSMoACGfn5ELFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E Mail ID" = _t]),
    BuffList = #"List of domains"[Tech Generic],
    #"Added Custom1" = Table.AddColumn(Source, "Custom", each if List.Contains(BuffList, Text.AfterDelimiter([E Mail ID], "@"), (x,y)=>Text.Contains(x, y, Comparer.OrdinalIgnoreCase)) then "Generic" else "Business")
in
    #"Added Custom1"

 If you use below code, this will be 3 times faster than above

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk7Jc0jPzU3US87PVYrViVYqSywDCiRm5sBF0rISkxwqESrSMrLSMoACGfn5ELFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E Mail ID" = _t]),
    BuffList = #"List of domains"[Tech Generic],
    Custom1 = Table.FromRecords(List.Transform(Table.ToRecords(Source), (x)=> Record.AddField(x, "Custom", if List.Contains(BuffList, Text.AfterDelimiter(x[E Mail ID], "@"), (x,y)=>Text.Contains(x, y, Comparer.OrdinalIgnoreCase)) then "Generic" else "Business" )))
in
    Custom1

[rishabjain237]

Hi @Vijay_A_Verma 
Thanks for the reply.
I have tried the first solution it works but takes too much time as you said.
Decompressing all is really advanced stuff for me. I have tried to copy paste this into a new query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk7Jc0jPzU3US87PVYrViVYqSywDCiRm5sBF0rISkxwqESrSMrLSMoACGfn5ELFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E Mail ID" = _t]),
BuffList = #"List of Domains"[Tech Generic],
Custom1 = Table.FromRecords(List.Transform(Table.ToRecords(Source), (x)=> Record.AddField(x, "Custom", if List.Contains(BuffList, Text.AfterDelimiter(#"Master append"[lead.email_address], "@"), (x,y)=>Text.Contains(x, y, Comparer.OrdinalIgnoreCase)) then "Generic" else "Business" )))
in
Custom1

but got an expression error.
Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

It would be helpful if you could Guide me on how to make changes on this.
and make use of this new query.

I want this new column on the "Master append" table which is used on all the reports and uploaded in my power BI service with out refresh options.

[Vijay_A_Verma]

In place of Source = ...., you will need to place your source. 

The will be of this type of thing

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

[rishabjain237]

@Vijay_A_Verma 
Iam not working on working on excel.
I am using a API URL in which I edit the date and column names. the connection is "Web"

[Vijay_A_Verma]

Open you query, delete everything after Source line and paste below code. Source can be anything. I showed through Table.FromRows, it can come from a database, Excel or API.

So if if your query is like

Source = MyAPISource,

gjdh

in

ghdh

Delete everything from ghdh onwards. You just need to retain Source line. 

BuffList = #"List of Domains"[Tech Generic],
Custom1 = Table.FromRecords(List.Transform(Table.ToRecords(Source), (x)=> Record.AddField(x, "Custom", if List.Contains(BuffList, Text.AfterDelimiter(#"Master append"[lead.email_address], "@"), (x,y)=>Text.Contains(x, y, Comparer.OrdinalIgnoreCase)) then "Generic" else "Business" )))
in
Custom1

[rishabjain237]

Okay. Thanks for explaining it in so detailed manner,
I have added the steps. it says function in the values.
functions(_as any) as any is displayed when I click on it.
@Vijay_A_Verma 

[Vijay_A_Verma]

Please paste your complete query here.