Relationship column with slightly different naming

[bakedpotatoes]

Hello,

My Job Number column is being used as the column to establish a relationship with other tables, please see figure below as reference. 

I am running into issues where some of the job numbers are entered slightly differently across different tables, with the following variations:

" A21/0847/MS"  (issue: spacing before the job number)

"A21/847/MS"  (issue: missing a 0 after A21/ )

These variations misleads PowerBi to recognise them as different job numbers (they should be the same job number). 

There too many mistakes like this scattered throughout the tables for me to manually change them myself. 

Is there anyway for PowerBi to recognise these variations as belonging to ONE job number, not multiple job numbers?

Thank you! 

[Anonymous · Accepted solution]

Hi @bakedpotatoes ,

Thanks for the reply from @Idrissshatila .

To replace A21/0945/ME with A21/945/ME, follow my steps below.

Here is the sample data I created:

Column1	Type	Custom
A21/847/MS	AA	8
A21/0847/MS	BB	0
A21/0847/MS	CC	0
A21/0847/MS	DD	0
A21/847/MS	EE	8
A21/847/MS	FF	8
A21/847/MS	GG	8
A21/847/MS	HH	8

First, create a custom column in Power Query:

Text.Middle([Column1], 4, 1)

Change the format of the Custom column to Text.

Create a measure to determine whether the value needs to be replaced based on Custom.

Measure = 
IF(
    MAX('Table'[Custom]) = "0",
    REPLACE(MAX('Table'[Column1]),4,2,"/"),
    MAX('Table'[Column1])
)

The final page effect is as follows:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Idrissshatila]

Hello @bakedpotatoes ,

if the mistakes are only added spaces, you can remove the spaces in Power query under the transform tab, there is format option then click on trim.

[bakedpotatoes]

Hello @Idrissshatila , thanks for the solution. 

I'm just wondering if there is a solution for an added 0? eg. A21/0945/ME when it should be A21/945/ME