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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Salomondiop
Frequent Visitor

Splitting time start and end columns into 15 minutes intervals columns daily with power Query

Hello dears, I want to split 2 time columns (start date and end date) into 15 minutes intervals daily. The dataset I have looks like this : 

Salomondiop_0-1695560247272.png

and the output I want looks like something similar to this :

Salomondiop_1-1695560322597.png

An if filter that say 1, when the start date - end date times concern each interval, and 0 if the interval is not concerned by the range. Thanks for your help !

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZPLbcQwDERbCXwmDIqkvq0stpeUlAJSxKajWBKkwBob2ZMBks8zGkqPx+ZYg2y0MblUP25nvwuLfoTCWMnbkxrkuUERRixAJQ7IdUguJm4Z6+6Wv8ai6aIydFqPbBFKheNFZQiFDvl1pLteKkNJj5477CnMQEVsUGqVMrIEdvI9Ze7oqcCiYvFwLJ0Oe48ER0D8L0CzDkEWBlnYVGqnCSSQejvuNSS59lKEAGNxAagZBddpTwGOYJCf93NX9YcuEqa1XpRcmE+7UqWE+0xAySnAuivQknxP9TtDzl+M3EPVhjjKuCykRoKSqg0NlN4xGE5aiRikPGYxoX5n4GG1kbUyX6NW75L31/fr6+fzv7vbyecv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Parking area no" = _t, #"Parking time in minutes" = _t, #"Start date" = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parking area no", Int64.Type}, {"Parking time in minutes", type text}, {"Start date", type datetime}, {"End date", type datetime}}),
   #"Added Custom" = Table.AddColumn(#"Changed Type", "7:00 to 7:15", each Number.From(Time.From([End date])> #time(7,0,0)
 and 
 Time.From([Start date])<= #time(7,15,0))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "7:15 to 7:30", each Number.From(Time.From([End date])> #time(7,15,0)
 and 
 Time.From([Start date])<= #time(7,30,0))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "7:30 to 7:45", each Number.From(Time.From([End date])> #time(7,30,0)
 and 
 Time.From([Start date])<= #time(7,45,0))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "7:45 to 8:00", each Number.From(Time.From([End date])> #time(7,45,0)
 and 
 Time.From([Start date])<= #time(8,0,0))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "8:00 to 8:15", each Number.From(Time.From([End date])> #time(8,0,0)
 and 
 Time.From([Start date])<= #time(8,15,0))),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "8:15 to 8:30", each Number.From(Time.From([End date])> #time(8,15,0)
 and 
 Time.From([Start date])<= #time(8,30,0))),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "8:30 to 8:45", each Number.From(Time.From([End date])> #time(8,30,0)
 and 
 Time.From([Start date])<= #time(8,45,0))),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "8:45 to 9:00", each Number.From(Time.From([End date])> #time(8,45,0)
 and 
 Time.From([Start date])<= #time(9,0,0)))
in
    #"Added Custom7"

 

 

https://1drv.ms/u/s!AiUZ0Ws7G26RinfuwECIMWKQBXqa?e=05xERs

View solution in original post

4 REPLIES 4
Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZPLbcQwDERbCXwmDIqkvq0stpeUlAJSxKajWBKkwBob2ZMBks8zGkqPx+ZYg2y0MblUP25nvwuLfoTCWMnbkxrkuUERRixAJQ7IdUguJm4Z6+6Wv8ai6aIydFqPbBFKheNFZQiFDvl1pLteKkNJj5477CnMQEVsUGqVMrIEdvI9Ze7oqcCiYvFwLJ0Oe48ER0D8L0CzDkEWBlnYVGqnCSSQejvuNSS59lKEAGNxAagZBddpTwGOYJCf93NX9YcuEqa1XpRcmE+7UqWE+0xAySnAuivQknxP9TtDzl+M3EPVhjjKuCykRoKSqg0NlN4xGE5aiRikPGYxoX5n4GG1kbUyX6NW75L31/fr6+fzv7vbyecv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Parking area no" = _t, #"Parking time in minutes" = _t, #"Start date" = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parking area no", Int64.Type}, {"Parking time in minutes", type text}, {"Start date", type datetime}, {"End date", type datetime}}),
   #"Added Custom" = Table.AddColumn(#"Changed Type", "7:00 to 7:15", each Number.From(Time.From([End date])> #time(7,0,0)
 and 
 Time.From([Start date])<= #time(7,15,0))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "7:15 to 7:30", each Number.From(Time.From([End date])> #time(7,15,0)
 and 
 Time.From([Start date])<= #time(7,30,0))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "7:30 to 7:45", each Number.From(Time.From([End date])> #time(7,30,0)
 and 
 Time.From([Start date])<= #time(7,45,0))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "7:45 to 8:00", each Number.From(Time.From([End date])> #time(7,45,0)
 and 
 Time.From([Start date])<= #time(8,0,0))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "8:00 to 8:15", each Number.From(Time.From([End date])> #time(8,0,0)
 and 
 Time.From([Start date])<= #time(8,15,0))),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "8:15 to 8:30", each Number.From(Time.From([End date])> #time(8,15,0)
 and 
 Time.From([Start date])<= #time(8,30,0))),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "8:30 to 8:45", each Number.From(Time.From([End date])> #time(8,30,0)
 and 
 Time.From([Start date])<= #time(8,45,0))),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "8:45 to 9:00", each Number.From(Time.From([End date])> #time(8,45,0)
 and 
 Time.From([Start date])<= #time(9,0,0)))
in
    #"Added Custom7"

 

 

https://1drv.ms/u/s!AiUZ0Ws7G26RinfuwECIMWKQBXqa?e=05xERs

Thanks dear ! This is exactly what I was looking for

Wiam_HTC
Regular Visitor

Hello, 

You can use this for every 15 minutes interval :

Create a new column

Use this formula if [Hstarthour] >= #7:00:00 AM# and [Endhour] <= #7:15:00 AM# then 1 else 0

Repeat this for every interval 

Using the columns in table 1, you mean : if [Start date] >= #7:00:00 AM# and [End date] <= #7:15:00 AM#
then 1
else 0 ? This end up with this error in the query 

Salomondiop_0-1695566053406.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors