Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Carol Chu]

I have a need to link 2 objects from 2 different data sources and the Field Name and Field Type are different for the 2 objects.  Sales Order and Line Position are numeric (without leading zeros) from the first object and Sales Order and Line Position are Alphanumeric (with leading zeros) from the other object.  What is the best way to link the 2 objects by the Sales Order and Line Position?

——————————
Carol Chu
94547
carol_chu@bio-rad.com
Hercules CA
5107416998
——————————

[Audrey Abbey]

Carol, 

Are you going to “link” by creating a relationship?
Or will you join the tables in the query editor?

Either way, you will need to either convert the data type from alphanumeric to whole number or decimal in the query editor. 
That will eliminate the leading zeros. 
Or you can duplicate the column first, if you need to retain the alpha-numeric version for other reasons.

Column names do not need to match, but you can always rename or duplicate/rename the column.

Power BI Query editor has a full set of data transforms. You can do a lot with it. 

Let me know if you have more questions!
Audrey

——————————
Audrey Abbey
SR. BI Developer/Analyst
New Seasons Market
Portland OR
——————————
——————————————-

[Ian Stuart]

Aubrey is spot on.  You can use the new column by example feature as well if you are not too confident with transformations.  

Ian

——————————
Ian Stuart
Principal
ians@altisglobal.co.uk
London
7946535454
——————————
——————————————-

[Carol Chu]

Hi Audrey,

Thank you very much for the helpful tips.  I was able to duplicate the column, change data type of the field and execute an Extract Last x digit to align the value from table 1 to value in table 2.  I was using the fields to create a relationship for a report that checks for exceptions between the 2 tables.

Thanks,
Carol

——————————
Carol Chu
94547
carol_chu@bio-rad.com
Hercules CA
5107416998
——————————
——————————————-