cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrianC
Frequent Visitor

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

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?

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

CahabaData
Memorable Member
Memorable Member

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.

www.CahabaData.com

View solution in original post

2 REPLIES 2
CahabaData
Memorable Member
Memorable Member

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.

www.CahabaData.com
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors