Forum Discussion

IvanS's avatar
IvanS
Helper V
4 years ago
Solved

Remove text string based on given text string from the list

Hi guys,


I am approching you as I am stuck with replacing given set of characters from the text string. Let's say I have following table:

 

Detailed LocationConsolidated Location
New York GeneralNew York
New York (1)New York
New York (2)New York
New York (10)New York
Paris (3)Paris
Paris (8)Paris
London GeneralLondon
London (15)London
London (7)London

 

What I need is to remove specific text string from the column "Detailed Location" and create Consolidated Location column. The text suffix always consists of word "General" or numbers (1), (2), etc. The problem is that when new location is opened, the number is added so in future there can be 15 or 20 locations. I would prefer to set the dynamic condition (which will look for text string "General" and simultaneously for any number in brackets) or as second option to create variable table with the list of text strings which would need to be manually adjusted (see below). Unfortunately, here is PBI expecting single value instead of multiple values.

 

 

Consolidated Location = 
VAR TextString = {"General", "(1)", "(2)", "(3)", "(4)", "(5)"} 
RETURN 
SUBSTITUTE(nav_DIM_Location[Name], TextString, "")

 

Thank you for any help

Ivan

Need Help
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this

    Consolidated location = 
    TRIM(
        SUBSTITUTE(
            MID([Detailed Location],1,
                FIND("(",[Detailed Location]&"("
                )-1
            ),
            "General",""
        )
    )

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    How about a Power Query solution?

    Replace Values in the column -  replace General with empty value

    Then split the column by delimiter - using ( as a delimiter

    • IvanS's avatar
      IvanS
      Helper V
      4 years ago

      This is unfortunately not possible as the column Detailed Location can consists of characters "(" and ")". In fact, this is the warehouse name - I have just used the cities to simplify the table.

       

      The warehouse name can looks like below:

      - Warehouse name (ex. old name) (C1) (1) 

      - Warehouse name (ex. old name) (C1) (2) 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Use this

    Consolidated location = 
    TRIM(
        SUBSTITUTE(
            MID([Detailed Location],1,
                FIND("(",[Detailed Location]&"("
                )-1
            ),
            "General",""
        )
    )