Inner Join with custom conditions

[TallKewlOnez]

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.

[fhill]

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_","")

[fhill]

[Anonymous]

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.

[parry2k · Accepted solution]

@Anonymous 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!

[TallKewlOnez]

Thanks very much

[Anonymous]

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

[collinq]

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_', ''))