Remove duplicate rows from the table based on condition and summarize

[domi25]

I have a very large table which I want to transform in the most optimal way. I need to have:

- Unique ID 

- SUM of Revenue  which depends on

* Gender, if Sale Id have both Male and Female I only need to get the Females ones,

* if i only Have one Gender "Male" i also need to check the date, if date is 2023 i want to have this row if the date is 2024 i don;t want this row

- and a lot of other colums.

[amustafa · Accepted solution]

In Power Query, you can filter your data first then group it. Here's a sample code that works against your sample data and expected outcome.

let
Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\Desktop\Book1.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table, {
{"Sale ID", Int64.Type}, {"Date", Int64.Type}, {"Place", type text}, {"Gender", type text}, {"Revenue", Int64.Type}
}),

// Filter rows where Gender is Female or where Date is not 2024
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Gender] = "Female" or [Date] <> 2024),

// Group by Sale ID, Date, and all other columns
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Sale ID", "Date", "Place", "Gender"}, {
{"Total Revenue", each List.Sum([Revenue]), type number}
})
in
#"Grouped Rows"