cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gazzer Member
Member

Whole Number ID Problem (19 Digits)

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?

7 REPLIES 7
Super User IV
Super User IV

Re: Whole Number ID Problem (19 Digits)

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Gazzer Member
Member

Re: Whole Number ID Problem (19 Digits)

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

 

Any thoughts on the issue I am having?

Microsoft v-huizhn-msft
Microsoft

Re: Whole Number ID Problem (19 Digits)

Hi @Gazzer,

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

Best Regards,
Angelia

Ylreeb Frequent Visitor
Frequent Visitor

Re: Whole Number ID Problem (19 Digits)

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

AndreyV Frequent Visitor
Frequent Visitor

Re: Whole Number ID Problem (19 Digits)

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?

Ylreeb Frequent Visitor
Frequent Visitor

Re: Whole Number ID Problem (19 Digits)

Dear PowerBI-Team,

 

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

Now stumbling when working with Python DataFrames.

 

Please fix it!!

vlknyzc Visitor
Visitor

Re: Whole Number ID Problem (19 Digits)

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.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors