How to identify rows with same ID and return a count of "1" in a separate column only once?

[Tooba_kazmi]

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

[v-xinruzhu-msft · Accepted 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.