Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Split columns based on multiple suffix values

I want to split column based on suffix values in Powery query and need to create 2 custom column   For example,  Column needs to be split and custom columns Main and Key needs to be created based o...
DAX Commands and Tips
Need Help
Power Query
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Anonymous 

     

    You can create two columns like this:

    Key = SWITCH(true(),RIGHT([Column],1)="1",1,RIGHT([Column],2)="25",25,RIGHT([Column],2)="12",12)
    Main = SWITCH(true(),[Key]=1,([Column]-1)/10,[Key]=25,([Column]-25)/100,[Key]=12,([Column]-12)/100)

    Best Regards,

    Community Support Team _Janey

    If this post helps, then please consider Accept it as the solution to help the other members find it more.

     

goncalogeraldes's avatar
goncalogeraldes
Icon for Super User rankSuper User
4 years ago

Hello there Anonymous ! What is your criteria for splitting? Is there any kind of pattern?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi goncalogeraldes , yes based on the key values which are added at last . In the above example for value 567891 , 1 is key similarly for value 7896525 , 25 is the key . As of now we can consider 1, 25 and 12 as key values  for entire data set

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    You can create two columns like this:

    Key = SWITCH(true(),RIGHT([Column],1)="1",1,RIGHT([Column],2)="25",25,RIGHT([Column],2)="12",12)
    Main = SWITCH(true(),[Key]=1,([Column]-1)/10,[Key]=25,([Column]-25)/100,[Key]=12,([Column]-12)/100)

    Best Regards,

    Community Support Team _Janey

    If this post helps, then please consider Accept it as the solution to help the other members find it more.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for you reposne , this works