Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables one with SQL as the datasource (Table1) the other (Table2) is sourced from a SharePoint list.
Table1
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:
Tier | |
xxxxx@CAM.com | Tier 1 |
xxxx@MACK.com | Tier 2 |
xxxxx@LOLA.com | Tier 1 |
Thank you in advan
Solved! Go to 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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingquery 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"
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"
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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"?
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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWow 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.