Forum Discussion
Relationship column with slightly different naming
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!
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 TeamIf 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!
3 Replies
- Idrissshatila
Super User
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.
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
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 TeamIf 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!
