Are certain joins impossible with PowerBI that are possible with SQL Server Management Studio?

[BrianC]

I'm trying to migrate a report I made in SQL into PowerBI but as I do it I'm finding that many of the joins won't work in PowerBI. To give one example, I am not able to join two tables together where the joining columns are not unique, however this is not a problem in SQL. Is the only solution to do all my SQL coding on the back end to create a flat file that I can bring into BI to visualize, or is there something Power BI can do here?

[parry2k · Accepted solution]

Yes for relationship, you need unique value in master table.

[CahabaData · Accepted solution]

Use the query editor steps that set up a distinct table; then join the tables to the distinct table rather than directly to each other.

yes this is different than the database approach - but you can still achieve the many:many relationship this way.

another difference is single joins only (unless my info is out of date; have been off line for a few months and the application updates frequently) - this is handled by using the query editor to create a new merged single column (from the columns that were joined in the database) in both tables -  and you join these new merged columns in Power BI.

[parry2k · Accepted solution]

Yes for relationship, you need unique value in master table.