Rearranging rows of consecutive dates and times into adjacent columns to calculate dates difference

[fatimah_beesh]

hello i need help with the following in power query 

i need to convert this table: 

Uniqie ID Department In time days Admission month
11 1L 11/9/2023 7:52:00 AM 0 Nov-23
11 2L 11/9/2023 5:21:00 PM 3 Nov-23
11 2L 11/12/2023 11:00:00 AM Null Nov-23
133 EMS 4/10/2023 3:32:00 PM 0 Apr-23
133 5L 4/10/2023 4:17:00 PM 3 Apr-23
133 OR 4/13/2023 11:36:00 AM 0 Apr-23
133 5L 4/13/2023 2:15:00 PM 18 Apr-23
133 5C 5/1/2023 3:05:00 PM 3 Apr-23
133 5L 5/4/2023 4:29:00 PM 27 Apr-23
133 5A 5/31/2023 2:20:00 PM 83 Apr-23
133 5A 8/22/2023 11:30:00 AM Null Apr-23

into this format and be able to have the days difference claculation as in the comment column

so for cases admitted and discharged in the same month as case with ID 11, there is no issue

however, with case ID 133, there is an issue since the case was last admitted into 5A on 05/31/2023 and left that unit after several months which is 8/22/2023. how can i add calculations to calculate the number of days spend each month, so i need days in May, June, July and August separately. 

Uniqie ID Department In time Out time days Admission month
11 1L 11/9/2023 7:52:00 AM 11/9/2023 5:21:00 PM 0 Nov-23
11 2L 11/9/2023 5:21:00 PM 11/12/2023 11:00:00 AM 3 Nov-23
11 2L 11/12/2023 11:00:00 AM Null Nov-23 

This above scenario have no issue since the admission and discharge are on the same month
133 EMS 4/10/2023 3:32:00 PM 4/10/2023 4:17:00 PM 0 Apr-23
133 5L 4/10/2023 4:17:00 PM 4/13/2023 11:36:00 AM 3 Apr-23
133 OR 4/13/2023 11:36:00 AM 4/13/2023 2:15:00 PM 0 Apr-23
133 5L 4/13/2023 2:15:00 PM 4/13/2023 2:15:00 PM 18 Apr-23
133 5C 4/13/2023 2:15:00 PM 5/4/2023 4:29:00 PM 3 Apr-23
133 5L 5/4/2023 4:29:00 PM 5/31/2023 2:20:00 PM 27 Apr-23
133 5A 5/31/2023 2:20:00 PM 8/22/2023 11:30:00 AM 83 Apr-23
133 5A 8/22/2023 11:30:00 AM Null Apr-23

in this scenario how do I count the days spent in June, July then August seperately

__PRESENT

__PRESENT

__PRESENT

[fatimah_beesh]

?

[Anonymous]

Hi @fatimah_beesh ,

You can follow the steps below to get it in Power Query Editor:

1. Add custom column to get the value of next row

Referencing the Next Row in Power Query • My Online Training Hub

2. Generate the rows if the date columns are from different months

Generating Rows by Month for Date Ranges in Power Query 

  (12 * (Date.Year([FY End]) - Date.Year([FY Start])))
+ (Date.Month([FY End]) - Date.Month([FY Start]))
+ (if Date.Day([FY End]) < Date.Day([FY Start]) 
    then -1 
    else 0
  )
+ 1

Best Regards

[fatimah_beesh]

hi, 

thanks for your support, the solution helped me partially and put me on the road start. 

however, part 2 of the solution, i could not apply? 

any suggestions??

[Anonymous]

Hi @fatimah_beesh ,

What do you mean for the following sentence? 

however, part 2 of the solution, i could not apply? 

---

Best Regards

[fatimah_beesh]

there was a proposed solution as seen above, however, i was not able to apply it nor get the understanding of it. 

what i need help with is to count the number of days for each month withinh the admission and discharge dates? for example 

if the in time and out time are the same month, then its just a simple subtraction. however, if the in and out dates are spanning over a 3 month period, i want to know the dates spent in each period/ month for each unique record. its like adding rows to the master data that generates details ? 

hope this makes it more clear?

thanks