Power BI automatically round 17 digit number causing erroneous joins

[__Johan__]

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

[mahoneypat · Accepted solution]

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