Forum Discussion
Value for each Key for the most current Date
Hi All, Any clue on how to get the Diagnosis Code for each Patient#, for the most current Date of Last Visit (in Query Editor)? I got the solution but way too many steps I think...If there is a be...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc1BCgAhCEDRu7gOTMuys0T3v8akTGGbiP9Q5wTKGRIQEnJm/+53pQuMZEQjEDt1pHFE5IF6Oj8Te5eYaJDioqjnvrTQC/Lt3Xv9T3Rb1Kxr6Ht9uzBgrQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Patient#" = _t, #"Date of Last Visit" = _t, #"Diagnose Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of Last Visit", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date of Last Visit", Order.Descending}}), #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Patient#"}) in #"Removed Duplicates"There's a much simpler way. Sort the table by Date descending, and then Remove Duplicates for the Patient # column.
lbendlin
Super User
Super Userlet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc1BCgAhCEDRu7gOTMuys0T3v8akTGGbiP9Q5wTKGRIQEnJm/+53pQuMZEQjEDt1pHFE5IF6Oj8Te5eYaJDioqjnvrTQC/Lt3Xv9T3Rb1Kxr6Ht9uzBgrQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Patient#" = _t, #"Date of Last Visit" = _t, #"Diagnose Code" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of Last Visit", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date of Last Visit", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Patient#"})
in
#"Removed Duplicates"There's a much simpler way. Sort the table by Date descending, and then Remove Duplicates for the Patient # column.
Thank you! this is the simplest solution that I've found so far.