Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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.
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.
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...
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |