Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lawsonbe
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
az38
Community Champion
Community Champion

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

8 REPLIES 8
v-eachen-msft
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.
az38
Community Champion
Community Champion

@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.

mahoneypat
Employee
Employee

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

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

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
Community Champion
Community Champion

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.