Clean Same Column with Multiple Conditions

[Vishruti]

I have following data

1. The blue fields have an extra dash at the end, which I want to remove

2. The orange cells have extra texts. I want to take the middle characters eg. ABC Ltd

3. The green field has an extra space before the text string, which I want to remove

If I apply one step to remove one error, the rest of the text is coming as blank

How can I achieve this data cleaning while retaining all values in the final column in Transform? I need to eventually build relationship with other tanle based on this column.

[Ahmedx · Accepted solution]

pls try

[Anonymous · Accepted solution]

Hi  @Vishruti ,

Here are the steps you can follow：

1. Create calculated column.

Result =
VAR _conditional1 =
    FIND ( "-", 'Table'[Site Name], 1, BLANK () )
VAR _originallen =
    LEN ( 'Table'[Site Name] )
VAR _conditional2_1 =
    FIND ( "->", 'Table'[Site Name], 1, BLANK () )
VAR _conditional2_1_result1 =
    IF (
        _conditional2_1 <> BLANK (),
        RIGHT ( 'Table'[Site Name], _originallen - 1 - _conditional2_1 )
    )
VAR _find2_2 =
    FIND ( "->", _conditional2_1_result1, 1, BLANK () ) + 1
VAR _conditional2_1_result2 =
    LEFT ( _conditional2_1_result1, LEN ( _conditional2_1_result1 ) - _find2_2 )
RETURN
    SWITCH (
        TRUE (),
        _originallen = _conditional1, LEFT ( 'Table'[Site Name], _originallen - 1 ),
        _originallen <> _conditional1
            && _conditional2_1 <> BLANK (), _conditional2_1_result2,
        [Site Name]
    )

2. Create calculated table.

new table =
DISTINCT('Table'[Result])

3. Result:

Best Regards,

Liu Yang

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

[Ahmedx · Accepted solution]

pls try