Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JPNK
Frequent Visitor

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")

JPNK_0-1683794050698.png

(text format)

SKUProduct typeColor1Color2Color3arrival_dateStock
1234squareredwhite 24/03/202025
4567squarered  15/08/202035
5678roundpurplegraywhite01/01/202115
2525squarewhite  20/02/202110
1111roundblackpinkred15/07/20211
6598fizzywhite  24/12/202265
6969trianglegreen  30/01/202368
4201googlyorangeyellowwhite25/03/20238

 

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)

JPNK_0-1683792371467.png

 

and for product w/ SKU 5678

JPNK_1-1683792676047.png

 

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: 

 

JPNK_1-1683794135388.png

(text format)

ColorSKUProduct typearrival_dateStock
red1234square24/03/202025
white1234square24/03/202025
red4567square15/08/202035
purple5678round01/01/202115
gray5678round01/01/202115
white5678round01/01/202115
white2525square20/02/202110
black1111round15/07/20211
pink1111round15/07/20211
red1111round15/07/20211
white6598fizzy24/12/202265
green6969triangle30/01/202368
orange4201googly25/03/20238
yellow4201googly25/03/20238
white4201googly25/03/20238

 

If anyone could help, I would appreciate it.

Thank you in advanced

1 ACCEPTED SOLUTION
JPNK
Frequent Visitor

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

JPNK_1-1683877405536.png

 

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

JPNK_0-1683877367386.png

 

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

 

Thanks to anyone who read this request

View solution in original post

4 REPLIES 4
Einomi
Helper V
Helper V

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
Frequent Visitor

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
Frequent Visitor

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

JPNK_1-1683877405536.png

 

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

JPNK_0-1683877367386.png

 

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
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors