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

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
v-jiascu-msft Super Contributor
Super Contributor

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

v-jiascu-msft Super Contributor
Super Contributor

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

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 341 members 3,568 guests
Please welcome our newest community members: