Convert few columns into rows in an existing table

[pointtoshare]

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.

[Anonymous]

Your solution works perfectly, but how do I apply a filter using this solution

[Sean]

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]

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]

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

[pointtoshare]

Hi Sean,

Here is my sample data table:

A	B	C	D	E	F	G	H	I	J	K	L
Category1	22	33	44	1	2	3	4	55	66	77	88
Category2	22	33	44	1	2	3	4	55	66	77	88
Category3	22	33	44	1	2	3	4	55	66	77	88

And, here is my expected output:

A	Col Name	Col Name
Category1	E	1
Category1	E	1
Category1	E	1
Category1	F	2
Category1	F	2
Category1	F	2
Category1	G	3
Category1	G	3
Category1	G	3
Category1	H	4
Category1	H	4
Category1	H	4
Category2	E	1
Category2	E	1
Category2	E	1
Category2	F	2
Category2	F	2
Category2	F	2
………

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 · Accepted solution]

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

[pointtoshare]

Hi @v-yulgu-msft,

Thanks for your support. If I unpivot 4 columns other columns will remain same but the rows will be splitted based on the mentioned 4 columns value that's what I don't want to be. I want other columns value as a consolidated one the reason why I wan to unpivot these 4 columns in a separate table. Each record is not repeating three times, three 1s are different three values, I kept it same to mean it as a group.

Thanks for the DAX expression, I will try it and get back to you if it doesn't serve my purpose.