Forum Discussion

adoalan's avatar
adoalan
Icon for Helper III rankHelper 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
Icon for Resident Rockstar rankResident 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
Icon for Helper III rankHelper 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
    Icon for Resident Rockstar rankResident 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
      Icon for Resident Rockstar rankResident 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 )