Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Power 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

 

Status: Investigating

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

Comments
v-xiaoyan-msft
Community Support
Status changed to: Investigating

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

Rudz
Kudo Collector

Those 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)

Rudz_1-1675261715560.png

and pasting this in the formula bar:

= List.Dates(#date(2023,1,1), -10, #duration(1,0,0,0))

Rudz_0-1675261682896.png

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:
-10

 

The 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.

Rudz_3-1675262907125.png
Rudz_2-1675262884667.png

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))