Problem with duplicate records

[MarkCorzo70]

Hi all I have the following and i can't find the solution, any advice is appreciated

Tag             Div          Event       Date

77AK9Y       04             04         20012020

77AK9Y       04             05         20012020

23AG4G      04             04         20012020

Desired result:

77AK9Y       04             05         20012020

23AG4G      04             04         20012020

Meaning if the event 5 exists for a record don't want to consider the event 4

Thanks

[Jimmy801]

Hello @MarkCorzo70 

this requires some writing of M-Code. I've prepared for you a solution that involves the Group-function with a special function, that filters for the max value in your Event-column. Check it out

let
	Source = #table
	(
		{"Tag","Div","Event","Date"},
		{
			{"77AK9Y","04    ","04   ","20012020"},	{"77AK9Y","04    ","05","20012020"},	{"23AG4G","04    ","04","20012020"}
		}
	),
    Group = Table.Group
	(
		Source, 
		{"Tag", "Div"}, 
		{{"AllRows", (tableint)=> let 
			RemoveColumns = Table.RemoveColumns(tableint, {"Tag", "Div"}),
			SelectMaxEvent = Table.SelectRows(RemoveColumns, each [Event]= List.Max(tableint[Event]))
		in 
			SelectMaxEvent,
		type table [Event=text, Date=text]}}
	),
    ExpandTable = Table.ExpandTableColumn(Group, "AllRows", {"Event", "Date"}, {"Event", "Date"})
in
	ExpandTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Mariusz · Accepted solution]

Hi @MarkCorzo70 

You can use Group By with Max on Event as per below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjd39LaMVNJRMoFiIwMDQyMDIwOlWB00WVN0WSNjR3cTd6x6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Tag = _t, Div = _t, Event = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tag", type text}, {"Div", Int64.Type}, {"Event", Int64.Type}, {"Date", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Tag", "Div", "Date"}, {{"Event", each List.Max([Event]), type number}})
in
    #"Grouped Rows"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn