Forum Discussion
Merge columns as new events
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
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
4 Replies
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
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
NewStep=Table.RemoveColumns(Table.UnpivotOtherColumns(Product_colors,List.Select(Table.ColumnNames(Product_colors),each not Text.Contains(_,"Color"))),"ColorX","Color"),{"ColorX"})
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
