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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.