Date of Last Visit by Key Value

[ranjitsingh]

Folks, I have a table with 2 columns:- Patient# and Date of Last Visit. Unfortunately, Date of Last Visit has multiple dates for each patient#. I want to keep only the most recent date per unique patient#. i.e. For each Patient#, return latest date.

It would be preferable to do this in query editor(M) if not then Data view (DAX). 

Thanks!

Patient#	Date of Last Visit
100	1/1/2020
100	12/11/2019
200	2/15/2018
200	7/4/2019
200	7/19/2019
300	8/8/2020
300	3/2/2020
400	7/7/2016
400	5/26/2016

@lbendlin @selimovd @CNENFRNL

[lbendlin]

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc3JDcAgDETRXnxGGtsQDLUg+m+DJShGuT7N6LdGwkyBBAJlZerhI4UslLpRN057lpXLDOk/M0h1i9sKiideilCndJ62jvmiGczH+gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Patient#" = _t, #"Date of Last Visit" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of Last Visit", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Patient#", Order.Ascending}, {"Date of Last Visit", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Patient#"})
in
#"Removed Duplicates"

[ranjitsingh]

Thanks so much! This query is mostly correct (other than Patient# 200). Added grouping to make it work...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc3JDcAgDETRXnxGGtsQDLUg+m+DJShGuT7N6LdGwkyBBAJlZerhI4UslLpRN057lpXLDOk/M0h1i9sKiideilCndJ62jvmiGczH+gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Patient#" = _t, #"Date of Last Visit" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of Last Visit", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Patient#", Order.Ascending}, {"Date of Last Visit", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Patient#"}, {{"Real DOFLV", each List.Max([Date of Last Visit]), type nullable date}})
in
#"Grouped Rows"

[selimovd · Accepted solution]

Hey @ranjitsingh ,

if you want to do it in Power Query that's totally possible.

In Power Query copy the original query and group by patient and Max value of the date of last visit. Here is a tutorial how to do that:

How to Group By Maximum Value using Table.Max - Power Query (gorilla.bi)

Afterwards join this grouped table to the original one. Then you have the max date per Patient.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[ranjitsingh]

Thanks so much! This worked for me.