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

Concatenate text from 2 columns in 2 related tables

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Concatenate text from 2 columns in 2 related tables

Hi @mbigham ,

 

To use the updated formual.

 

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

Capture.PNG

 

Please find the pbix as attched.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Concatenate text from 2 columns in 2 related tables

Hi @mbigham ,

 

To use the updated formual.

 

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

Capture.PNG

 

Please find the pbix as attched.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
mbigham Frequent Visitor
Frequent Visitor

Re: Concatenate text from 2 columns in 2 related tables

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