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

[Salomondiop]

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 : 

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

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 !

[Ahmedx · Accepted solution]

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

[Salomondiop]

Thanks dear ! This is exactly what I was looking for

[Wiam_HTC]

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 

[Salomondiop]

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