Forum Discussion
[Defiant Power Query] - Keep only First Occurrence row info - Sales Table
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 | camp1 | |
| 41564584 | 202201051123 | camp2 | |
| 41564584 | 202201071122 | tiktok | camp3 |
| 41564584 | 202201061122 | affiliate | camp4 |
| 41564584 | 202201051129 | camp5 | |
| 44544545 | 202201051122 | camp6 | |
| 54546546 | 202201051122 | tiktok | camp7 |
| 45424545 | 202201051122 | affiliate | camp8 |
| 45457877 | 202201051122 | camp9 | |
| 45457877 | 202201051221 | tiktok | camp9 |
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 !
1 Reply
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 !