Why does PBI find's duplicates where there are none?

[Willborn]

Hi there!

I struggle with a strange behaviour: I like  to connect two tables in the data model. The one has a key-column with unique values, the other multiples (1:n). Now PBI don't let me build this connection, as it finds duplicates in the column where there are none.

Crazy - when I filter the list using "keep duplicates" - it shows me values - but they are not duplicates!!??

Does anyone has a suggestion?

Thanks, Patrick

Key
300020
300030
300035
300040
300042
300050
300052
300053
300054
300056
300058
300060
300070
300069_BC
300069_OBC
300220
300220_MF
300230
300230_MF
300235
300235_MF
300240
300242
300250
300252
300253
300254
300254_MF
300256
300258
300260
300270
300299_BC
300299_OBC
300020_BC
300020_OBC
300035_BC
300035_OBC
300030_BC
300030_OBC
300054_BC
300055
300069_BC
300069_OBC
300220_BC
300220_OBC
300230_BC
300230_OBC
300235_BC
300235_OBC
300299_BC
300299_OBC
300254_BC
300255
300369_BC
300369_OBC
300315
300320
300330
300335
300340
300342
300350
300352
300353
300354
300356
300360
OI0059_BC
OI0059_OBC
OI0020_BC
OI0020_OBC
OI0030_BC
OI0030_OBC
OI0035_BC
OI0035_OBC
OI0054
OI0054_BC
OI0055
300500_CSS
S10010Y10
S10010Y15
S10010Y20
S10010Y25
S10010Y49
S10010Y30
S10010Y35
S10010Y40
S10010Y50
S10010Y55
S10010Y60
S10010Y65
S10010Y70
S10010Y75
S10010Y80
S10010Y99
300500_CS
300500_CSS
300500
300600

[Seth_C_Bauer]

@Willborn There are duplicates in your key column... It's even in your screenshot #9 and #10 are the same.

300500_CSS

Are you saying that these aren't part of your initial data set that you loaded?

[Willborn]

It seems that for PBI "300500_CSS" is the same as "300500_CS" and the same as "300500" !?

[Seth_C_Bauer · Accepted solution]

@Willborn Please see this image... the first and last are the same value. Maybe you have a space at the end of one of them in your originating set when you are pulling distinct?

[Willborn]

Argh,... the system won ;o)

Thanks for showing me the trees in the wood... and sorry for taking your time!

Regards Patrick

[Willborn]

Hi Eno

The full data i've loaded is the one I pasted into the code.

300500_CSS is there only once...

When I open the file in Excel and search for duplicates, it tells me "no duplicates found"

The screenshot is the result of the PBI Desktop Function "keep duplicates". I really don't know wher he takes them...

The import file has 100 lines only.

[Willborn]

Hi there!

I struggle with a strange behaviour: I like  to connect two tables in the data model. The one has a key-column with unique values, the other multiples (1:n). Now PBI don't let me build this connection, as it finds duplicates in the column where there are none.

Crazy - when I filter the list using "keep duplicates" - it shows me values - but they are not duplicates!!??

Does anyone has a suggestion?

Thanks, Patrick

Key
300020
300030
300035
300040
300042
300050
300052
300053
300054
300056
300058
300060
300070
300069_BC
300069_OBC
300220
300220_MF
300230
300230_MF
300235
300235_MF
300240
300242
300250
300252
300253
300254
300254_MF
300256
300258
300260
300270
300299_BC
300299_OBC
300020_BC
300020_OBC
300035_BC
300035_OBC
300030_BC
300030_OBC
300054_BC
300055
300069_BC
300069_OBC
300220_BC
300220_OBC
300230_BC
300230_OBC
300235_BC
300235_OBC
300299_BC
300299_OBC
300254_BC
300255
300369_BC
300369_OBC
300315
300320
300330
300335
300340
300342
300350
300352
300353
300354
300356
300360
OI0059_BC
OI0059_OBC
OI0020_BC
OI0020_OBC
OI0030_BC
OI0030_OBC
OI0035_BC
OI0035_OBC
OI0054
OI0054_BC
OI0055
300500_CSS
S10010Y10
S10010Y15
S10010Y20
S10010Y25
S10010Y49
S10010Y30
S10010Y35
S10010Y40
S10010Y50
S10010Y55
S10010Y60
S10010Y65
S10010Y70
S10010Y75
S10010Y80
S10010Y99
300500_CS
300500_CSS
300500
300600