Left Outer Join/Left Four Characters

[imani_tech]

So I'm trying to re-create the following in Power BI:

SELECT
i.*,
d.[EmployeeID],
d.[LOB]
FROM [dbo].[tblImport] i
LEFT JOIN [dbo].[tblDepartments] d
ON LEFT(i.[Delegate], 4) = d.[EmployeeID]

First, I created a calculated column to get the left 4 characters of the Delegate field, and then the Relationships button to create the relationship.  I could not create a relationship because the values in the Delegate field are not unique.

Second, I went to the Query Editor to try to create the LEFT JOIN  there.  I attempted to add a calculated column to get the left 4 characters of the Delegate field.  I received an error.  Here is the code I attempted:

Left4 = LEFT('Main Data Source'[Delegate], 4)

What am I doing wrong?  Am I attempting something that Power BI was not built to do?

[MFelix · Accepted solution]

Hi @imani_tech,

In the query editor the language is not DAX is M so the formulas are different you need to yse the following formula:
Text.Start([Delegate], 4)

Be aware that in power query the formulas are case sensitive so you need yo put it with the caps in first letter of each word.

Regards
MFelix