Forum Discussion
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 on suffix values 1,25,12
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.
4 Replies
- goncalogeraldes
Super User
Hello there Anonymous ! What is your criteria for splitting? Is there any kind of pattern?
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
Community Support
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.
