Best way to crop number for one kind Format

[Applicable88]

Hello,

I have a storage system which many different name formats, depending on the manufacturer of that system:

Storagetype		Storage
Standard		01-01-01/1
Standard		01-01-01/2
Standard		01-01-02/1
Standard		01-01-02/1
Standard		SP 1234
Standard		SP 1235
EXPO		001-01-01
EXPO		001-01-02
EXPO		001-01-03
EXPO		001-01-04
EXTRA		ET 1500
EXTRA		ET 1501

The storagetype "Standard" has two different formats, the first one looks like this: "01-01-01" and the second one like "SP 1234". Unfortunately in storages with a format like "XX-XX-XX" the employees can manipulate the storage place and put two different products in one storage place instead of one. The only thing they need to do, so they can book  their second product is to add a number as a suffix. The first product is now in "01-01-01/1" and the second "01-01-01/2". Thats what we do internally, but in our storage capacity report we disregard that. The storage place of interest is only the distinct "01-01-01", "01-01-02" and so on.  So now we have many storage places which is double used and I need to disregard all the double usages. One way I thought is to simply crop away the last number and count the distinct storages. But I also have other storage types which has totally different formats like "ET 1501" and even the storagetype "Standard" has another name convention like "SP 1234", where I also cannot crop the number.

What is the best way to solve this problem?

Thank you very much in advance. 

Best. 

[v-stephen-msft]

Hi @Applicable88 ,

What is your expected result?

Do you want to split or extract numbers?

Split Column by Delimiter in Power BI and Power Query - RADACAD

Extract number from string power bi - deBUG.to

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Applicable88]

@v-stephen-msft I want to rectify that column as good as possible. It doesn't matter if I extract or split the values in that column, as long it only changes those which has a suffix at the end like :

Standard		01-01-02/1
Standard		01-01-02/1
Standard		SP 1234
Standard		SP 1235

At the same time all other formats should stay the same. 

Is that possible?

Best.