Join/Relationship on table not working as expected due to automatic text trimming

[CalebR]

Hello,

I have two tables from two different databases that keep track of our sales orders. My goal is to create a report to see how accurate these databases are to each other but I am running into an interesting issue.

I have joined the tables together on SalesOrderNo but in one database the sales order number is listed as "IA381" meanwhile in the other the same SO# is listed as "00IA381". This is causing the relationship not to work correctly. I want to add a related column in my SO_SalesOrderHeader that grabs the SalesOrderNo from the separate connected database and then I would add another column afterward that compares those two against each other.

So basically it would look like this -

SalesOrderNO	DATE	ORDERTYPE	GenesisSalesOrderNO (related column)	SalesOrdersMatch?
00IA381	01/01/2021	O	IA381	YES
00DC046	01/18.2022	O	DC046	YES

This doesnt work now though since the Related() function cant work because it cant find the GenesisSalesOrderNo since 00IA381 doesnt match IA381. Im trying to refrain from automatically trimming the leading zeroes of off the SO# because the true SO# does need those zeroes to be accurate. Im not completely against inserting two 0's in the table from the DB that is trimming them off already but im not sure how to do that so any help would be appreciated.

[andhiii079845]

Make please a example for the table how it looks now and how you want to change it. Than its easier to help 🙂

[CalebR]

two tables-

table1 

SalesOrder	Date
00IA381	01/10/2022
00DC63	01/15/2022

table 2

SalesOrder	Date
IA381	01/10/2022

I expect the following returned in table1 

SalesOrder	Date	IsPresentInTable2?
00IA381	01/10/2022	YES
00DC63	01/15/2022	NO

[andhiii079845 · Accepted solution]

My little walkthrough:
I insert both tables, go to table1, insert "New column" (I fix the date column later, not shown here)

The function:

SalesOrderShort = right('Table'[SalesOrder],len('Table'[SalesOrder])-2)

 The result:

I create a relationship: Be aware of the right direction depands on your real data. It is not clear in the moment for me. I choose this directions because the on Order misses in the other table

Then you can check the result:

Before going further in the walkthrough. My question is if you have to check also the Date column: 
Are Sales Order and Date column euqal? 

[CalebR]

This makes sense to me! I got it working, no I didnt need to do anything with the date yet hopefully should be good.

Thank you for your help!!

[andhiii079845]

Sure, use calculated columns. Than you have both columns. One for join, a other one for showing something in a matrix.

[CalebR]

Im not sure what you mean. Ive tried adding a column using related( Table2[SalesOrderNo] ) but it just returns a blank since the SO#s dont match. What would be the correct way to add a calculated column to table2 where it takes the shortened sales order and adds two leading zeros to it?

[andhiii079845]

What is now the problem, to remove this "00" ? If it alway "00" and not "000" or "0" -> mynewcolumn = right(SalesOrderNo,len(SalesOrderNo)-2))

[CalebR]

Is there a way to alter table2 (the table that is already autotrimming the leading zeroes) to add the zeros back on? And then I can just join table1 00IA381 on to the newly altered table2 IA381 (now 00IA381) value?