Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
brunofranc
Frequent Visitor

[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:

 

brunofranc_0-1665596015416.png

 

After :

 

brunofranc_1-1665596042940.png

Table for Copy Below:

id_transactiondate_hourchannelcampaign
41564584202201051122googlecamp1
41564584202201051123facebookcamp2
41564584202201071122tiktokcamp3
41564584202201061122affiliatecamp4
41564584202201051129googlecamp5
44544545202201051122facebookcamp6
54546546202201051122tiktokcamp7
45424545202201051122affiliatecamp8
45457877202201051122facebookcamp9
45457877202201051221tiktok

camp9

 

 

 
1 ACCEPTED SOLUTION
serpiva64
Super User
Super User

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 !

View solution in original post

1 REPLY 1
serpiva64
Super User
Super User

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 !

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors