none
Expression.Error: The number is out of range of a 32 bit integer value.(Script Attached) RRS feed

  • Question

  • Hello Community,

    I am getting an error "Expression.Error: The number is out of range of a 32 bit integer value.".Please suggest me some workaround.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVdSxN1zW0UNJRMjQGs40MgWxjMwXf/LySjGKlWJ1okIxvYhFUlYWuf3KJrqEliG0JVxULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, #"Contract Length" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Contract Length", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Contract Length", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Contract Length.1", "Contract Length.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contract Length.1", Int64.Type}, {"Contract Length.2", type text}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each Number.Round (
    Value.Divide([Contract Length.1],12),2)),
        #"Inserted Integer-Division" = Table.AddColumn(#"Added Custom1", "Integer-Division", each Number.IntegerDivide([Contract Length.1], 12), Int64.Type),
        #"Added Custom2" = Table.AddColumn(#"Inserted Integer-Division", "Custom.1", each let a= [Custom],b = [#"Integer-Division"] in 
    if a<1 or a>b then List.Generate(()=>1,each _<= a+1,each _ +1) else List.Generate(()=>1,each _<= a,each _ +1)),
        #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom2", "Custom.1"),
        #"Added Custom3" = Table.AddColumn(#"Expanded Custom.1", "Custom.3", each Number.Round (
    if [Custom.1]<>1 and [Custom]>1 and [Custom]<>[#"Integer-Division"] then [Custom]-[#"Integer-Division"] else [Custom.1],2)),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each true),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom.2", each if [Custom.1]=1 then [Start Date] else Date.AddDays(Date.AddYears([Start Date],[Custom.1]-1),-1)),
        #"Added Custom4" = Table.AddColumn(#"Added Custom", "Custom.4", each if [Contract Length.1] <=12 
    then Date.AddDays(Date.AddMonths([Start Date],[Contract Length.1]),-1) 
    else 
        if [Custom.1] = [Custom.3] 
        then Date.AddDays(Date.AddYears([Start Date],[Custom.3]),-2) 
        else Date.AddMonths([Custom.2],([Custom.3])*12)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Start Date", "End Date", "Custom", "Integer-Division", "Custom.1", "Custom.3"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Start date"}, {"Custom.4", "End date"}}),
        #"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns", "Contract Length", each Text.Combine({Text.From([Contract Length.1], "en-US"), [Contract Length.2]}, " "), type text),
        #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Contract Length.1", "Contract Length.2"})
    in
        #"Removed Columns1"

    Sunday, December 22, 2019 1:58 AM

Answers

  • Hi,

    this seems like a rounding issue (as far as I know, RoundingMode.AwayFromZero does the rounding the same way as excel does)

    If that is not what you want you can experiment with the following options:

    • RoundingMode.Down (or 1)
    • RoundingMode.AwayFromZero (or 2)
    • RoundingMode.TowardsZero (or 3)
    • RoundingMode.ToEven (or 4) [default]

    One note though, in your sample "End Date" is smaller than "Start Date" so I am not very sure how you end up with "19 Months". Probably a typo?


    George Kosmidis

    Monday, December 23, 2019 8:56 AM

All replies

  • Hi ABC1919,

    which value do you want to see instead of the current error?


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, December 22, 2019 7:38 AM
    Moderator
  • Hi Imke,

    Thank you so much for attending my question.

    My whole script works fine for contract months 36 months,24 months or 12 months etc.

    For 19 months I had an error,in a last line.

    I think if this error will fix then it will produce the correct result.

    Let me know if you need more information.

    (Once my account information will verify,i will post the screenshot)

    Sunday, December 22, 2019 2:18 PM
  • Hi Imke,

    I found the issue,

    “else Date.AddMonths([Custom.2],([Custom.3])*12)”

    Custome.3=0.58*12 and Custom.2 is date 03/12/2019.I am not sure how to tackle that issue.

    Custom.4= if [Contract Length.1] <=12 
    then Date.AddDays(Date.AddMonths([Start Date],[Contract Length.1]),-1) 
    else 
        if [Custom.1] = [Custom.3] 
        then Date.AddDays(Date.AddYears([Start Date],[Custom.3]),-2) 
        else Date.AddMonths([Custom.2],([Custom.3])*12)
    

    Sunday, December 22, 2019 3:40 PM
  • As a general advice, I would suggest to name your variables better than Custom1 and Custom2 because it is difficult to follow what is happening!

    Anyway, I am not very familiar with PowerQuery but according to the documentation the second parameter of AddMonths should be a number (https://docs.microsoft.com/en-us/powerquery-m/date-addmonths). Although it doesn't specify anything about integers, IF you found the error on that line it could be that you are not providing an integer as a second parameter.

    Try Int32.From found on https://docs.microsoft.com/en-us/powerquery-m/int32-from :

    Date.AddMonths([Custom.2], Int32.From([Custom.3]*12, null, RoundingMode.AwayFromZero))


    George Kosmidis

    Sunday, December 22, 2019 4:00 PM
  • Hi George,

    Thank you so much for your guidance,Your suggestion partially worked for me.

    Please see below my data set,

    Start Date        End Date        Contract Length

    13-Mar-2018    18-Oct-2019    19 Months

    Final Result Should BE

    Start Date        End Date        Contract Length
    13-Mar-2018    12-Mar-2018    19 Months
    13-Mar-2019    18-Oct-2019     19 Months

    After your using suggestion error went away but final result is not showing right.

    Current Result

    Start Date        End Date        Contract Length
    13-Mar-2018    11-Mar-2018    19 Months
    12-Mar-2019    12-Oct-2019     19 Months

    Do you have any suggestion?

    Sunday, December 22, 2019 4:44 PM
  • Hi,

    this seems like a rounding issue (as far as I know, RoundingMode.AwayFromZero does the rounding the same way as excel does)

    If that is not what you want you can experiment with the following options:

    • RoundingMode.Down (or 1)
    • RoundingMode.AwayFromZero (or 2)
    • RoundingMode.TowardsZero (or 3)
    • RoundingMode.ToEven (or 4) [default]

    One note though, in your sample "End Date" is smaller than "Start Date" so I am not very sure how you end up with "19 Months". Probably a typo?


    George Kosmidis

    Monday, December 23, 2019 8:56 AM