Cannot spot Syntax failure/list min query

[Applicable88]

Hello,

I made a calculated column for this table:

11.05.2021	Tuesday	1	1234
12.05.2021	Wednesday	1	1235
13.05.2021	Thursday	1	1236
14.05.2021	Friday	1	1237
15.05.2021	Saturaday	0	1238
16.05.2021	Sunday	0	1239
17.05.2021	Monday	0	null
18.05.2021	Tuesday	0	null
19.05.2021	Wednesday	1	1242
20.05.2021	Thursday	1	1243
21.05.2021	Friday	1	1244
22.05.2021	Saturaday	0	1245
23.05.2021	Sunday	0	1246
24.05.2021	Monday	1	1247
25.05.2021	Tuesday	1	1248
26.05.2021	Wednesday	1	1249

Whatever day today is I want a calculated column to tell me the next productive workday =1. But only the next one, not further into the future:

Someone helped me a lot and provided me this query, but it might still has a syntax failure in it?

NextDay = List.Min(Table.SelectRows(Ordertable, each [ProductiveWorkday] = 1 and [OutBoundDate] > Date.From(DateTime.LocalNow()))[OutBoundDate])

I only get a error message.

Thank you very much in advance.

Best. 

[HotChilli]

A cyclic reference is when A refers to B which refers to A and so on   or  even A refers to A (refers to A ...)

In Power Query a table is a sequence of query steps so in this case the last query step referred to the table name (which included the last query step) so it was cyclic.

The general advice is that each query step should act on the previous step.  That's not always true but it's a reasonable starting point.

[Applicable88]

Got it. Thanks for the explanation @HotChilli 

[Anonymous]

You may wish to wrap each if clause in parentheses so there is not chance of PQ getting confused, like this:

([ProductiveWorkday] = 1) and ([OutboundDate] > Date.From(DateTime.LocalNow()))

[Applicable88]

Hi @Anonymous , thanks again for your effort. I also tried to leave the ProductiveWorkday out completely. Didn't help. But I think the tip of @HotChilli is going into the right direction. 

[HotChilli · Accepted solution]

This is a bit of a guess.  Replace 

Ordertable

in the formula with the previous step name  like ......      #"whatever"

[Applicable88]

Hey @HotChilli, thanks so much... this was it!

It was a perfect "guess":)

I really want to learn from this mistake, so can you explain

 why it doesn't work with the orginal table name but with the #"StepName" ?

I also want to mention that the ProductiveWorkdays column is originally from a separated mastercalendar

that I joined with the maintable so I can use this as a condition for my statement. I don't know if that plays a role? 

[HotChilli]

what's the error?

[Applicable88]

no syntax error announced when I input it. but the calculated column state the typical error on every row. so no results. alll the column stated in the function do exist. I clicked on them in the right panel instead of writing myself. 

[HotChilli]

Click in a cell with the error and it will display under the table.

[Applicable88]

oh, thank you.

there it is, i translate in english:

Failure in the query 'Ordertable'.Expression.Error: While making the evaluation a cyclical reference where found.