Forum Discussion

adoalan's avatar
adoalan
Helper III
3 years ago

Help with formula

Hi All,   I need some help in removing -UK from both columns Source Code and Allocation and also everything was is after / in the Source Code     Any help please?   Thanks
Need Help
DOLEARY85's avatar
DOLEARY85
Resident Rockstar
3 years ago

Hi,

 

you can do this in power query, just right click the columns, select replace values type -uk in the first and leave the second blank

  • adoalan's avatar
    adoalan
    Helper III
    3 years ago

    DOLEARY85  I can't, wont let me if I'm querrieng the data from the database. To use Power Query I need to import the data and will take ages.

     

     

    • DOLEARY85's avatar
      DOLEARY85
      Resident Rockstar
      3 years ago

      Ah okay, 

       

      for the -uk part you could create a column in report view:

       

      Column = SUBSTITUTE('Table (2)'[Column1],"-uk","")
      • DOLEARY85's avatar
        DOLEARY85
        Resident Rockstar
        3 years ago

        For the part after the /  you could use the search function to count the starting character using wildcards in the search e.g 

        SEARCH("???-uk",'Table (2)'[Source Code], 1, 0)
         
        then use a Left function to get the left value of the string up to 1 character before the start:
         
        full column: 
        left('Table (2)'[Source Code],SEARCH("???-uk",'Table (2)'[Source Code], 1, 0)-1 )