If column in Table1 contains text from a column in Table2 assign label

[Anonymous]

I have two tables one with SQL as the datasource (Table1) the other (Table2) is sourced from a SharePoint list.

Table1

Email

xxxxx@CAM.com

xxxx@MACK.com

xxxxx@LOLA.com

Table2

Domain

@Anonymous.com

@Lola.com

I would like to create a new column in Table1 that will assign "Tier 1" if the email in Table1 contains the Domain from Table2 and if else assign "Tier 2"

Desired Outcome Example:

Email	Tier
xxxxx@CAM.com	Tier 1
xxxx@MACK.com	Tier 2
xxxxx@LOLA.com	Tier 1

Thank you in advan

[Anonymous]

query domain:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnBOzNVLzs9VitUBcnzycxIhvFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Domain = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Domain", type text}}),
    #"Testo in minuscolo" = Table.TransformColumns(#"Modificato tipo",{{"Domain", Text.Lower, type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Testo in minuscolo", "Value", each "Tier 1"),
    #"Rinominate colonne" = Record.FromTable(Table.RenameColumns(#"Aggiunta colonna personalizzata",{{"Domain", "Name"}}))
in
    #"Rinominate colonne"

query email:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqgABB2dHX73k/FylWB2IiIOvo7M3ikiFg4+/jyNEKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t]),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "tier", each Record.FieldOrDefault(domain,"@"&Text.Lower(Text.AfterDelimiter([Email],"@")),"Tier 2"))
in
    #"Aggiunta colonna personalizzata"

[Vera_33]

Hi @Anonymous 

If you have a lot of data, buffer the Table2List with List.Buffer

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqgABB2dHX73k/FylWB2IiIOvo7M3ikiFg4+/jyNEKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t]),
Table2List = List.Distinct(List.Transform( Table2[Domain], each Text.Lower(_))),
#"Added Custom" = Table.AddColumn(Source, "Tier", each [
a="@"&Text.Lower( Text.Trim( Text.AfterDelimiter([Email],"@"))),
b = if List.Contains(Table2List,a) then "Tier 1" else "Tier 2"][b])
in
#"Added Custom"

[edhans]

Yes. Just remove those other columns. My query only adds one column, and only uses the email address column. Any other columns you can remove before or after my code. If that isn't clear, you'll need to post screenshots of what you are talking about.

[edhans]

See my post above. There is a blog article at the bottom of it (step 5) that shows you how to use my M code but connect it to your actual tables.

[Anonymous]

I did read through that and it was able to help me get the table data in so thank you. Now the query is pulling all columns from Table1 into the new query. Is it possible to make the query only 2 columns "Email" and new column "Tier"? 

[edhans · Accepted solution]

This will do it all in one step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqgABB2dHX73k/FylWB2IiIOvo7M3ikiFg4+/jyNEKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t]),
    AssignTier = 
        Table.AddColumn(
            Source,
            "Tier",
            each
                let
                    varDomain = "@" & Text.Lower(Text.AfterDelimiter([Email],"@"))
                in
            if
                List.Contains(
                    List.Buffer(
                        List.Distinct(
                            Table.TransformColumns(Table2,{{"Domain", Text.Lower, type text}})[Domain]
                        )
                    ),
                    varDomain
                )
            then "Tier 1" else "Tier 2"
        ) 
in
    AssignTier

Power Query is case sensitive, so I had to make sure everythign was lower case for the match to work.

It basically does this:

• Gets the domain of the current email address
• takes your Table2 list of domains and
  • sets them to lower case
  • creates a distinct list (this may not be necessary)
  • Buffers the list for performance
  • Uses List.Contains return true or false to see if the email domain is in the domain table.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

I cannot guarantee performance on a large table. But if this is 100% on SQL I suspect I could get the entire thing to fold and let the server do all of work. Not with the code above (it might!!).

You can read my blog here on List.Contains and how to make folding work.

[Anonymous]

Wow this was a lot of help, not quite there yet. @edhans 

The data I posted was example data. In my situation, Table1 and Table2 are constantly changing. 

Not quite sure how it worked but the code provided actually places the emails above in the email column instead of using the actual email column in my dataset.

I think my question is how do I change the beginning of the M code to reference the email addresses in my actual data? And not the example emails provided above.