Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
In Table 1, a customer gets referred for three different products namely, motor, home & landlord insurance known as product referrals.The field, LEAD TYPE ID identifies a unique customer.
What's the best way to search for unique Customer Referrals identified by Lead Type ID as shown in Table 2?
For example, Search rows having same Lead Type ID and return 1 in the Customer Referral Count field only once?
Table 1:
LEAD_TYPE | LEAD_TYPE_ID | CUSTOMER_EMAIL | PRODUCT |
R | f88f271b-4a99-4c2d-9b6c-5a58c239082f | sbb@bigpond.com | PERSONAL_MOTOR |
R | f88f271b-4a99-4c2d-9b6c-5a58c239082f | sbb@bigpond.com | PERSONAL_HOME |
R | f88f271b-4a99-4c2d-9b6c-5a58c239082f | sbb@bigpond.com | PERSONAL_LANDLORD |
R | c97e9b0c-1bed-48a9-af36-d2057bccf4c5 | thnbills@gmail.com | PERSONAL_MOTOR |
R | c97e9b0c-1bed-48a9-af36-d2057bccf4c5 | thnbills@gmail.com | PERSONAL_HOME |
Table 2:
LEAD_TYPE | LEAD_TYPE_ID | CUSTOMER_EMAIL | PRODUCT | Customer Referral Count |
R | f88f271b-4a99-4c2d-9b6c-5a58c239082f | sbb@bigpond.com | PERSONAL_MOTOR | 1 |
R | f88f271b-4a99-4c2d-9b6c-5a58c239082f | sbb@bigpond.com | PERSONAL_HOME | |
R | f88f271b-4a99-4c2d-9b6c-5a58c239082f | sbb@bigpond.com | PERSONAL_LANDLORD | |
R | c97e9b0c-1bed-48a9-af36-d2057bccf4c5 | thnbills@gmail.com | PERSONAL_MOTOR | 1 |
R | c97e9b0c-1bed-48a9-af36-d2057bccf4c5 | thnbills@gmail.com | PERSONAL_HOME |
Solved! Go to Solution.
Hi @Tooba_kazmi
You can put the following code to advanced editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rc89C4MwFIXh/5LZWzQmmmwVFDqoKbabSMm9MVbwo2D/PxUKHTu5Hl4eOG3LGhYwr5TnaYQgrNYgiDvQmBBIKxXxWIeK+z3bEM84Dq91cSda5325Fs3N1Fn5qMzdNKwLjvIupioO5MqszkvT5D+SdNprDAki7B0IZTVYHyfgeChTJPKC5J69nwuO07Sdh9mO0//Th5Df390H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LEAD_TYPE = _t, LEAD_TYPE_ID = _t, CUSTOMER_EMAIL = _t, PRODUCT = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LEAD_TYPE", type text}, {"LEAD_TYPE_ID", type text}, {"CUSTOMER_EMAIL", type text}, {"PRODUCT", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"LEAD_TYPE", "LEAD_TYPE_ID"}, {{"Count",each Table.AddIndexColumn(_, "Index", 1, 1), type table }}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"CUSTOMER_EMAIL", "PRODUCT", "Index"}, {"CUSTOMER_EMAIL", "PRODUCT", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Count", each if [Index] = 1 then 1 else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Count", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"})
in
#"Removed Columns"
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.
Hi @Tooba_kazmi
You can put the following code to advanced editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rc89C4MwFIXh/5LZWzQmmmwVFDqoKbabSMm9MVbwo2D/PxUKHTu5Hl4eOG3LGhYwr5TnaYQgrNYgiDvQmBBIKxXxWIeK+z3bEM84Dq91cSda5325Fs3N1Fn5qMzdNKwLjvIupioO5MqszkvT5D+SdNprDAki7B0IZTVYHyfgeChTJPKC5J69nwuO07Sdh9mO0//Th5Df390H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LEAD_TYPE = _t, LEAD_TYPE_ID = _t, CUSTOMER_EMAIL = _t, PRODUCT = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LEAD_TYPE", type text}, {"LEAD_TYPE_ID", type text}, {"CUSTOMER_EMAIL", type text}, {"PRODUCT", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"LEAD_TYPE", "LEAD_TYPE_ID"}, {{"Count",each Table.AddIndexColumn(_, "Index", 1, 1), type table }}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"CUSTOMER_EMAIL", "PRODUCT", "Index"}, {"CUSTOMER_EMAIL", "PRODUCT", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Count", each if [Index] = 1 then 1 else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Count", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"})
in
#"Removed Columns"
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.
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |