Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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 !
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 !