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

Duplicates values exist in a column when in report / data view but not in query editor

Hi, I have encountered a problem whereby a column which is unique in query editor appears to lose uniqueness in the report view. I'm a certain as I can be the the column is unique in the data source and query editor but it is definitely not unique in report view because - amongst other checks - I cannot use it to create a relationship and the data view shows row count > distinct values

 

Has anyone seen this before? I've seen posts about similar issues here but they didn't get resolved.

 

Other useful info:

 

- The column is the primary key of a table in a PostgresSQL database and is of type int8 (and there are no leading zeroes)

- Power query imports the column as a whole number (without an additional 'changed type' query step)

- Using Count Values & Count Distinct Values (from Transform > Statistics) returns an identical value and the value matches a row count in the database

- I've also tried doing a full refresh, a preview refresh, and a restart

- It affects another dimension table from this database but not all of them

 

PS unfortunately I cannot share the PBIX

Thanks

 

 

13 REPLIES 13
MarcelBeug Super Contributor
Super Contributor

Re: Duplicates values exist in a column when in report / data view but not in query editor

If the column is of Int8 type than you have a maximum of distinct 256 values, so if you sort and check in the data view, you should easily find the duplicates (or any empty values)?

 

I know a phenomenon where adding a column with Int8.Type displays values in Power Query, even if they are outside the range, but they won't load into the data model.

I raised an issue some time ago, but it is qualified as "by design".

Try the simple query below and you'll see results in the Power Query editor that won't load into the data model.

 

let
    Source = {125..130},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1], Int8.Type)
in
    #"Added Custom"
Specializing in Power Query Formula Language (M)

Re: Duplicates values exist in a column when in report / data view but not in query editor

Thanks Marcel, the Int8 is the data type in PostgresSQL, which is a 64 bit int, so the values shouldn't be out of range. The value in query editor was 'whole number'. I decided to try explicty converting the column to type Int.64 but that didn't appear to make a difference

MarcelBeug Super Contributor
Super Contributor

Re: Duplicates values exist in a column when in report / data view but not in query editor

You can try and create a column chart with the count of the values, so anything >1 will be visible immediately.

Specializing in Power Query Formula Language (M)

Re: Duplicates values exist in a column when in report / data view but not in query editor

Thanks Marcel; I'm able to identify the duplicates, but it isn't clear why they are being duplicated. The final result in the query editor should be identical to the data table in the report view, or at least I'm not aware of any reason why it shouldn't be...

MarcelBeug Super Contributor
Super Contributor

Re: Duplicates values exist in a column when in report / data view but not in query editor

Well then please share the information so we'll be better able to help you.

Which value is duplicated and what are the corresponding values in the Query Editor?

 

 

 

Specializing in Power Query Formula Language (M)

Re: Duplicates values exist in a column when in report / data view but not in query editor

In the report view there are 403,320 rows and 399,226 distinct values, so 4,094 duplicates (all the duplictaes have 2 rows). There doesn't appear to be a pattern in the duplicated ids

 

As you can see from the 2 images below, id 35 occurs twice in report view, once in the query editor

 

duplicates.PNG

 

duplicates2.PNG

 

 

MarcelBeug Super Contributor
Super Contributor

Re: Duplicates values exist in a column when in report / data view but not in query editor

Hm, that doesn't look like Int8 as these range from -128 thru 127.

 

Anyhow, this is what I would do to analyse: add an Index column to the table and check which index values show up with id 35 in the data model and compare this with the id's in the Query Editor for those index values.

Specializing in Power Query Formula Language (M)

Re: Duplicates values exist in a column when in report / data view but not in query editor

Int8 is the data type of the column in PostgresSQL, which is an 64 bit interger (8 bytes).  The data type of the column in query editor is 'whole number' which I think defaults to Int.64. So I don't think that should cause a problem

 

I can find the duplicate values no problem, it's why they are bing duplicated I'm struggling with. Interestingly, when I do a full refresh the duplicate ids change.

MarcelBeug Super Contributor
Super Contributor

Re: Duplicates values exist in a column when in report / data view but not in query editor

Please read part 2 of my previous post, with a suggestion how to analyze. That should give you the values in the Power Query editor that are duplicates in the data model. Maybe that can lead to an explanation why these are distinct in the Query Editor and duplicate in the data model. 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)