cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vinaydavid Regular Visitor
Regular Visitor

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

Accepted Solutions
vinaydavid Regular Visitor
Regular Visitor

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

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 6
Super User
Super User

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

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 Regular Visitor
Regular Visitor

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

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.
Super User
Super User

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

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 Regular Visitor
Regular Visitor

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

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
vinaydavid Regular Visitor
Regular Visitor

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

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?

 

vinaydavid Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 170 members 2,344 guests
Please welcome our newest community members: