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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
__Johan__
New Member

Power BI automatically round 17 digit number causing erroneous joins

Hello,

 

I'm new to PowerBI so maybe I'm missing simething obviouos but I'm getting a really strange behaviour.

 

I extract data from the source system using dataflow. Each table has a unique 17 digit ID which PowerBI automatically round to the nearest 10 or 100 when displaying in Reports or in Data view. When I join in Model view it works fine and the corresponding report give the correct result. However when I do the same join using power query (tried all the join versions but it should a inner join) I get multiple of the original rows. The only explanation that I can see is that Power Query joins on the rounded values instead of the actual. PowerQuery also display rounded values in the preview but in filters it shows the correct values. Also the preview in the dataflow shows the rounded number.

When I first convert the attribute to a text field then I get the correct result. In other BI tools this is not recommended as it degrades performance over joining on integers as the data volume grow. 

 

Is there a way prevent these roundings?

What is the best practise for working with keys/identifers in power bi?

 

Appreciate any input you might have!

 

Thanks

Fredrik

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Storing a column as text over integer can have an impact but it shouldn't be significant unless you have millions of rows.  I would try out the easy fix as a text column and see the impact on file size and if there is any impact on performance (I expect not).  If not an issue, finish your model and make some reports.

 

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


View solution in original post

1 REPLY 1
mahoneypat
Employee
Employee

Storing a column as text over integer can have an impact but it shouldn't be significant unless you have millions of rows.  I would try out the easy fix as a text column and see the impact on file size and if there is any impact on performance (I expect not).  If not an issue, finish your model and make some reports.

 

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


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors