[Defiant Power Query] - Keep only First Occurrence row info - Sales Table

[brunofranc]

It's hard to find a solution in Power Query.
Keep only the most recent occurrence based on id_transaction.
In the example below I show what the expectation would look like:

Before:

After :

Table for Copy Below:

id_transaction	date_hour	channel	campaign
41564584	202201051122	google	camp1
41564584	202201051123	facebook	camp2
41564584	202201071122	tiktok	camp3
41564584	202201061122	affiliate	camp4
41564584	202201051129	google	camp5
44544545	202201051122	facebook	camp6
54546546	202201051122	tiktok	camp7
45424545	202201051122	affiliate	camp8
45457877	202201051122	facebook	camp9
45457877	202201051221	tiktok	camp9

 

[serpiva64 · Accepted solution]

Hi,

you can try to apply these transformations:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc/BCoMwDIDhd+nZg4lJW59FPFSxUnR0h74/y2YdrK4b5NDAR/g7DIqANbEl1ShsEVtoGQBR1jXGdV/kMbvbHdTY1HAnq3fzMsW4ZY4VbvLtFLb0xl0F64yd92EPLp0t9KOlL8P5wMTP4esvi3D94iJJy1z5R7g5bjPh99tluD09G2vM35a+yhGhbBE8PgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id_transaction = _t, date_hour = _t, channel = _t, campaign = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"id_transaction"}, {{"MinIndex", each List.Min([Index]), type number}, {"AllRows", each _, type table [id_transaction=nullable text, date_hour=nullable text, channel=nullable text, campaign=nullable text, Index=number]}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"date_hour", "channel", "campaign", "Index"}, {"AllRows.date_hour", "AllRows.channel", "AllRows.campaign", "AllRows.Index"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded AllRows", "Custom", each if [MinIndex] = [AllRows.Index] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MinIndex", "AllRows.Index", "Custom"})
in
#"Removed Columns"

It function but i think there is a problem in your exemple:

the third row on 07 january 2022 is "most recent" respect the first row on 05 jan 2022.

In that case you need some more transformations

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !