Forum Discussion
Cannot spot Syntax failure/list min query
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.
This is a bit of a guess. Replace
Ordertablein the formula with the previous step name like ...... #"whatever"
10 Replies
what's the error?
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.
Click in a cell with the error and it will display under the table.
This is a bit of a guess. Replace
Ordertablein the formula with the previous step name like ...... #"whatever"
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?
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.
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.
Got it. Thanks for the explanation HotChilli
