Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vinaydavid
Helper III
Helper III

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

1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
Mariusz
Community Champion
Community Champion

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

 

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.

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

 

Hi Mariusz

 

This step seems to be replacing the 'null' values with 'N/A' for Date field.

The need is to pick the date field if there are duplicates (where one is date other is N/A)

Also, there are other entries (without duplicates) where the values can be N/A. 

 

So I want to eliminate/Filter 'N/A', only when we have duplicates for the ID.

Is there a way, to filter out in this way?

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.