CODEWITHSUNDEEP
powerbipowerquery
Joe Crozier
Joe Crozier

Reputation: 1036

True/False column based on most recent date of other column in Power Query

I have data like this in Power Query:

enter image description here

And I'd like to create a new column that is true/false. It's TRUE if a patient's most recent status date is associated with their "On Followup", i.e. they are currently on followup.

So for instance the new column would look like this:

enter image description here

Where Bob and Joe are true, but Tiffany and John are false. John has never been on followup, and Tiffany was but its not her most recent status.

Upvotes: 0

Views: 488

Answers (1)

horseyride
horseyride

Reputation: 21413

One way

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"data", each _, type table } ,{"status" ,each if Table.Sort(_,{{"Status_Date", Order.Descending}}){0}[Status] = "On Followup" then true else false } }),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Status", "Status_Date"}, {"Status.1", "Status_Date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data",{{"Status_Date", type date}})
in #"Changed Type"

Upvotes: 2

Related Questions