Forum Discussion
Remove duplicate rows from the table based on condition and summarize
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.
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"
1 Reply
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"