Adding Commas to Text

[Anonymous]

Hello,

My data source is a SharePoint list and I want to add a comma within a text column so that it be cleanly split by a delimiter. This is how the text appears in some of the rows:

BaCLH-20LH-21LH-23

I want to add commas so that it looks like this:

BaC,LH-20,LH-21,LH-23

If the first part was just four characters, that would be a clean split but the first part has three characters. I have also tried SplitTextBy.CharacterTransition but that did not work effectively.

After the text gets split into different columns, each individual item (BaC, LH-20, LH-21, and LH-23) will then be summed and used in visualizations.

Does anyone have any ideas?

[Anonymous]

What I did so far was change the three-letter 'BaC' into a four-letter one and then could split by number of characters as they now were all the same number. Once divided, I could revert the three-letter one into its original form.

[Ashish_Mathur]

Hi,

What common logic has to be deployed for inserting commas?  Please take a representatie sample and show the expected result.

[Ahmedx]

Is this what you are looking for?

[Anonymous]

Yes, that what I am looking for. I was able to get a simliar result through changing the three-letter aspect into a four-letter one briefly and then reverting once the columns were split.

[Ahmedx]

pls try this

[amitchandak · Accepted solution]

@Anonymous , I power query use Split by position, multiple times with 3,4,4 and the create columns. If needed append them again

https://learn.microsoft.com/en-us/power-query/split-columns-positions

For visual you can also consider this approach after splitting and combine 

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
https://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2