More Distinct/Unique values than rows in table

[sslezic]

I'm importing a simple set of data from ADX table. One of my columns is a GUID, uniquely idenifying each row.

After importing the table into PowerBI (both direct query and import modes behave the same), I'm getting bizarre behaviour from Power Query and the model. Here's an example:

According to this, I have more distinct values than there are rows in the table. I also, while messing around with filters, managed to get unique values to be greater than distinct values, as well as get unique values to be greater than the number of rows.

All GUIDs should be unique (I have verified this 3 different ways). There are no empties, nulls, errors. All GUIDS have been tolower'd and trim'd.

The only hint I have is Text.Clean function. It is the one thing that makes an appreciable difference. However, I cannot run it with Direct Query (which is my preference for this model). I also don't understand why it WOULD make any difference here, or what is the problem it is supposed to be solving. I have no escape characters in my data. They are just standard alphanumeric GUIDS with dashes (as seen in the screenshot). It is also impossible to isolate a row of data that is causing issues. I tried filtering subsegments of data (by GUID starting character) and have not been able to isolate any subsegment that would reliably cause an issue. The issue usually appears when I have more than 1100 rows, in any permutation of data subsegments.

[sslezic]

I have an open ticket with MS Power BI tech support. I will try to stear them your way and see if maybe htye can use this additional data to help debug.

[MorezPT]

Hey @sslezic, have you received any updates on this?

[sslezic]

I passed onto them your example. They thanked for additional information and have nor heard from them since. Will update here when I hear more.

[MorezPT]

we have a similar issue in PowerBI. we're trying to import datetime column from ADX to PowerBI and we can see that the number of distinct values are more than the unique values. the unique value count matches what we have in our ADX data but there seems to be some extra distinct values getting added by PowerBI.

we tried importing the datetime as text and then clean the text column and it seems like that fixes the issue but it means we have to switch to import mode which is not ideal for us.

You can also reproduce our issue using the following Kusto queries:

`range TimeGenerated from datetime(2024-01-01T21:30:00.000Z) to datetime(2024-01-08T23:50:00.000Z) step 10m` gives us 1023 unique and distinct values

but

`range TimeGenerated from datetime(2024-01-01T21:10:00.000Z) to datetime(2024-01-08T23:50:00.000Z) step 10m` gives us1016 distinct and 1025 unique values

which is quite strange. any help is appreciated

[sslezic]

I'm still having this issue. I feel I should mention that this is a showstopper for me as this issue prevents me from using this ID column as a foreign key in another table. When this issue pops up PowerBI insists that I must have many-to-many relationship. This happens when my tables are in DirectQuery mode. If I convert them into Import mode, the relationship problem seems to go away. However, as I mentioned in my initial post, I require these tables to be DirectQuery.

I would appreciate it very much if someone official could respond to this to at least let me know if this is a bug or a dumb-user-error. So that I can adjust my requirements/plans going forward.

[amitchandak]

@sslezic , That seems strange. There is an option at the bottom to check it full data, try that out and check

[sslezic]

And this is my today's counts:

Today, I have more unique values than distinct values. Still looking for clues and hints as to how to address this.

[sslezic]

@amitchandak you'll notice that my screenshot shows more than 1000 data points. It was generated on the full data set.