Leading zeros for alphanumeric columns

[ShaneL79]

Hello Everyone,

I have two different data sources that I am trying to connect using system IDs. The problem is that there is no consistency in how the two departments have named the systems.  

Example:

Source 1 - System Names	Source 2 - System Names
01	1
02	2
03A	3A
15	15
3003	3003

Basically, on the first three rows a leading zero is needed. The last two are okay.

I have found plenty of resources for adding a leading zero, but none cover different length of values or values that contain a letter. 

Any idea how to make source 2 look the same as source 1?

Thank you.

[v-cgao-msft · Accepted solution]

Hi @ShaneL79 ,

Try adding a custom column in the PowerQuery editor:

if Text.StartsWith([System Names],"0") then Text.Range([System Names],1) else [System Names]

Best Regards,
Gao
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!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ShaneL79]

Thank you. This worked.

[lbendlin]

What if Source 1 all of a sudden has both  03A and 3A  system names (and they signify different systems) ?