Conditional Column Based on Three Criteria

[subirch]

Hi,

I and trying to calculate the "Tag" column based on two criteria for each customer. If Product is "Pd A" and sales is >=1,000,000 then "Yes" for each rows wherever same customer id is appearing else "No". The data set is large. I need the solution in PowerQueary not DAX.

Customer ID	Product	Sales	Tag
444444	Pd A	1,000,000	Yes
444444	Pd B	76,000	Yes
444444	Pd C	5,600	Yes
444444	Pd E	4,000	Yes
777777	Pd A	2,000,000	Yes
777777	Pd B	54,398	Yes
777777	Pd C	40,000	Yes
777777	Pd E	2,000	Yes
999999	Pd A	100,000	No
999999	Pd B	2,000,000	No
999999	Pd C	50,000	No
999999	Pd E	453,666	No

[AlienSx]

hi @subirch 

let
    data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxDoAgDIXhq5DOHRBLkVGNuzth8/5nUCFBtPUlhuUL+TEloDJA2A8zX4epG9Bae3+Q8Y2WB9UFVt36dcZ4ZAVuElK7MZSJPPfK65HI84RjnIQTeaTfp9S5BmOZqCs/sOvr2fL3iB6JOK+7Txz5EZkZcj4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, Product = _t, Sales = _t]),
    source = Table.TransformColumnTypes(data,{{"Sales", Currency.Type}}),
    g = Table.Group(source, {"Customer ID"}, {{"sales", each _, type table [Customer ID=nullable text, Product=nullable text, Sales=nullable number, Tag=nullable text]}}),
    tag = Table.AddColumn(g, "Tag", (x) => if x[sales]{[Product="Pd A"]}[Sales] >= 1000000 then "Yes" else "No"),
    expand = Table.ExpandTableColumn(tag, "sales", {"Product", "Sales"}, {"Product", "Sales"})
in
    expand

[wickkey]

Hi,

You can try the if..else statement in the power query using custom column as below:

if [Product] = "Pd A" and [Sales] > 1000000 then "Yes" else "No"

Thanks!

[subirch]

This is not what I am looking. I need same tag for all rows for same customer.