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: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

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
5,203 Views
1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft Employee
Microsoft 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
5,315 Views
3 REPLIES 3
v-jiascu-msft
Microsoft Employee
Microsoft 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
5,316 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
4,886 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
5,168 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All
Top Solution Authors
View All
Top Kudoed Authors
View All