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

[Anonymous]

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

[msespin · Accepted solution]

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.

[fsfs]

I know it's an old question, but I found this thread while looking for solution myself

The error comes from negative value of length

in my case it was a silly mistake of startdate - enddate instead of the other way around

[Anonymous]

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

[Anonymous · Accepted solution]

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

[v-jiascu-msft]

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

[Anonymous]

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

[v-jiascu-msft]

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?

Best Regards,

Dale

[msespin · Accepted solution]

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.