How to show the data in a many to many with bridge table

[Andyr]

Hi, 

I've got two tables that are related with a bridge table in the middle to create a many to many relationship. 

The data is coming from different data sources. 

As an example:

Table Foo (From sql server)
   Id
   Name

Table Bar (From excell)
   Id
   Name

 
Table FooToBar (From another excell linking many Foos to many Bars)
   Id
   FooId
   BarId

I've set these tables up with relationships.

All I want to currently do is show in a table visual all rows in the Foo table and all rows in Bar table as they are realted by the bridge table as you would in SQL with inner joins relating the data simular to :

select * from @fooToBar ftb
inner join @foo f on f.id = ftb.fooID
inner join @bar b on b.Id = ftb.BarId

I've tried just using the table visual but as soon as I select columns from the 'bar' table I get an error unless I set the column to an agregate such as count.

Is this actually possible in PBI? 

[Anonymous · Accepted solution]

Yes,

There is an "old way" and the "new way" to handle this.

Please refer to the following article (you can jump directly to the "new way")

https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/

Michael

[Andyr]

Thanks Michael

The problem was that there was a duplicate Id in one of the tables, once I removed that the solution you pointed me to works a treat. 

Cheers