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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
STIBBS_807
Resolver I
Resolver I

The first date in list of dates

Good day Newbie here,

 

I  have a list of contact dates and I would like to filter out only the last contact for the group under a program.  So here is my  Data

 

ProgramClient IDDateContactID
Program1  1239018/11/2020  106257
Program1  1239013/08/2020  104049
Program1  1239016/07/2020  103619
Program1  1239009/07/2020  103506
Program2  1192807/10/2021  114424
Program2  1192804/10/2021  114261
Program2  1192823/09/2021  114066
Program2  1192815/07/2021  112515
Program2  1192814/07/2021  112444
Program2  1192812/07/2021  112351
Program2  1192830/06/2021  112109

 

And I want the data to appear like this:

 

ProgramClient IDDateContactID
Program1  1239009/07/2020  103506
Program2  1192830/06/2021  112109

 

I remember somewhere reading a post on doing a sort by the column decending then doing a delete duplicates.  But this did not work for me.  I believe I have miss a step.  Wondered if someone has the solution for this issue.  

 

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Here is one way to do it. First, Group the table by Program, keeping All Rows. Then add the custom Table.TransformColumns( ) step to keep only the Min record based on the Date column.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdCxCsMwDATQXwmeA76TZTn+i+4hQ6ZOpZD/H+qQFBJDtInjIU6a5/Davu9t/TCMYRgoqaJNnCIZBYIjhkkuYRmfeIqYrlyh1eEWUa48GR2O2vMMu3I5claZdl4isfNzC1VFHa49F+Mzl3ZqvXGYU4b57P7nkpkdrj1XdbpTep6y0z0hwm6caH9ffg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Program = _t, #"Client ID" = _t, Date = _t, ContactID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Program", type text}, {"Client ID", Int64.Type}, {"Date", type text}, {"ContactID", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Program"}, {{"AllRows", each _, type table [Program=nullable text, Client ID=nullable number, Date=nullable date, ContactID=nullable number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Min(_, "Date")}}),
    #"Expanded AllRows" = Table.ExpandRecordColumn(Custom1, "AllRows", {"Client ID", "Date", "ContactID"}, {"Client ID", "Date", "ContactID"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Client ID", Int64.Type}, {"Date", type date}, {"ContactID", Int64.Type}})
in
    #"Changed Type1"

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

this maybe work

NewStep=Table.Distinct(Table.Sort(PreviousStepName,{"Date",1}),{"Program","Client"})

mahoneypat
Employee
Employee

Here is one way to do it. First, Group the table by Program, keeping All Rows. Then add the custom Table.TransformColumns( ) step to keep only the Min record based on the Date column.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdCxCsMwDATQXwmeA76TZTn+i+4hQ6ZOpZD/H+qQFBJDtInjIU6a5/Davu9t/TCMYRgoqaJNnCIZBYIjhkkuYRmfeIqYrlyh1eEWUa48GR2O2vMMu3I5claZdl4isfNzC1VFHa49F+Mzl3ZqvXGYU4b57P7nkpkdrj1XdbpTep6y0z0hwm6caH9ffg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Program = _t, #"Client ID" = _t, Date = _t, ContactID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Program", type text}, {"Client ID", Int64.Type}, {"Date", type text}, {"ContactID", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Program"}, {{"AllRows", each _, type table [Program=nullable text, Client ID=nullable number, Date=nullable date, ContactID=nullable number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Min(_, "Date")}}),
    #"Expanded AllRows" = Table.ExpandRecordColumn(Custom1, "AllRows", {"Client ID", "Date", "ContactID"}, {"Client ID", "Date", "ContactID"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Client ID", Int64.Type}, {"Date", type date}, {"ContactID", Int64.Type}})
in
    #"Changed Type1"

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors