Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
Regards,
David
Hi @vinaydavid
You can filter out all N/A before Group By
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"
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.
Regards,
David
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |