Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
rtillery2000
Frequent Visitor

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

‎02-09-2018 08:37 AM

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
Labels:
Message 1 of 4
4,247 Views
1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

‎02-12-2018 05:59 PM

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

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 2 of 4
4,359 Views
3 REPLIES 3
v-jiascu-msft
Employee
Employee

‎02-12-2018 05:59 PM

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

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 4
4,360 Views
roenson
New Member
In response to v-jiascu-msft

‎12-03-2018 02:45 AM

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           
Message 4 of 4
3,930 Views
0
rtillery2000
Frequent Visitor
In response to v-jiascu-msft

‎02-13-2018 06:57 AM

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

 

Message 3 of 4
4,212 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All