Forum Discussion

rtillery2000's avatar
rtillery2000
Frequent Visitor
8 years ago
Solved

Separate Rows in to days based on Start and end date/time

I Understand what I am asking may require setting up a new table in Power Bi. Example of current Data below.

My goal is to determine the minutes offline and online for each individual day. As you can see my Start and End data times are logged. Using Row 1 as an example I need a new row with all data copied and create with start time as 2/9/18 12:00 AM and original row Endtime changed to 2/8/18 11:59.59 PM  The Minutes Offline and Online recalculated to reflect accurately.

 

Please note that end date can be multiple days out, I need each row to be individual days.

 

Hoping this make sense and someone can help me.

 

 

 

 

RoomNameRoomCategoryDeviceNameDeviceTypeStartTimeStartValueEndTimeEndValueMinutesOfflineStartHoursStartDateEndDateEndHoursPercentMinutesOnline
O3RoomA760A2/8/18 2:10 PM22/9/18 1:15 PM2138514:10:122/8/20182/9/20181:15:02 PM96.18%55
O3RoomA760A2/7/18 1:36 PM22/8/18 2:10 PM2147413:36:112/7/20182/8/20182:10:12 PM102.36%-34
O3RoomA760A2/6/18 3:06 PM22/7/18 1:36 PM2135015:06:172/6/20182/7/20181:36:11 PM93.75%90
O3RoomA760A2/5/18 2:30 PM22/6/18 3:06 PM2147614:30:222/5/20182/6/20183:06:17 PM102.50%-36
O3RoomA760A2/1/18 7:28 PM22/5/18 2:30 PM2546219:28:472/1/20182/5/20182:30:22 PM379.31%-4022
O3-3ConferenceGS4A1/30/18 3:23 PM21/30/18 4:08 PM24515:23:021/30/20181/30/20184:08:58 PM3.13%1395
O3-3ConferenceGS4A1/30/18 3:12 PM21/30/18 3:23 PM21115:12:411/30/20181/30/20183:23:02 PM0.76%1429
O3-3ConferenceGS4A1/30/18 1:36 PM21/30/18 3:12 PM29613:36:531/30/20181/30/20183:12:41 PM6.67%1344
O3-3ConferenceGS4A1/29/18 9:49 PM21/30/18 1:36 PM294721:49:361/29/20181/30/20181:36:53 PM65.76%493
O3-3ConferenceGS4A1/29/18 9:00 PM21/29/18 9:49 PM24921:00:341/29/20181/29/20189:49:36 PM3.40%1391
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi rtillery2000,

     

    Please check out the demo here. 

    1. Add a custom column like this.

    {Number.From([StartDate])..Number.From([EndDate])}

    2. Expand the custom column.

    3. Change its type to Date. (not datetime).

    4. Add a new column "NewStart".

    if ( [Temp] = [StartDate]) then [StartTime] else [Temp]

    5. Change its type to datetime.

    6. Add a new column "NewEnd".

    if ([Temp] = [EndDate]) then [EndTime] else [Temp] & #time(23,59,59)

    7. You can delete the old two columns. 

    Separate_Rows_in_to_days_based_on_Start_and_end_datetime

     

    Best Regards,

    Dale

     

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi rtillery2000,

     

    Please check out the demo here. 

    1. Add a custom column like this.

    {Number.From([StartDate])..Number.From([EndDate])}

    2. Expand the custom column.

    3. Change its type to Date. (not datetime).

    4. Add a new column "NewStart".

    if ( [Temp] = [StartDate]) then [StartTime] else [Temp]

    5. Change its type to datetime.

    6. Add a new column "NewEnd".

    if ([Temp] = [EndDate]) then [EndTime] else [Temp] & #time(23,59,59)

    7. You can delete the old two columns. 

    Separate_Rows_in_to_days_based_on_Start_and_end_datetime

     

    Best Regards,

    Dale

     

    • rtillery2000's avatar
      rtillery2000
      Frequent Visitor
      8 years ago

      Thank you, that was much easier than the route I was headed down.

       

    • roenson's avatar
      roenson
      New Member
      7 years ago

      Hi

       

      I'am looking for an almost similar solution. 

      Contract Position from Date to Date over an periode (multiple years)

      Split the contract positon amount over all month with creating multiple rows.

       

      any ideas - help would be great!

       

      thx Reto

       

      Solution with an array per year                   
      Contract-Nr.Pos1BetragJahrvon bisKoarAnzahl MAnzahl Jamount/mm1m2m3m4m5m6m7m8m9m10m11m12
      50015000.00011000201701.07.201730.06.201945550024341.67      41.6741.6741.6741.6741.6741.67
      50015000.00011000201801.07.201730.06.201945550024341.6741.6741.6741.6741.6741.6741.6741.6741.6741.6741.6741.6741.67
      50015000.00011000201901.07.201730.06.201945550024341.6741.6741.6741.6741.6741.6741.67      
      50015000.00022500201701.07.201730.06.2019475000243104.17      104.17104.17104.17104.17104.17104.17
      50015000.00022500201801.07.201730.06.2019475000243104.17104.17104.17104.17104.17104.17104.17104.17104.17104.17104.17104.17104.17
      50015000.00022500201901.07.201730.06.2019475000243104.17104.17104.17104.17104.17104.17104.17      
      50015000.00031500201701.07.201730.06.201955000024362.50      62.562.562.562.562.562.5
      50015000.00031500201801.07.201730.06.201955000024362.5062.562.562.562.562.562.562.562.562.562.562.562.5
      50015000.00031500201901.07.201730.06.201955000024362.5062.562.562.562.562.562.5      
                            
                            
      OR BETTER: FOR EACH MONTH ONE ROW         Month           
      50015000.00011000201701.07.201730.06.201945550024341.677.2017           
      50015000.00011000201701.07.201730.06.201945550024341.678.2017           
      50015000.00011000201701.07.201730.06.201945550024341.679.2017           
      50015000.00011000201701.07.201730.06.201945550024341.6710.2017           
      50015000.00011000201701.07.201730.06.201945550024341.6711.2017           
      50015000.00011000201701.07.201730.06.201945550024341.6712.2017           
      50015000.00011000201801.07.201730.06.201945550024341.671.2018           
      50015000.00011000201801.07.201730.06.201945550024341.672.2018           
      50015000.00011000201801.07.201730.06.201945550024341.673.2018           
      50015000.00011000201801.07.201730.06.201945550024341.674.2018           
      50015000.00011000201801.07.201730.06.201945550024341.675.2018           
      50015000.00011000201801.07.201730.06.201945550024341.676.2018           
      50015000.00011000201801.07.201730.06.201945550024341.677.2018           
      50015000.00011000201801.07.201730.06.201945550024341.678.2018           
      50015000.00011000201801.07.201730.06.201945550024341.679.2018           
      50015000.00011000201801.07.201730.06.201945550024341.6710.2018           
      50015000.00011000201801.07.201730.06.201945550024341.6711.2018           
      50015000.00011000201801.07.201730.06.201945550024341.6712.2018           
      50015000.00011000201801.07.201730.06.201945550024341.671.2019           
      50015000.00011000201801.07.201730.06.201945550024341.672.2019           
      50015000.00011000201801.07.201730.06.201945550024341.673.2019           
      50015000.00011000201801.07.201730.06.201945550024341.674.2019           
      50015000.00011000201801.07.201730.06.201945550024341.675.2019           
      50015000.00011000201801.07.201730.06.201945550024341.676.2019