Forum Discussion

Neptune87's avatar
Neptune87
Frequent Visitor
7 years ago

dataflows is truncating my 19 digit number

I am getting data from on-prem SQL Server through dataflows and the last 2 digits of the 19 digit number are being changed to zero. Below is a screenshot of the data in SQL and Power Query after it i...
Need Help
Neptune87's avatar
Neptune87
Frequent Visitor
7 years ago

Hi v-piga-msft,

Thanks for looking into this.

When I load the same table from Power BI Desktop all 19 numbers come over with no issues.

In SQL the Data Type is set to "bigint"

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi Neptune87 ,

By my tests with bigint type, I could reproduce your issue.

I'm afraid that should be by design in dataflow.

For a workaround, you may change the data type to be nvarchar so that it will be detect as text type when load in dataflow, then you could change it to be whole number type.

Best Regards,

Cherry

 

  • bi-team's avatar
    bi-team
    New Member
    6 years ago

    Explanation of the fundamental need to solve the bigint problem in dataflow:

    For us, this problem occurs when trying to load data from accounting system tables (1C) into a power bi project via dataflow.  Initially, the values of the key fields of all tables of the accounting system (1C) have the form similar to this  "ed1f21b7-6978-4cf0-8129-8ed16-f738103-dfeab1" .  Which allows you to make power bi projects using text key fields in downloadable tables.  But on big data, this makes the project heavier and slower.  

    Provided, that millions of users work with the accounting system (1C), we would like to work with the BIGINT type for long key fields in DATAFLOW without unnecessary transformations and to have the INT64 data type worked equally well in both the power bi desktop project and dataflow.