Concatenate text from 2 columns in 2 related tables

[mbigham]

Good morning,

I have two tables.  Table 1 includes the headers from our sales orders, and table 2 contains the customer delivery requirements.  I need to concatenate the left 3 letters from the Location Code in Table 1 with the Equipment Code from Table 2 and seperate them with a hyphen.

Table 1 and Table 2 have a relationship with the customer number.

Table 1 data looks something like this

Customer#          SalesOrder#          LocationCode

Customer1          SO111                   SOUTH

Customer1          SO222                   NORTH

Customer2          SO333                   SOUTH

Customer3          SO444                   WEST

Table 2 is like this

Customer#          EqCode

Customer1          REF-PLJ

Customer2          REF

Customer3          REF-PLJ

Desired result:

Customer#          SalesOrder#          LocationCode      EqiupCode

Customer1          SO111                   SOUTH                 REF-PLJ-SOU

Customer1          SO222                   NORTH                 REF-PLJ-NOR

Customer2          SO333                   SOUTH                 REF-SOU

Customer3          SO444                   WEST                    REF-PLJ-WES

I am using direct query from our ERP.

I tried this: CustomColumn = Table2[EqCode]&"-"&RELATED(LEFT(Table1[LocationCode],3))

I don't think the logic behind that is quite right, because I can have the same customer listed several times and the location code can change each time, so it has to filter by the sales order number. 

Can someone help a rookie out?

[v-frfei-msft · Accepted solution]

Hi @mbigham ,

To use the updated formual.

Column = RELATED(Table2[EqCode])& "-"&LEFT(Table1[LocationCode],3)

Please find the pbix as attched.

Regards,

Frank

[mbigham]

Hello @v-frfei-msft

I can't seem to get past this relationship error:  "The column 'Sheet1[EqCode]' either doesn't exist or doesn't have a relationship to any table available in the current context."

My table with the EqCode, is an imported excel sheet.  My table with the Order number is a direct query.  They are related on the customer number.  I tested several ways and the only way i was able to get it to work was to switch my sales header table to import instead of direct query.  Do you know what is stopping the relationship from working with one being direct query?

Thank you