Power Query Editor: Changing data value if a row in a particular column is duplicate

Hi Anonymous ,

According to my understanding, if there are more than 1 row of each Date and Country code, then set value=0 , otherwise set the value = original Customers, right?

If so, please use GroupBy and add a custom column:

Output:

Below is the whole M syntx:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBBDsQgCAXQu7huUv8XKlxjklk1vf81ase0mCbORhbvE4F9T8zMK7AiLenzbY94ra2YMR3L3AFVHxKMxFb9Sjj41wFBvRPtgyFQ0EfwgV8IlM2C+Yxn1KvXyxwBVgSXWA3WWWTOjhwoNyoNvXcc682uGqjPSlT5lXaO4wQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Country Code" = _t, UoA = _t, Customers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Country Code", type text}, {"UoA", Int64.Type}, {"Customers", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Country Code"}, {{"Count", each _, type table [Date=nullable date, Country Code=nullable text, UoA=nullable number, Customers=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if Table.RowCount([Count]) >1 then 0 else Table.FirstValue(Table.SelectColumns([Count],"Customers"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"})
in
    #"Removed Columns"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.