Ingredients:

1. PO data with inconsistent formatting and special characters
2. Transform Table within TBM Studio
3. Assorted Formula Columns ( Left, Right, Search, Len, If)

Assumptions:

• You have a column with PO data that contains hyphens, and you do not need this hyphen nor the data after it.
• You are cleaning this data so PO Number can be used for data relationship in an allocation. 

Steps:

1. Prepare the ingredients: Start with a Transform Table in TBM Studio and select the column containing the PO data (e.g., {myPOColumn}).
2. Find the hyphen: Create a new formula column called "PO Hyphen Find" using the Search() function to locate the position of the hyphen in the PO data: =Search(“-“,{myPOColumn}).
3. Get the length: Create another formula column called "PO Len" to calculate the length of the PO data: =Len({myPOColumn}).
4. Trim the fat: Create a new formula column called "PO Trim Math" to calculate the number of characters to trim: =PO Len-(PO Len-PO Hyphen Find)-1. This will give you the position of the last character before the hyphen.
5. Trim the PO: Create a new formula column called "PO Trimmed" using the Left() function to extract the trimmed PO number: =Left({myPOColumn},PO Trim Math). 
6. Clean up: Create a final formula column called "PO Number" to handle cases where there is no hyphen: =If(PO Hyphen Find=0,{myPOColumn}, PO Trimmed). This will return the original PO value if there's no hyphen, or the trimmed PO value if there is.
7. Serve: Use the "PO Number" column in your mapped columns, allocation line, or report to display the cleaned-up PO data.

Tips and Variations:

• You can adjust the formula to handle different types of inconsistent formatting (e.g., using the Right() function to extract data from the right side of the hyphen).

Footnote:

• This recipe brought to you by IBM Watsonx with inspiration and validation from Devon.