Incorrect Time Bands Pulling Through - Power Query [M]

[Anonymous]

Hi,

Following on from the syntax error I was getting with the below which has now been corrected by the Community, I now have an issue where all the records have been banded at 00:00 - 01:00.

Can someone please check if I've missed something in my formula below?

=if [#"ReceievedDate - Time"] >= #time(0,0,0) or 
[#"ReceievedDate - Time"] <= #time(0,59,59) 
then "00:00 - 01:00"

else 
if [#"ReceievedDate - Time"] >= #time(1,0,0)  or 
[#"ReceievedDate - Time"] <= #time(1,59,59)  
then "01:00 - 02:00"

else 
if [#"ReceievedDate - Time"] >= #time  (2,0,0)  or 
[#"ReceievedDate - Time"] <= #time (2,59,59)
then "02:00 - 03:00"

else 
if [#"ReceievedDate - Time"] >= #time (3,0,0) or 
[#"ReceievedDate - Time"] <= #time  (3,59,59) 
then "03:00 - 04:00"

else 
if [#"ReceievedDate - Time"] >= #time  (4,0,0)  or 
[#"ReceievedDate - Time"] <= #time (4,59,59) 
then "04:00 - 05:00"

else 
if [#"ReceievedDate - Time"] >= #time  (5,0,0)  or 
[#"ReceievedDate - Time"] <= #time  (5,59,59)  
then "05:00 - 06:00"

else 
if [#"ReceievedDate - Time"] >= #time  (6,0,0) or 
[#"ReceievedDate - Time"] <= #time  (6,59,59) 
then "06:00 - 07:00"

else 
if [#"ReceievedDate - Time"] >= #time  (7,0,0)  or 
[#"ReceievedDate - Time"] <= #time  (7,59,59) 
then "07:00 - 08:00"

else 
if [#"ReceievedDate - Time"] >= #time  (8,0,0)  or 
[#"ReceievedDate - Time"] <= #time  (8,59,59) 
then "08:00 - 09:00"

else 
if [#"ReceievedDate - Time"] >= #time  (9,0,0)  or 
[#"ReceievedDate - Time"] <= #time  (9,59,59) 
then "09:00 - 10:00"

else 
if [#"ReceievedDate - Time"] >= #time (10,0,0) or 
[#"ReceievedDate - Time"] <= #time (10,59,59)  
then "10:00 - 11:00"

else 
if [#"ReceievedDate - Time"] >= #time (11,0,0)  or 
[#"ReceievedDate - Time"] <= #time (11,59,59)  
then "11:00 - 12:00"

else 
if [#"ReceievedDate - Time"] >= #time (12,0,0)   or 
[#"ReceievedDate - Time"] <= #time (12,59,59)  
then "12:00 - 13:00"

else 
if [#"ReceievedDate - Time"] >= #time (13,0,0)   or 
[#"ReceievedDate - Time"] <= #time (13,59,59)  
then "13:00 - 14:00"

else 
if [#"ReceievedDate - Time"] >= #time (14,0,0)   or 
[#"ReceievedDate - Time"] <= #time (14,59,59)  
then "14:00 - 15:00"

else 
if [#"ReceievedDate - Time"] >= #time (15,0,0)   or 
[#"ReceievedDate - Time"] <= #time (15,59,59)  
then "15:00 - 16:00"

else 
if [#"ReceievedDate - Time"] >= #time (16,0,0)   or 
[#"ReceievedDate - Time"] <= #time (16,59,59)  
then "16:00 - 17:00"

else 
if [#"ReceievedDate - Time"] >= #time (17,0,0)   or 
[#"ReceievedDate - Time"] <= #time (17,59,59)  
then "17:00 - 18:00"

else 
if [#"ReceievedDate - Time"] >= #time (18,0,0)   or 
[#"ReceievedDate - Time"] <= #time (18,59,59)  
then "18:00 - 19:00"

else 
if [#"ReceievedDate - Time"] >= #time (19,0,0)   or 
[#"ReceievedDate - Time"] <= #time (19,59,59)  
then "19:00 - 20:00"

else 
if [#"ReceievedDate - Time"] >= #time (20,0,0)   or 
[#"ReceievedDate - Time"] <= #time (22,59,59)   
then "20:00 - 21:00"

else 
if [#"ReceievedDate - Time"] >= #time (21,0,0)   or 
[#"ReceievedDate - Time"] <= #time (21,59,59)   
then "21:00 - 22:00"

else 
if [#"ReceievedDate - Time"] >= #time (22,0,0)   or 
[#"ReceievedDate - Time"] <= #time (22,59,59)  
then "22:00 - 23:00"

else "23:00 - 00:00"

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

If you want to find a time range, please change OR function to AND function.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-zhenbw-msft 

Thank you this works!

[amitchandak]

@Anonymous , receive date-time has only time?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi @amitchandak ,

Yes ReceievedDate-Time only has time and is a columns duplicated from the ReceievedDate column which wach originally a Date/Time column. Apologies for the column spellings that whow they're spelt in our system.

I'm not entirely sure how to share the data on here or at least to add a screen print as I've tried and it doesn't work, so below is a table of how this looks like:

ReceievedDate	ReceievedDate-Time
03 April 2019	19:41:03
29 August 2019	11:38:02

Many thanks

[HotChilli]

We can't see your data but you need to concentrate on the line that you think is firing.

And it looks like this one

if [#"ReceievedDate - Time"] >= #time(0,0,0) or 
[#"ReceievedDate - Time"] <= #time(0,59,59) 
then "00:00 - 01:00"

is firing for every line.

[Anonymous]

Hi @HotChilli 

Thanks for getting back to me.

Yes, it's the result from this line it's bringing back, so just looking into why it's doing this as there are no syntax errors.

I'm completely stumped

[HotChilli]

Well, you need to look at the logic.

There are 2 logical clauses in the part that returns 

 "00:00 - 01:00"

so every data value that gets compared in the part

if [#"ReceievedDate - Time"] >= #time(0,0,0) or 
[#"ReceievedDate - Time"] <= #time(0,59,59) 
then "00:00 - 01:00"

returns true.  To debug this, substitute your data values (in your head) into each clause, and ask yourself "is that true?".  If the answer is "yes", the code doesn't go any further, it just returns the string "00:00 - 01:00"