Delete Duplicates based on multiple criteria

[ExcelDavid4]

Attached is a sample workbook that needs to be cleaned up. I need to remove duplicates based on multiple criteria. The criteria are Project Number, Invoice Business Unit and Total. Where it confuses me is that I need to compare these records against each other and delete duplicates on the following conditions.

Does the Project Number Match?
Does the Invoice Business Unit Match?
If the record meets both conditions and the total is zero and is the only one, it stays
If the record meets both conditions and the total is zero and is not the only one, all duplicate records go
If the Project Number matches but the Invoice Business Unit doesn't match, then it stays
If nothing matches, it stays

I hope the example explains this better than what I've typed above. Everything staying is in green, and everything that would be deleted is in yellow. Which zero dollar record that gets deleted is irrelevant as long as one remains from the matching conditions.

Any help or insight would be greatly appreciated.

[sandy666]

is that what you want?

Total	Invoice Business Unit	Project Number				Total	Invoice Business Unit	Project Number	Index
$0.00	Department A	Customer 1234		1		0	Department A	Customer 1234	1
$0.00	Department A	Customer 1234		2		0	Department B	Customer 1234	4
$0.00	Department A	Customer 1234		3		0	Department B	Customer 2345	6
$0.00	Department B	Customer 1234		4		89	Department B	Customer 2345	7
$0.00	Department A	Customer 1234		5		89	Department C	Customer 0812	8
$0.00	Department B	Customer 2345		6		0	Department D	Customer 0812	9
$89.00	Department B	Customer 2345		7		0	Department C	Customer 0812	10
$89.00	Department C	Customer 0812		8
$0.00	Department D	Customer 0812		9
$0.00	Department C	Customer 0812		10
$0.00	Department D	Customer 0812		11

Ijndex is for visualization only

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Total", Int64.Type}, {"Invoice Business Unit", type text}, {"Project Number", type text}}),
    Idx = Table.AddIndexColumn(Type, "Index", 1, 1),
    Distinct = Table.Distinct(Idx, Table.ColumnNames(Type))
in
    Distinct

[HansDouwe]

Is =UNIQUE(Table1) what you want?

See attachment.

[ExcelDavid4]

Hans,

This provides data similar to Sandy's solution. Close but not exact. Thank you for replying.

[ExcelDavid4]

Sandy,

That is close, but for Customer 2345, I only need the one record that has money on it. The second Customer record that matches Department B and has no money would need to go. All records with money on it would also need to be kept.

[sandy666]

Sandy,
That is close, but for Customer 2345, I only need the one record that has money on it. The second Customer record that matches Department B and has no money would need to go. All records with money on it would also need to be kept.

like this?

Total	Invoice Business Unit	Project Number				Total	Invoice Business Unit	Project Number	Index
$0.00	Department A	Customer 1234		1		0	Department A	Customer 1234	1
$0.00	Department A	Customer 1234		2		0	Department B	Customer 1234	4
$0.00	Department A	Customer 1234		3		89	Department B	Customer 2345	7
$0.00	Department B	Customer 1234		4		89	Department C	Customer 0812	8
$0.00	Department A	Customer 1234		5		0	Department D	Customer 0812	9
$0.00	Department B	Customer 2345		6
$89.00	Department B	Customer 2345		7
$89.00	Department C	Customer 0812		8
$0.00	Department D	Customer 0812		9
$0.00	Department C	Customer 0812		10
$0.00	Department D	Customer 0812		11

or post example of proper result

[ExcelDavid4]

Sandy,

Yes that's it! How did you accomplish that?

[sandy666]

Just Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Total", Int64.Type}, {"Invoice Business Unit", type text}, {"Project Number", type text}}),
    Idx = Table.AddIndexColumn(Type, "Index", 1, 1),
    Distinct = Table.Distinct(Idx, Table.ColumnNames(Type)),
    Asc = Table.Sort(Distinct,{{"Total", Order.Ascending}}),
    DDistinct = Table.Distinct(Asc, {"Total", "Invoice Business Unit"}),
    DDDistinct = Table.Distinct(DDistinct, {"Invoice Business Unit", "Project Number"}),
    TSRAsc = Table.Sort(DDDistinct,{{"Index", Order.Ascending}}),
    RC = Table.RemoveColumns(TSRAsc,{"Index"})
in
    RC

explanation:

1. let:
   This keyword indicates the start of the Power Query expression.
2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]:
   This line assigns the variable Source to the content of a table named "Table1" in the current workbook. It assumes that there is a table named "Table1" in the Excel workbook where the code is running.
3. Type = Table.TransformColumnTypes(Source,{{"Total", Int64.Type}, {"Invoice Business Unit", type text}, {"Project Number", type text}}):
   This line creates a new variable Type by transforming the column types of the Source table. It specifies that the "Total" column should be of type Int64, and the "Invoice Business Unit" and "Project Number" columns should be of type text.
4. Idx = Table.AddIndexColumn(Type, "Index", 1, 1):
   This line adds a new column named "Index" to the Type table. The "Index" column contains incremental values starting from 1.
5. Distinct = Table.Distinct(Idx, Table.ColumnNames(Type)):
   This line creates a new variable Distinct by removing duplicate rows from the Idx table based on all columns of the Type table.
6. Asc = Table.Sort(Distinct,{{"Total", Order.Ascending}}):
   This line sorts the Distinct table in ascending order based on the "Total" column.
7. DDistinct = Table.Distinct(Asc, {"Total", "Invoice Business Unit"}):
   This line creates a new variable DDistinct by removing duplicate rows from the Asc table based on the "Total" and "Invoice Business Unit" columns.
8. DDDistinct = Table.Distinct(DDistinct, {"Invoice Business Unit", "Project Number"}):
   This line further removes duplicate rows from the DDistinct table based on the "Invoice Business Unit" and "Project Number" columns.
9. TSRAsc = Table.Sort(DDDistinct,{{"Index", Order.Ascending}}):
   This line sorts the DDDistinct table in ascending order based on the "Index" column.
10. RC = Table.RemoveColumns(TSRAsc,{"Index"}):
    This line creates a new variable RC by removing the "Index" column from the TSRAsc table.
11. in RC:
    This line indicates the end of the Power Query expression and returns the RC table as the result.

Overall, the code performs several transformations on the initial data source (Source), including type conversion, removing duplicates, adding an index column, and sorting the data based on specific columns. The final result is a table (RC) with the desired transformations applied.

Total	Invoice Business Unit	Project Number
0	Department A	Customer 1234
0	Department B	Customer 1234
89	Department B	Customer 2345
89	Department C	Customer 0812
0	Department D	Customer 0812

[tanasedn]

Hi,
You can do this using sort and remove duplicates