cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chouston1725
Regular Visitor

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

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
@Cam.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:

EmailTier
xxxxx@CAM.comTier 1
xxxx@MACK.comTier 2
xxxxx@LOLA.comTier 1

 

Thank you in advan

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

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.

edhans_0-1613081633455.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
Rocco_sprmnt21
Super User II
Super User II

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
Solution Sage
Solution Sage

Hi @Chouston1725 

 

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"

 

Vera_33_0-1613197987076.png

 

edhans
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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
Super User III
Super User III

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.

edhans_0-1613081633455.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.