Long ID number rounding

[lawsonbe]

I have an interesting issue popping up in my newest data source. The tables I am bringing into the data set contain long id numbers. The numbers type in Power Querry is int64. In PowerBI they are Whole Numbers. When you look at the table view in PowerBI you can see the actual numbeer as it appears in the original table. When you use that same id number in a table visual, it gets rounded as if it came through scientific notation and got simplified. I couldn't find this specific issue in any of the messages. If I missed it, please point me in the right direction. An example of a number used is "148800000000000106". There are 18 digits in every number so it is not going longer that the int64 limit.

[v-eachen-msft]

Hi @lawsonbe ,

It is a similar problem in Excel. 

If you only want to show it in report, you could transform the data type as text. Or you could refer to the following DAX:

FORMAT( 'Table'[ID], "000000000000000000" )

If you want to calculate the result based on these values, you could change them as scientific numbers.

[az38]

@lawsonbe 

AS it is ID, I think you aren't going to aggreagate it, and it will not be a problem if you set this column as Text

[lawsonbe]

These long ids are in every table and sometimes multiple times. The data type detection worked perfectly on import and I really don't want to have to change all the data types to text to make multiple table relationships work. I need PowerBI to not round the number. It seems like it should not do that if the type is int64.

[mahoneypat]

In the main report view, click on the column in the fields panel and the Column Tools tab.  Make sure the format shows as Whole Number and not Scientific (and the that column isn't being aggregated).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lawsonbe]

That doesn't seem to make a difference.

Here is the Report view

Here is the table view

[adrianhen]

lawsonbe
Can you confirm that is issue have been resolved as I am also struggling with the same issue 😞

PREVIEW

[lawsonbe]

I am not working in that dataset any longer and can't confirm that it has been corrected. If you are still seeing the same behavior, it is safe to say it is not. Changing the field to Text is your only option.

[az38 · Accepted solution]

Hi @lawsonbe 

I confirm the problem and created an issue here https://community.powerbi.com/t5/Issues/Unexpected-long-numbers-round-in-Table-visual/idi-p/1082517#.... you can vote for

But solution in your case is to change date type to string in Power QUery mode, it helped me