Forum Discussion
Split rows depending on time span
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
The result will show as below:
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, 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:
Here is a sample.
You also can refer to some related post.
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 _ EasonExactly!
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
The result will show as below:
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.