Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ranjitsingh
Advocate I
Advocate I

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 better/more elegant solution, please let me know.

 

 

     Patient#                    Date of Last Visit                      Diagnosis Code

1001/1/2020120
10012/11/2019120
2007/19/201955
2007/4/2019220
2002/15/2018220
3008/8/202056
3003/2/202057
4007/7/201658
4005/26/201659

 

1) Using Group By from Home Tab, I grouped them by Patient# and did MAX operation on Date of Last Visit. But there was no way to pull in the corresponding Diagnosis Code for the MAX Date of Last Visit for each patient#, so I  asked for the whole

Table object in new column called Details.

 

ranjitsingh_1-1617135489580.png

 

2) When clicked OK, I got only the Most recent visit by each unique Patient# and the table object.

 

  Patient#                 Most recent visit                      Details

1001/1/2020[Table]
2007/19/2019[Table]
3008/8/2020[Table]
4007/7/2016[Table]

 

3) I need to reach within the Table object and select only the corresponding most recent Diagnosis Code. For example for patient# 200, I only need 55.

 

ranjitsingh_2-1617137073354.png

 

4) When I expanded the details column, choosing only Diagnosis code, I get the whole table back so that doesnt work.

 

 

ranjitsingh_3-1617137565517.png 

ranjitsingh_4-1617137705579.png

 

5) If I bring in Details.Date of Last Visit as well, I can compare the 2 dates  and only keep the rows where they are equal. This will give me the diagnosis code for the most recent date rows only, and the rest are null.

 

ranjitsingh_5-1617137874424.png

 

 

ranjitsingh_7-1617138882858.png

 

ranjitsingh_8-1617139037264.png

 

 

6) I filter out the null values from the Final Diagnosis Code column, to get the table I need.

 

ranjitsingh_9-1617139177531.png

 

7) So many steps! 😥

 

ranjitsingh_10-1617139409921.png

 

@selimovd @eilebre @lbendlin 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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.

Thank you! this is the simplest solution that I've found so far.  

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.