Whole Number ID Problem (19 Digits)

[Gazzer]

My data source is using 19 digit integers for unique IDs. According to specification, this is fine with Power BI (19 digits being the maximum width).

When I merge one query with another using this ID for the join, I get multiple rows of data where there should be just one. If I add a filter to the first query so that it contains just one client, I see the following:

• I start with 68 rows of data for the single client, all with the same ProjectUID.
• I group by ProjectUID and a couple of other common fields and aggregate some values, which gives me one row
• Then I merge with a second query and when I expand the new fields, I now have four rows - one is the expected client, the other three are totally unrelated clients
• If I then change the ProjectUID type to text, I get the single row that I was expecting.

I am trying to avoid setting these numeric IDs to text as I believe this adversely affects processing speed and I am already reaching a point where Power BI is to slow to be useable.

I have found that if I take the last 15 digits (which in this case are still unique), then everything works as expected. This suggests that the stated 19 digit width of integer type is incorrect (or that I am misunderstanding something). The number of excess rows reduces by one for each digit I remove from the beginning of the ID. I am uncertain whether the 15 digit width is working because it works or if it is just because I only had three excess rows in that filter to begin with.

Anyone else had this issue?

Just a thought - does the 19 digit width mean 18 digits plus the +/- sign?

[MrLidums]

Hi there,

it is May 2024 and I'm face same problem here. There is id field in database, containing 19 digits ids with the "-" sign in front of them. (exampl [-1234567891234567891].

When query is loaded in Power Qeury I see all numbers as it should be. When data is loaded into the report, in Table view I see number as it should be. But:

• when I try to join tables on this field, I getting warning that it will be many-to-many join. This should not be, as one table has only unique id, and the other one is having list of events related to particular id
• when I put this field in the table visual, I see Power Bi converts last 3 digits to zeros

[vlknyzc]

having the same issue with the 17 digits ID column in number format.

PBI rounds up the numbers and my model does not work properly. 

I want to avoid having ID's in text format for the performance purposes but it seems there isn't a workaround, yet.

[Ylreeb]

Dear PowerBI-Team,

can you please fix this issue...it bugs.

Now stumbling when working with Python DataFrames.

Please fix it!!

[Ylreeb]

I have the same issue.

Importing data from an Oracle DB with rows of ID's with 19 digits and no signs (as a primary key).

Figured out several issues when  formating it to a "Whole Nr." (Int.Type 64).

• Column Sorting in retrieving -> receiving even more, or less items
• Relationsships in data model -> cardinality
• Merging tables

After formating to "Type TExt" it works.

Whats wrong? In my understanding computing digits (instead) of text is even more "easy" for a computer...

[Anonymous]

I have the same issue. When reading in a data table from CSV, the unique 17-digit ID column (no signs) gets converted to a WholeNumber type. These numbers are no longer unique and dropping duplicate rows based on this column reduces the size of the table by half. When converting the ID column back to text, every row has a unique ID again and no duplicates are detected. Why?

[Greg_Deckler]

I would say that according to this article:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-types/

it should be 19 actual digits and not 18 plus the +/- sign.

[Gazzer]

Yes, agreed - it is 19 digits without the sign.

Any thoughts on the issue I am having?

[v-huizhn-msft]

Hi @Gazzer,

It's weird, do you mind share the .pbix for further analysis? You can share it by private message.

Best Regards,
Angelia