Forum Discussion

WannaBe's avatar
WannaBe
Frequent Visitor
2 years ago
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. ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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:

    1. 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.
    2. Expand the column that contains all rows except uncheck email column and turn off "Use original column name as prefix"
    3. 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"

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

WannaBe 

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"
WannaBe's avatar
WannaBe
Frequent Visitor
2 years ago

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"

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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:

    1. 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.
    2. Expand the column that contains all rows except uncheck email column and turn off "Use original column name as prefix"
    3. 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"