Forum Discussion
Helper IIIGroup 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
Helper IIII can't do that as some of the ID contains single dates and that happened to be N/A, which are valid.
Community ChampionHi 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"Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
- vinaydavidWill 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
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
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