dataflows is truncating my 19 digit number

[Neptune87]

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 is moved through data flows.

[v-piga-msft]

Hi @Neptune87 ,

By my test in power bi service with the dataflow, I cannot reproduce your issue.

Actually, this is similar to that when we get data from SQL Server in power bi desktop.

Do you have this issue when you load data from SQL Server in Power BI Desktop?

In addition, what data type of the CallID do you set in your Database?

Best  Regards,

Cherry

[Neptune87]

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]

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]

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.