Forum Discussion
Helper IISplitting a text in a column
I have tried to use certain transfomations in Power query, Please let me know if this is what you are looking for.
I will try to explain the working later:
https://drive.google.com/file/d/1oweJyDKNJ5JqK6UNTIrWxPRCUhyb7fFX/view?usp=sharing
Solution SageI have tried to use certain transfomations in Power query, Please let me know if this is what you are looking for.
I will try to explain the working later:
https://drive.google.com/file/d/1oweJyDKNJ5JqK6UNTIrWxPRCUhyb7fFX/view?usp=sharing
- mahenkj2
Hello RingoSun
Though, I have attached a sample file for you as an example, it really also depends how your column data looks like. If there is certainty that there shall be some numbers after which you finally want to split, and only after second space you need this split (first split is between the 'Test' and numbers), then this sample file should be showing exactly that.
If your data has some other behavior, even then it should be possible to undertstand the possibilities of Power query for all those varying needs.
So what I did here:
1. Split the column at left most delimiter, i.e. a space here.
2. Split the second column again at left most delimiter i.e.a space.
3. The way your data is, first record is Test without a number, but other record have numbers. So column generated in step 2 above have text as well as numbers. We need to repace text in this column with blank. So I used Text.Select function.
4. Finally merged 2 columns of interest and removed unnecessary columns.
If any questions, please do let us know.