Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Program | Client ID | Date | ContactID |
Program1 | 12390 | 18/11/2020 | 106257 |
Program1 | 12390 | 13/08/2020 | 104049 |
Program1 | 12390 | 16/07/2020 | 103619 |
Program1 | 12390 | 09/07/2020 | 103506 |
Program2 | 11928 | 07/10/2021 | 114424 |
Program2 | 11928 | 04/10/2021 | 114261 |
Program2 | 11928 | 23/09/2021 | 114066 |
Program2 | 11928 | 15/07/2021 | 112515 |
Program2 | 11928 | 14/07/2021 | 112444 |
Program2 | 11928 | 12/07/2021 | 112351 |
Program2 | 11928 | 30/06/2021 | 112109 |
And I want the data to appear like this:
Program | Client ID | Date | ContactID |
Program1 | 12390 | 09/07/2020 | 103506 |
Program2 | 11928 | 30/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.
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
this maybe work
NewStep=Table.Distinct(Table.Sort(PreviousStepName,{"Date",1}),{"Program","Client"})
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.