Power BI need to separate column values into rows but there is no delimeter

Question

Here is an example of my data table

ID	Values
1	Red
2	Blue
3	Green
4	BlueRed
5	BlueGreen
6	YellowBlueRed
7	Green. Light blue
8	RedNeon Pink
9	BlueNavy Blue (Royal)

As you can see, the values column contains more than one color per row. I need to split the values so that each row has only one color. The expected result is as follows:

ID	Values
1	Red
2	Blue
3	Green
4	Blue
4	Red
5	Blue
5	Green
6	Yellow
6	Blue
6	Red
7	Green
7	. Light blue
8	Red
8	Neon Pink
9	Blue
9	Navy Blue (Royal)

Our IT department, as you can see in their infinite wisdom (/sarcasm), stores the values column without comma separated values. You'll also notice that I can't rely on capital letters as delimiters.

I was thinking that the solution might involve inserting a comma after each word (color) but because some colors have spaces and are made up of more than one word, I'm at a loss as to how to go about doing this in Power Query.

I am aware of the Text.Insert function, but it requires knowing the position where you need to insert the character (the comma) but the position is random (based on what colors are stored) and more than one comma might need to be inserted if the value contains more than 2 colors.

The number of colors stored per row is limited only by the total number of colors the user can select from but only one of each color can be stored (There are no BlueBlue values for example).

The example set provided only includes a limited list of colors, but the full list is much longer.

Does anyone have any ideas as to how to achieve the expected result?

Answer 1

1. select split the column by lowercase to uppercase.

2. select 3 new columns and merge them with "." as separator.(i choose dot because of Green. Light blue)

3. split the column by Delimiter. choose custom Delimiter and in advanced option choose split into Row.

4. and then filter blank rows