Using Power query to group patient visits within a date range

user_18072784 0
Viewed 118

I have a list of patients with visit effective from dates that fall within the effective dates of their initial visits that i don't need to bill. The effective dates start on the date of admission and end 30 days from the date of discharge. Since most patients are discharged the same day the common effective date rand is 30 days but can be more.

Patient Visit start date discharge + 29 days Number of visits Bill / Don't Bill
John 1/7/2021 2/5/2021 4 Bill
John 1/13/2021 2/11/2021 4 Don't Bill
John 2/11/2021 3/12/2021 4 Bill
John 2/18/2021 3/19/2021 4 Don't Bill
Jane 4/19/2021 5/18/2021 4 Bill
Jane 9/8/2021 10/7/2021 4 Bill
Jane 9/10/2021 10/9/2021 4 Don't Bill
Jane 9/18/2021 10/17/2021 4 Don't Bill
Joe 1/9/2021 2/7/2021 2 Bill
Joe 1/14/2021 2/12/2021 2 Don't Bill

I was hoping to find a function that can grab the initial date range based on the minimum of the "visit start date" column for each patient. In the image above the initial visit is marked "bill" and the initial date range is set to 1/7/2021-2/5/2021. Since John's 2nd visit has a visit start date that falls within the initial range it id marked don't bill. it does not matter that the discharge date is out of the range as long as the start date is within. John's 3rd visit has a visit start date outside the previous date range so it should be billed and set as the new date range. I hope this makes sense :(

enter image description here

powerquery excel-formula
1 Answers

Using PowerQuery (data ... from table/range .... )

The main trick is to sort on patient, then start date, and then offset the data one row so you can compare to what is in there already to see if it falls into the range of the prior row

Sample code and data, that you could paste into home... advanced editor...

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient", type text}, {"Visit start date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Patient", Order.Ascending}, {"Visit start date", Order.Ascending}}),
//copy down all the columns, offset by one row
MinusOne =  #table({"Column1"}, {{null}}) & Table.Skip(Table.DemoteHeaders(Table.RemoveLastN(#"Sorted Rows",1)),1),
custom1 = Table.ToColumns(#"Sorted Rows") & Table.ToColumns(MinusOne ),
custom2 = Table.FromColumns(custom1,Table.ColumnNames(#"Sorted Rows")&Table.ColumnNames(MinusOne ) ),
//start using them
#"Added Custom1" = Table.AddColumn(custom2, "Custom", each if [Column2]=null then [Visit start date] else if [Patient]=[Column1] and [Visit start date]>=[Column2] and [Visit start date]<=Date.AddDays([Column2],28) then [Column2] else [Visit start date]),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Bill / Dont Bill", each if [Visit start date]=[Custom] then "Bill" else "Don't Bill"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column2", "Custom"})
in #"Removed Columns"

If you need the bill end date, just add column .. custom column .. with formula =Date.Add([Visit Start Date],28)

enter image description here

user_92642300
Related