The number is out of range of a 32 bit integer value

[smpa01]

Hi,

I am getting an error when I am trying to run the following code. I am trying the code to generate what {1..5} would generate.

= {4615443880..4615443890}

Error

I know from https://community.powerbi.com/t5/Desktop/Only-64-Bit-Data-Type-in-Data-Models/td-p/133635

that following are the range for different Int Category.

Int8: -256 - +255

Int16: -32,768 - +32,767

Int32: -2,147,483,648 – +2,147,483,647

Int64: -9,223,372,036,854,775,808 - +9,223,372,036,854,775,807

As per the above 4615443880 is indeed out of 32 bit range.

However, I also tried the following but the error remained same.

= {Int64.From(4615443880)..Int64.From(4615443890)}

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEzNDUxMbawMFDSgXMsDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[Column1]..[Column2]})
in
    #"Added Custom"

Does anyone know what is the work around for this? Does Power Query list only generate values between 32 bit integers?

Appreciate any help. Thank you in advance.

[v-danhe-msft]

Hi @smpa01 ,

Based on my test, you could use GENERATESERIES function:

https://docs.microsoft.com/en-us/dax/generateseries-function

Table = GENERATESERIES(4615443880,4615443890)

Result:

Regards,

Daniel He

[smpa01]

@v-danhe-msftthank you very much for your reply.

Can the same be replicated using M and not DAX? I am looking for a M solution to the problem.

[v-danhe-msft · Accepted solution]

Hi @smpa01 ,

Based on my test, you could use List.Numbers function:https://docs.microsoft.com/en-us/powerquery-m/list-numbers

Result:

Regards,

Daniel He

[Anonymous]

Source = Salesforce.Reports(),
    #"00O2p000008qsHmEAI" = Source{[Name="00O2p000008qsHmEAI"]}[Data],
    
      #"Added Custom" = Table.AddColumn(#"00O2p000008qsHmEAI", "Custom", each {List.Numbers(1,10)})

in
     #"Added Custom"

Expression error : The number is out of range of 32 bit integer value Details: 24.69063222

[smpa01]

Hello @v-danhe-msft 

This is amazing. It has worked. Thank you very much. Awesome way to tackle this 32 bit integer limitation.