cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Split rows depending on time span

Hi there,

 

I am working with a report in which I would like to analyze data on a weekly basis. The issue that I'm having is that there are some rows that go from let's say sunday 17:00 to monday 3:00. What I would like to do is to split this row into two different rows, the first one should cover from 17:00 - 23:59 and the second one from 00:00 to 3:00. Below is an example of what I mean. 

 

Initial Data:

Forum PowerBI dies.JPG

Data how I would want it:

Forum PowerBI dies.2.JPG

 

Notice how the information for the rest of the rows should remain the same.

 

If anyone knows how I could do it I would really appreciate it!

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi , @GuerauFF 

You only need to add additional  judgment condition to determine whether the weeknum of the two dates is the same, and then determine the number of expanded rows. If the date weeknums are different, the number of expanded rows is 1, otherwise the number of expanded rows is 0

77.png

 

The result will show as below:

 

78.png

 

Pbix attached

 

Best Regards,
Community Support Team _ Eason
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

7 REPLIES 7
Super User IV
Super User IV

@GuerauFF , please check if logic in this file can help you

https://www.dropbox.com/s/5wo5jhzl488zy69/Minute_between%20Hours.pbix?dl=0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Not sure why but I can't download the file... 

Community Support
Community Support

Hi, @GuerauFF 

Create a blank query  and  try this M query Code in advanced editor .

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDLDoIwEEV/pWFNIp0WeeyMbhUjuiIsGmhMEzM0UhL9eysRQaxx1enpZE7nFoV36BAVnonnT8uNbKur0kY1SKi9U74I2AICCMgyheRJwjehccq4RdnYn4t7fx7lzdii9Atv1xiSVVWnlazty1YoNBIFVnLmA/f0KUrSEEYhvIRsEEIvPKG+CERZk9w0WouzbG3DarNaz4TMPZ1GU8To14bR4OO973+S3D11ggA+omTzKFlv2jv2+p1n6FbQePxIlLJktPJ5nqFXlg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Group = _t, Description = _t, StartDate = _t, FinishDate = _t, #"Parameter 1" = _t, #"Parameter 2" = _t, #"Parameter 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Group", type text}, {"Description", type text}, {"StartDate", type text}, {"FinishDate", type text}, {"Parameter 1", type text}, {"Parameter 2", type text}, {"Parameter 3", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"StartDate", type datetime}, {"FinishDate", type datetime}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "datediff", each Duration.TotalDays(Date.StartOfDay([FinishDate])-Date.StartOfDay([StartDate]))),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom", each if [datediff] <= 1 then 0 else [datediff]-1),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each List.Generate(()=>[Custom], each _ >= 0, each _ - 1)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Start", each if [Custom.1]=[Custom] then [StartDate] else  Date.AddDays(Date.StartOfDay([StartDate]),[Custom]-[Custom.1])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "End", each if [Custom.1]=0 then [FinishDate] else  #datetime(Date.Year([Start]),Date.Month([Start]),Date.Day([Start]),23,59,0)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"StartDate", "FinishDate", "datediff", "Custom", "Custom.1"})
in
    #"Removed Columns"

The result will show as below:

23.png

 

Here is a sample.

pbix attached

 

You also can refer to some related post.

https://community.powerbi.com/t5/Power-Query/Splitting-event-duration-rows-spanning-multiple-days/m-...

https://community.powerbi.com/t5/Desktop/Split-Rows-based-on-Condition/td-p/577645/page/2

 

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

Hi, @v-easonf-msft

 

I think I maybe didn't explain myself properly. I was following along the steps that you established with the advanced editor and I see how it's working but there are a couple of things that should work differently for my case. 

 

I've noticed that with your solution what you do is split those rows that cover two days independently of the day. While that's very useful it is not exactly what I would need.

 

My report is aimed to be a summary of how the past week went (week going from Monday 00:00 to Sunday 23:59) and it will be checked every Monday. In order to do so I would like to split all the rows that start on Sunday and finish on Monday, this way, when I summaryze the results I'll be looking at the results of the last natural week (7 days * 24 hours/day = 168 hours). 

 

Is it more clear now?

 

Thanks!

Hi , @GuerauFF 

Does it mean that you just want to split the rows that  StartDate is Sunday and EndDate is Monday ?

 

Best Regards,
Community Support Team _ Eason

Exactly!

Hi , @GuerauFF 

You only need to add additional  judgment condition to determine whether the weeknum of the two dates is the same, and then determine the number of expanded rows. If the date weeknums are different, the number of expanded rows is 1, otherwise the number of expanded rows is 0

77.png

 

The result will show as below:

 

78.png

 

Pbix attached

 

Best Regards,
Community Support Team _ Eason
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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors