Forum Discussion

pointtoshare's avatar
pointtoshare
Frequent Visitor
9 years ago
Solved

Convert few columns into rows in an existing table

Hi, I have one table contains columns: A, B, C, D, E, F, G, H, I, J, K, L. I want to convert only E, F, G, H columns into rows . How can I go for it.   Thanks in advance.
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi pointtoshare,

     

    If we Unpivot these 4 columns: E,F,G,H, we will get below result.  The other columns are still kept as original.  From the image which is your expected output, I am confused why each record repeats for three times.

     

    To your second question"can I convert columns into rows in a calculated table", it is not available to pivot/unpivot a calculated table in query editor mode. However, to work around this issue, please try below DAX:

    New Table =
UNION (
    SELECTCOLUMNS (
        'Convert column',
        "A", 'Convert column'[A],
        "Col1", "E",
        "Col2", 'Convert column'[E]
    ),
    SELECTCOLUMNS (
        'Convert column',
        "A", 'Convert column'[A],
        "Col1", "F",
        "Col2", 'Convert column'[F]
    ),
    SELECTCOLUMNS (
        'Convert column',
        "A", 'Convert column'[A],
        "Col1", "G",
        "Col2", 'Convert column'[G]
    ),
    SELECTCOLUMNS (
        'Convert column',
        "A", 'Convert column'[A],
        "Col1", "H",
        "Col2", 'Convert column'[H]
    )
)

     

    Best regards,
    Yuliana Gu

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

In the Query Editor

1) Select the 4 columns E, F, G, H

2) Transform tab - Unpivot Columns

3) Rename new columns as necessary

pointtoshare's avatar
pointtoshare
Frequent Visitor
9 years ago

Hi Sean,

Thanks for your prompt reply.

The method you proposed is working but it breaks other columns setup as well. I want to keep other columns as it is. Is there any way to move the column E, F, G, H to another new table as table rows? Or, can I convert columns into rows in a calculated table?

 

Thanks.

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Post sample data in its original format and then desired output from that data.

    • pointtoshare's avatar
      pointtoshare
      Frequent Visitor
      9 years ago

      Hi Sean,

       

      Here is my sample data table:

      ABCDEFGHIJKL
      Category1223344123455667788
      Category2223344123455667788
      Category3223344123455667788

       

      And, here is my expected output:

      ACol NameCol Name
      Category1E1
      Category1E1
      Category1E1
      Category1F2
      Category1F2
      Category1F2
      Category1G3
      Category1G3
      Category1G3
      Category1H4
      Category1H4
      Category1H4
      Category2E1
      Category2E1
      Category2E1
      Category2F2
      Category2F2
      Category2F2
      ………  

       

      I want to convert only E, F, G, H Columns into Rows based on column A, keeping other columns as it is. Please let me know should you need any further clarifications.

       

      Thanks.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi pointtoshare,

         

        If we Unpivot these 4 columns: E,F,G,H, we will get below result.  The other columns are still kept as original.  From the image which is your expected output, I am confused why each record repeats for three times.

         

        To your second question"can I convert columns into rows in a calculated table", it is not available to pivot/unpivot a calculated table in query editor mode. However, to work around this issue, please try below DAX:

        New Table =
UNION (
    SELECTCOLUMNS (
        'Convert column',
        "A", 'Convert column'[A],
        "Col1", "E",
        "Col2", 'Convert column'[E]
    ),
    SELECTCOLUMNS (
        'Convert column',
        "A", 'Convert column'[A],
        "Col1", "F",
        "Col2", 'Convert column'[F]
    ),
    SELECTCOLUMNS (
        'Convert column',
        "A", 'Convert column'[A],
        "Col1", "G",
        "Col2", 'Convert column'[G]
    ),
    SELECTCOLUMNS (
        'Convert column',
        "A", 'Convert column'[A],
        "Col1", "H",
        "Col2", 'Convert column'[H]
    )
)

         

        Best regards,
        Yuliana Gu