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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors