Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Stacking Columns in Power BI

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

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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?

  • Manendra's avatar
    Manendra
    Advocate I
    3 years ago

    Hi Anonymous 

     

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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?