Forum Discussion

lkshck's avatar
lkshck
Helper III
4 years ago
Solved

New column summarize Values by Name

Hey, 

I'm currently having a table with one Column which looks like the following:

PORTAL:123456789
PORTAL:876543
PORTAL:86235872
Test1
Test2
Test3

PORTAL:623856

 

My goal is to create second column which writes the Name of the first column to it but summarizing those which have PORTAL infront or cutting everything which is behind PORTAL. So second column should look like the following:

PORTAL
PORTAL
PORTAL
Test1
Test2
Test3
PORTAL

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    lkshck 

    you can try this

    Column = 
VAR _check=SEARCH(":",'Table'[Column1],1,0)
return if(_check=0,'Table'[Column1],LEFT('Table'[Column1],_check-1))

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    lkshck 

    pls try this

    Column = if(LEFT('Table'[Column1],6)="PORTAL","PORTAL",'Table'[Column1])

    • lkshck's avatar
      lkshck
      Helper III
      4 years ago

      Thanks! Worked fine for this case. Is there any other way which doesn't require the hardcoded count of character number of 6?

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        4 years ago

        lkshck 

        you can try this

        Column = 
VAR _check=SEARCH(":",'Table'[Column1],1,0)
return if(_check=0,'Table'[Column1],LEFT('Table'[Column1],_check-1))