Forum Discussion

vinaydavid's avatar
vinaydavid
Icon for Helper III rankHelper III
7 years ago
Solved

Group by - Aggregation (avoid N/A values and pick date)

Hi Experts,   I have a situation where my table contains entries duplicate entries and I am trying to pick the latest date out of that. Here is the Mock Up.   ID      No.      Event       Date ...
Need Help
  • vinaydavid's avatar
    vinaydavid
    7 years ago

    First of all thanks to Mariuz for this time on helping me.

     

    After spending a bit of time on checking everything that associated with these fields.

    Here are the finding and I made the below necessary changes to achieve the desired result.

     

    • The datatype of 'Date' field was 'Text' --> hence the behaviour of displaying N/A instead of Date, I reckon. Changed it to 'Date' then the aggregation was working fine.
    • To handle 'N/A' values --> I have replaced it with a dummy date '1/1/1970' and applied aggregation to pick Max date and once all the steps are done, replaced with N/A instead of dummy date.

    Regards,

    David

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

Hi vinaydavid 

Please see the below solution that will address valid N/A's

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIEEYlJyUDST99RKVYHQ9jIWN/AXN/IwNASLGsEEgIRFZVVQNLAUN/AAresEYqsMVDIGETk5uXDLIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"No." = _t, Event = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"No.", type text}, {"Event", type text}, {"Date", type date}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Date", null}}),
    #"Grouped Rows" = Table.Group(#"Replaced Errors", {"ID", "No.", "Event"}, {{"MaxDate", each List.Max([Date]), type date}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"MaxDate", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"N/A",Replacer.ReplaceValue,{"MaxDate"})
in
    #"Replaced Value"
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

vinaydavid's avatar
vinaydavid
Icon for Helper III rankHelper III
7 years ago
Will this work fine for scenarios like as well
3 33 Pqr N/A
4 44 mno N/A

Where N/A are valid as there are no duplicates...for ID 3 & 4