cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Long ID number rounding

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.

 

1 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

 

Снимок.PNG


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Super User II
Super User II

@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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.

Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


That doesn't seem to make a difference.

 

Here is the Report view

 

ReportView.jpg

 
 
 

Here is the table view

 

TableView.jpg

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

 

Снимок.PNG


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors