Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
This is a bit of a guess. Replace
Ordertable
in the formula with the previous step name like ...... #"whatever"
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.
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()))
Hi @watkinnc , 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.
This is a bit of a guess. Replace
Ordertable
in 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?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.