Unwanted duplicated rows in visual table

[Darats]

Hello,

I'm struggling with a stange behavior I cannot explain. Hopefully, someone will be able to help me on it.

I have 2 tables from 2 PBI Datasets (live connection), linked via a "many to many" relationship.

If I create a visual table:

This is expected behavior, all rows from Table 1 and rows from table 2 only when data are matching between the 2 tables

If I add others fields from Table 2 : for some reason, PBI duplicates rows (yellow bellow) :

I see no reason why PowerBI behave like this and cannot find any similar problem on the forums.

Thanks,

[amitchandak]

@Darats , In such cases it is better to create one or more bridge table like ref_user in this case and use that

https://www.seerinteractive.com/blog/join-many-many-power-bi/

refer to my video on how to create Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

[Darats]

Hi,

I did follow your tutorial and try to create a Bridge table before, I didn't succeed and there came back with the many to many relation.

I've made a Bridge table  : DISTINCT ( UNION ('Table 1','Table 2') ).

I wasn't able to append Table 1 and Table 2 because I cannot have them in power query because they are PBI Datasets

Here are the results I got :

Bridge + Table 1 and Bridge + Table 2 works but I still have those duplicated rows problems (yellow above).

Table 1 + Table 2 : gives an error, which I don't understand why.

I still believe those duplicated rows are not normal, any idea?

[v-deddai1-msft]

Hi @Darats ,

You can learn more about many to many relationship from https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships and https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

Would you please show us sample pbix contains table1 and table2?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Darats · Accepted solution]

Hi,

Sorry for my late answer.

As I have live datasets I tried to embed the data directely within the pbix and I figured out that my duplicated rows issue was gone.

So I must say that it looks like a bug when having a relationship between 2 tables from live powerbi datasets.

Problem is solved at the moment as I have embed everything within the same pbix.

But if you want to reproduce, you can try to link 2 live datasets and make a relationship between them, then display the result in a visual table.

[v-deddai1-msft]

Hi @Darats ,

Thank you for sharing the solution. One thing need to be confirmed, if the two tables from different live connection data source, then the issue happens.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai