Kudo CollectorPower Query error message is wrong on List.Dates for negative count
I'm hoping I'm submitting a bug report the right way. I don't see an option for Power Query.
We had an error in a query that passed the wrong value to List.Dates. Part of what made the problem difficult to debug is that the error message is wrong. Here's the documentation on List.Dates:
Syntax
List.Dates(start as date, count as number, step as duration) as list
About
Returns a list of date values of size count, starting at start. The given increment, step, is a duration value that is added to every value.
We passed a negative number to count and this is the error message:
= List.Dates(#date(2023,1,1), -10, #duration(1,0,0,0))
Expression.Error: The 'increment' argument is out of range.
Details:
1.00:00:00
It was not the 'increment' or step (1.00:00:00) that was the problem. It was the count being -10. The error message should say something like:
Expression.Error: The 'count' argument is out of range.
Details:
-10
2 Comments
Hi Rudz ,
This seems to be a problem caused by incorrect code, you can refer to the following threads to see if it helps.
Solved: The mess "Expression.Error: The 'Count' argument i... - Microsoft Power BI Community
excel - PowerQuery M Conditional Column 'count' argument is out of range - Stack Overflow
Solved: Re: The mess "Expression.Error: The 'Count' argume... - Microsoft Power BI Community
Here is the Power query forum and if you need further help you may try this forum.
Power Query - Microsoft Power BI Community
Best regards.
Community Support Team_ Caitlyn
RudzThose links don't apply. The first and third are the same issue and are DAX, not Power Query. The second is Power Query, but a different function with user error and a correct error message.
This case is a bug where Power Query gives an incorrect error message. You can replicate this error by creating a blank query (Get data > Blank query)
and pasting this in the formula bar:
= List.Dates(#date(2023,1,1), -10, #duration(1,0,0,0))
The -10 is the error. It must be a positive number.
Then change -10 to 10 and it works. The -10 or 10 is the count parameter, not the Increment or step indicated in the error message, which is the #duration(1,0,0,0) or 1:00:00:00.
Syntax
List.Dates(start as date, count as number, step as duration) as list
About
Returns a list of date values of size count, starting at start. The given increment, step, is a duration value that is added to every value.
So this is a bug in Power Query in the List.Dates function. The error message is referring to the wrong parameter and should be adjusted to call out the count parameter instead of increment when count is negative:
Expression.Error: The 'count' argument is out of range.
Details:
-10The documentation for List.Dates should also be updated to indicate that count must be a positive number.
The documentation is also lacking info about the boundaries of the output. The list of dates that are output from the List.Dates function must be between January 1, 0001 and December 31, 9999.
These fail because the dates are out of this acceptable range:
= List.Dates(#date(1,1,1), 2, #duration(-1,0,0,0))
= List.Dates(#date(9999,12,31), 2, #duration(1,0,0,0))
And these work:
= List.Dates(#date(1,1,1), 1, #duration(-1,0,0,0))
= List.Dates(#date(9999,12,31), 1, #duration(1,0,0,0))
