cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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
Microsoft
Microsoft

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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors