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

1        11        abc          N/A

1         11       abc        23/07/2019

2         22       xyz         01/08/2019

2         22       xyz         02/08/2019

 

To achieve this, I have used group by in power query editor and used 'Max' as the aggregation to pick Max date.

However, for the ID 1 scenario, N/A is being picked. But I need to pick the date in case the other entry is N/A.

For ID 2 scenario, its working perfectly fine.

 

Kindly help.

 

Cheers,

David

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

6 Replies

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

    Hi vinaydavid 

    You can filter out all N/A before Group By 

     

    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
      Thanks for your reply,
      I can't do that as some of the ID contains single dates and that happened to be N/A, which are valid.
      • 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