cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinmcmillz Regular Visitor
Regular Visitor

Expression.Error: The 'increment' argument is out of range.

Hi

 

I've received this error message on a date field and have no idea why.

 

Expression.Error: The 'increment' argument is out of range.
Details:
    1.00:00:00

 

Any ideas?

 

Martin

2 ACCEPTED SOLUTIONS

Accepted Solutions
msespin Frequent Visitor
Frequent Visitor

Re: Expression.Error: The 'increment' argument is out of range.

Hi,

 

I have the same issue, with the same formula.

hopefully, with a little set of data.

The error comes from the data. Some Start Date are higher than the End Date. The calculated duration appears to be negative.

 

MaxS.

 

View solution in original post

Highlighted
csteiner3 Occasional Visitor
Occasional Visitor

Re: Expression.Error: The 'increment' argument is out of range.

I know it has been a while since this was posted.

 

I just encountered the same error and I tracked it down to the count argument of the List.Date function being negative. In reference to your:

 

"

It happens when I add a custom column:

 

= Table.AddColumn(#"Filtered Rows", "FillInDate", each List.Dates([Starting_Date],Duration.Days([Duration])+1,#duration(1,0,0,0)))"

 

Is it possible the 'Duration.Days([Duration]) + 1' results as a negative number at some point?

 

For me, when I removed all rows that produced a negative for this input argument the error went away.

 

Hope this helps.

 

This appears to be the same answer msespin provided earlier.

 

-Caleb

View solution in original post

6 REPLIES 6
Microsoft v-jiascu-msft
Microsoft

Re: Expression.Error: The 'increment' argument is out of range.

Hi Martin,

 

Did you use the function Generateservies() when you got this error message? If so, what's 1.00:00:00? It couldn't be a valid time or date. How about 01:00:00?

 

Please share more details.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
martinmcmillz Regular Visitor
Regular Visitor

Re: Expression.Error: The 'increment' argument is out of range.

Hi Dale

 

Thanks for getting back to me.

 

I'm not using that particular function. 01:00:00 could be format, but when I try to filter out anything that doesn't look like a date I'm not finding anything. There are a lot of rows so perhaps you could suggest a method of weeding out the funky dates.

 

It happens when I add a custom column:

 

= Table.AddColumn(#"Filtered Rows", "FillInDate", each List.Dates([Starting_Date],Duration.Days([Duration])+1,#duration(1,0,0,0)))

 

I've got a long list of jobs with a Start Date and Duration and I'm trying to fill in with the dates between. This will allow me to produce a chart with jobs plotted on an axis with the values of those jobs on the other axis.

 

Here's the sequence:

 

let
    Source = #"Subcontractor_Account_Overview - All",
    #"Merged Queries4" = Table.NestedJoin(Source,{"Job_No"},#"Project List",{"No"},"Project List",JoinKind.LeftOuter),
    #"Expanded Project Summary" = Table.ExpandTableColumn(#"Merged Queries4", "Project List", {"Starting_Date", "Ending_Date", "Duration", "Duration - Months"}, {"Starting_Date", "Ending_Date", "Duration", "Duration - Months"}),
    #"Filtered Rows4" = Table.SelectRows(#"Expanded Project Summary", each ([Duration] <> #duration(0, 0, 0, 0))),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows4", each ([Duration] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Duration - Months", Int64.Type}}),
    #"Filtered Rows5" = Table.SelectRows(#"Changed Type", each ([Starting_Date] <> null)),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "FillInDate", each List.Dates([Starting_Date],Duration.Days([Duration])+1,#duration(1,0,0,0))),
    #"Expanded FillInDate" = Table.ExpandListColumn(#"Added Custom1", "FillInDate"),
    #"Filtered Rows3" = Table.SelectRows(#"Expanded FillInDate", each [FillInDate] <> null and [FillInDate] <> ""),
    #"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows3", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"FillInDate", type date}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type3",{{"FillInDate", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "FillInDate", "FillInDate - Copy"),
    #"Calculated Start of Month" = Table.TransformColumns(#"Duplicated Column",{{"FillInDate - Copy", Date.StartOfMonth, type date}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Calculated Start of Month",{"FillInDate"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"FillInDate - Copy", "FillInDate"}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Renamed Columns3", "FillInDate", "FillInDate - Copy"),
    #"Extracted Month" = Table.TransformColumns(#"Duplicated Column2",{{"FillInDate - Copy", Date.Month, Int64.Type}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month", "FillInDate", "FillInDate - Copy.1"),
    #"Extracted Year" = Table.TransformColumns(#"Duplicated Column1",{{"FillInDate - Copy.1", Date.Year, Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"FillInDate - Copy", "Month"}, {"FillInDate - Copy.1", "Year"}}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each [Year] >= 2016),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows2", {"Year", "Month", "Vendor No.", "Name", "Job_No"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [FillInDate] > #date(2014, 1, 1)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Job_No"},#"Project List",{"No"},"Project List",JoinKind.LeftOuter),
    #"Expanded Project List" = Table.ExpandTableColumn(#"Merged Queries", "Project List", {"Description"}, {"Project List.Description"})
in
    #"Expanded Project List"

 

Hope that's enough information to go on.

 

Thanks for your help.

 

Martin

Microsoft v-jiascu-msft
Microsoft

Re: Expression.Error: The 'increment' argument is out of range.

Hi Martin,

 

This issue is very weird. Even the error message has error itself. 1.00:00:00 is a valid duration type. Can you share a dummy sample and some snapshots?

Expression_Error_The_increment_argument_is_out_of_range

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
msespin Frequent Visitor
Frequent Visitor

Re: Expression.Error: The 'increment' argument is out of range.

Hi,

 

I have the same issue, with the same formula.

hopefully, with a little set of data.

The error comes from the data. Some Start Date are higher than the End Date. The calculated duration appears to be negative.

 

MaxS.

 

View solution in original post

Highlighted
csteiner3 Occasional Visitor
Occasional Visitor

Re: Expression.Error: The 'increment' argument is out of range.

I know it has been a while since this was posted.

 

I just encountered the same error and I tracked it down to the count argument of the List.Date function being negative. In reference to your:

 

"

It happens when I add a custom column:

 

= Table.AddColumn(#"Filtered Rows", "FillInDate", each List.Dates([Starting_Date],Duration.Days([Duration])+1,#duration(1,0,0,0)))"

 

Is it possible the 'Duration.Days([Duration]) + 1' results as a negative number at some point?

 

For me, when I removed all rows that produced a negative for this input argument the error went away.

 

Hope this helps.

 

This appears to be the same answer msespin provided earlier.

 

-Caleb

View solution in original post

Elli27_ New Member
New Member

Re: Expression.Error: The 'increment' argument is out of range.

Hello, 

 

I had the same proble as I wanted to create a new column in Power Query. 

I got this error: Expression.Error: Die Zahl befindet sich außerhalb des gültigen Bereichs eines 64-Bit-Ganzzahlwerts.
Details:
3,70014E+24.

I solved this problem with right click  on the csv file that I wanted to insert column in the main window of Power BI. 

After that  I just used the function that I needed and the column was created without to get an error.

 

I hope to help also others with this solution! 

Ciao, 

Eli

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)