Forum Discussion

Vishruti's avatar
Vishruti
Helper I
2 years ago
Solved

Clean Same Column with Multiple Conditions

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.

Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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