cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Inner Join with custom conditions

I need to reproduce an MSSQL Join in PowerBI, please help (yes I am new)

 

SELECT [dbo].[Table2].[sID],
[dbo].[Table1].[rVer]
FROM [dbo].[Table1]
JOIN [dbo].[Table2] ON [dbo].[Table2].[sName] = (REPLACE([dbo].[Table1].[objImpact], 'per_', ''));

 

Any assistance would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: Inner Join with custom conditions

@toolatejake solution is ok but don't merge in power query, just load both the tables and set relationship between the tables on the columns you are using for join in SQL, but yes, do the replacement in Power Query

 

Merge is an expensive query and if not required, one should avoid it.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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

7 REPLIES 7
Highlighted
Solution Sage
Solution Sage

Re: Inner Join with custom conditions

Hi TallKewlOnez,

 

Power BI syntax is a bit different than SQL syntax and I am going to answer the question as asked but I am wondering if you should be doing this by using this method at all.  It appears to me that you are just "moving SQL to Power BI" when it might be more efficient to use Power BI to import the tables and link them in the Manage Relationships.  This will probably give you more flexibility and less need to rely on the SQL.

 

That said, if you are trying to for a SQL statement something like this should work (to be honest, I am not positive about the "replace" part but you can probably experiment a bit to get that right:

SELECT Table2.sID, Table1.rVer
FROM Table1
LEFT JOIN Table2 ON Table2.sName = (REPLACE(Table1.objImpact, 'per_', ''))

Highlighted
Helper V
Helper V

Re: Inner Join with custom conditions

You will need to use power query to accomplish this. You will need to do the following -

1) Import both tables into power query

2) Perform any the "Replace" Transform on table 1.

3) use the "Merge" function in power query to join the tables.

Highlighted
Super User VII
Super User VII

Re: Inner Join with custom conditions

@toolatejake solution is ok but don't merge in power query, just load both the tables and set relationship between the tables on the columns you are using for join in SQL, but yes, do the replacement in Power Query

 

Merge is an expensive query and if not required, one should avoid it.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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

Highlighted
Helper V
Helper V

Re: Inner Join with custom conditions

Agreed merge should be avoided. Relating tables is preferred but maybe a bit unintuitive.

Super User I
Super User I

Re: Inner Join with custom conditions

There's probably a more technial way to do this, but to keep it simeple (Since Manage Relationships in Power BI Desktop doesn't have 'code' behind it) I created a New Column  in Table1 and used SUBSTITUTE to remove any "per_" and replace with blank.   Then I can link the new column to SName to create the relationship.

Forrest

 

 

** My picture isn't showing up... here's the code I used...**

New Table = SUBSTITUDE(Table1[objImpact], "per_","")



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Highlighted
Super User I
Super User I

Re: Inner Join with custom conditions

Capture.PNG



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Highlighted
Helper I
Helper I

Re: Inner Join with custom conditions

Thanks very much

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors