Merge columns as new events

[JPNK]

Hello all, I've been trying to find a solution to my problem, and I haven't been able to work it out.

Probably I'm not using the correct keywords, as when I search for "merge columns" it usually directs me to solutions like concatenate ou add text from different columns, but my problem is different.

Let me give you an example using Excel.

So this is an example of the original datasource (table called "Product_colors")

(text format)

SKU	Product type	Color1	Color2	Color3	arrival_date	Stock
1234	square	red	white		24/03/2020	25
4567	square	red			15/08/2020	35
5678	round	purple	gray	white	01/01/2021	15
2525	square	white			20/02/2021	10
1111	round	black	pink	red	15/07/2021	1
6598	fizzy	white			24/12/2022	65
6969	triangle	green			30/01/2023	68
4201	googly	orange	yellow	white	25/03/2023	8

and I need to create a new table (linked to this one, as this one will change monthly), that will get all the colors (Color1/Coler2/Color3) under a single column, and relate to the other columns that already exist.

Let me show you an example for the first line (SKU 1234)

and for product w/ SKU 5678

So the final result would be something like all colors listed (regardless if they exist in Color1/Coler2/Color3, and then the respective fields from the line where that color was taken, e.g. for final result: 

(text format)

Color	SKU	Product type	arrival_date	Stock
red	1234	square	24/03/2020	25
white	1234	square	24/03/2020	25
red	4567	square	15/08/2020	35
purple	5678	round	01/01/2021	15
gray	5678	round	01/01/2021	15
white	5678	round	01/01/2021	15
white	2525	square	20/02/2021	10
black	1111	round	15/07/2021	1
pink	1111	round	15/07/2021	1
red	1111	round	15/07/2021	1
white	6598	fizzy	24/12/2022	65
green	6969	triangle	30/01/2023	68
orange	4201	googly	25/03/2023	8
yellow	4201	googly	25/03/2023	8
white	4201	googly	25/03/2023	8

If anyone could help, I would appreciate it.

Thank you in advanced

[Einomi]

Why not a simple Unpivot Other Columns ?

You select the following columns : SKU, Product Type, Arrival Date, Stock

Then right click > unpivot other columns

This way if you get more colours in the future it will update, in your solution @JPNK  you assuming you cannot get more than 3 columns...shame to hardcode this, with unpivot it will work even if you get more than 3 colours

[JPNK]

That's a good idea. Right now the product only has 3 color specs, but if this changes I'll definitely consider your suggestion.

Thanks for the input

[JPNK · Accepted solution]

Actually I already figured it out.

So the solution for me was to merge all colors into a new column

all_colors = Product_colors[Color1]&","&Product_colors[Color2]&","&Product_colors[Color3]

then I clicked on split column by delimiter

On spliting, I chose to split into rows instead of columns

After that I just filtered out all the blanks from this new column, and job done.

Thanks to anyone who read this request

[wdx223_Daniel]

NewStep=Table.RemoveColumns(Table.UnpivotOtherColumns(Product_colors,List.Select(Table.ColumnNames(Product_colors),each not Text.Contains(_,"Color"))),"ColorX","Color"),{"ColorX"})