Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GuerauFF
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

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
v-easonf-msft
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.

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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