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.
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:
Data how I would want it:
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!
Solved! Go to 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
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 _ 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
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.
@GuerauFF , please check if logic in this file can help you
https://www.dropbox.com/s/5wo5jhzl488zy69/Minute_between%20Hours.pbix?dl=0
Not sure why but I can't download the file...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |