Forum Discussion
Solved
How to flag oldest data by group
Hello,
I am trying to identify (include / exclude) with a flag some Email by grouping them by Email and date.
Only the oldest entries by Email, need to be Exclude.
Here an exemple of what I need. I already have all the columns, except the "Flag" one.
| DetailDate | Flag | Date | |
| 2021-11-25 18:30:33 | [email protected] | Exclude | 2021-11-25 |
| 2021-11-25 18:33:15 | [email protected] | Exclude | 2021-11-25 |
| 2021-11-25 18:35:42 | [email protected] | Exclude | 2021-11-25 |
| 2021-11-25 18:46:51 | [email protected] | Exclude | 2021-11-25 |
| 2021-11-29 10:15:33 | [email protected] | Include | 2021-11-29 |
| 2021-11-29 11:45:22 | [email protected] | Include | 2021-11-29 |
| 2021-12-05 18:35:42 | [email protected] | Include | 2021-12-05 |
| 2021-12-05 18:46:51 | [email protected] | Include | 2021-12-05 |
| 2023-06-06 18:35:42 | [email protected] | Exclude | 2023-06-06 |
| 2023-06-06 18:46:51 | [email protected] | Exclude | 2023-06-06 |
| 2024-02-12 10:15:33 | [email protected] | Include | 2024-02-12 |
| 2024-02-12 11:45:22 | [email protected] | Include | 2024-02-12 |
| 2024-03-03 18:35:42 | [email protected] | Include | 2024-03-03 |
| 2024-03-03 18:46:51 | [email protected] | Include | 2024-03-03 |
Thank you in advance for your help!
WannaBe I just went with the names of the columns you showed in your sample data. If your column is named something else you will have to adjust. Alternatively, perhaps the actual steps will help. Get all of your transformations done that you want to clean up your data. Then do the following:
- Select Group By in the ribbon of the Home tab. Turn on Advanced. Group by Email column. Create an aggregation that returns the Minimum of Date. Create another aggregation that returns all rows.
- Expand the column that contains all rows except uncheck email column and turn off "Use original column name as prefix"
- Add a conditional column where if the minimum data aggregation column and the data column are equal to one another then return "Exclude" else return "Include"
4 Replies
Thank you Greg_Deckler for your help!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdGxCoMwEAbgV5HMpuQuidSbunToM4iDWIeCaadC7dM3AYvWxIRqcJDA/3H3p6oYCgQOwFFncCQpSEqWs8adkxk609z6Q/sw9u78avvntbN/U4bVuUdIAr2T0KRwH6EK0rCZKDMQdov1Li53jyg9AkhpwrVFEgRykexiSbhMgIh1ESMkF4X95lO83YnVOWYCxHeKfwnFBdq55i8SIn4WGTMeMb3IBsLOJVNdLAmXCRCxLsJE/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DetailDate = _t, Email = _t, Flag = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DetailDate", type datetime}, {"Email", type text}, {"Flag", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Email"}, {{"MinDate", each List.Min([Date]), type nullable date}, {"Rows", each _, type table [DetailDate=nullable datetime, Email=nullable text, Flag=nullable text, Date=nullable date]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"DetailDate", "Flag", "Date"}, {"DetailDate", "Flag", "Date"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded Rows", "Custom", each if [Date] = [MinDate] then "Exclude" else "Include") in #"Added Conditional Column"Thank you Greg_Deckler for your answer.
I apply your suggestion but I got the following error :
Sorry... We could not find the column "DetailDate" in the table.Here the code that I have so far...
let Source = PowerPlatform.Dataflows(null), Workspaces = Source{[Id="Workspaces"]}[Data], #"92106211-0a06-45cd-b045-beec2a539839" = Workspaces{[workspaceId="92106211-0a06-45cd-b045-beec2a539839"]}[Data], #"bd89b90e-eb4b-4247-ada9-3ab511e01fa9" = #"92106211-0a06-45cd-b045-beec2a539839"{[dataflowId="bd89b90e-eb4b-4247-ada9-3ab511e01fa9"]}[Data], phonecall_ = #"bd89b90e-eb4b-4247-ada9-3ab511e01fa9"{[entity="phonecall",version=""]}[Data], #"Autres colonnes supprimées" = Table.SelectColumns(phonecall_,{"actualstart", "internalemailaddress"}), #"Date insérée" = Table.AddColumn(#"Autres colonnes supprimées", "Date", each Date.From([actualstart]), type date), #"Lignes triées" = Table.Sort(#"Date insérée",{{"internalemailaddress", Order.Ascending}, {"actualstart", Order.Ascending} }), #"Colonnes renommées" = Table.RenameColumns(#"Lignes triées",{{"actualstart", "DetailDate"}, {"internalemailaddress", "Email"}}), #"Personnalisée ajoutée" = Table.AddColumn(#"Colonnes renommées", "Flag", each "Include"), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DetailDate", type datetime}, {"Email", type text}, {"Flag", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Email"}, {{"MinDate", each List.Min([Date]), type nullable date}, {"Rows", each _, type table [DetailDate=nullable datetime, Email=nullable text, Flag=nullable text, Date=nullable date]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"DetailDate", "Flag", "Date"}, {"DetailDate", "Flag", "Date"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded Rows", "Custom", each if [Date] = [MinDate] then "Exclude" else "Include") in #"Added Conditional Column"WannaBe I just went with the names of the columns you showed in your sample data. If your column is named something else you will have to adjust. Alternatively, perhaps the actual steps will help. Get all of your transformations done that you want to clean up your data. Then do the following:
- Select Group By in the ribbon of the Home tab. Turn on Advanced. Group by Email column. Create an aggregation that returns the Minimum of Date. Create another aggregation that returns all rows.
- Expand the column that contains all rows except uncheck email column and turn off "Use original column name as prefix"
- Add a conditional column where if the minimum data aggregation column and the data column are equal to one another then return "Exclude" else return "Include"
