Forum Discussion
Splitting a text in a column
Inside a column I have values such as:
Column |
Test Nice Product |
| Test 1 Product |
| Test 2 Good Product |
| Test 3 Product |
I want to do the splitting inside the same column (I don't want new columns to be created) so it should look like this
| Column |
| Test |
| Test 1 |
| Test 2 |
| Test 3 |
In other words, I want the words after Test to be excluded. How do I do that?
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
6 Replies
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
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.
Hi,
In the Query Editor, select this column and click on Replace Values. Find for Product i.e. space and Product. Click on Replace.
RingoSun because rows has data with many spaces. You can try Ashish_Mathur 's solution or you can try with DAX something like:
column= Left/right('table[column]', 6]
