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

[Anonymous]

Hi, I would like to make "Customers" column value = 0 if "Date" and "Country Code" column is duplicate.

Below is the sample data. For example, I want to change "Customers" value to 0 if "Date" and "Country Code" value are 11/1/2020 and RU respectively. Would also appreciate if there is a way to remove the duplicates together with changing the values to 0

Does anyone know how I can do it? Thank you

[Anonymous · Accepted solution]

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.

[Vera_33]

Hi @Anonymous 

Can you be more specific? For the first two rows, they have the same Date and Country code but different values in Customers, you want to remove both rows or to keep one of them, if keep one, which one? Please also provide sample data in a format which people can copy...