Stacking Columns in Power BI

[x35431]

As depicted in Table #1, I have two columns of color information, Color_1 and Color_2. I need to transform this data in Power BI by stacking Color_1 and Color_2 together to create a new column, Color_Stack (see Table #2). Can someone please help guide me in how this can be accomplished.

Table #1Table #2

[Manendra]

Hi @x35431 

You need to do this in the Power query editor. 

Step1: Click on Transform data (Power query editor will open)

Step2: Select the two colour columns (Color_1 & color_2)

Step3: Go to Transform Tab > select Unpivot columns

Now your columns will be transposed to one column.

Step4: Delete the attribute column

Step5: Select the Values colum and replace the blank values with null

Step6: Select the Values Column again and go to Transform Tab and select Fill Down.

Step7: Rename your Values Column to Color_Stack

Let me know if you need further help on this.

Cheers,

Manendra.

[x35431]

Thanks for the guidance. When the Color_1 and Color_2 columns both had colors listed, it worked. However, if only Color_1 has a color, all of the other columns in the data table become filled with "Error" cells. Any suggestions on how to remedy this problem?

[Manendra · Accepted solution]

@x35431 here is a quick video on how I implemented it. Make sure all your data types are correct before unpivoting.

https://www.mediafire.com/file/m7shua0kqw0aacs/Unpivot_columns.mp4/file

(this is an instant video link. It may expire over some time)

Let me know if you need any more help on this.

Cheers,

Manendra.

[amitchandak]

@x35431 , Unpivot the color columns in Power Query

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

Power BI: Power Query Functions one must know
: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s
Power BI - Cheat Sheet: Power Query Functions one must know
https://amitchandak.medium.com/cheat-sheet-power-query-functions-one-must-know-a8531b6e321a

[x35431]

Thanks for the guidance. When the Color_1 and Color_2 columns both had colors listed, it worked. However, if only Color_1 has a color, all of the other columns in the data table become filled with "Error" cells. How do I remedy this problem?