Transpose only two row

[silvias]

HI,

I have a table like the following:

ID	CARACT	VALUE
a1	caract1	value1
a1	caract2	value2
a2	caract1	value3
a2	caract2	value4

I would like to transform it into:

ID	caract1	caract2
a1	value1	value2
a2	value3	value4

I mean, the ID column would look exactly the same, but the column carac is replaced by the columns that used to be the rows, obtaining the values of the column value.

I tried to transpose but I also transposed the ID column and they appear wrong.

How can I do this transormation?

Thank you very much.

[jppv20 · Accepted solution]

Hi @silvias ,

Go to PowerQuery, select the column Caract and choose Pivot Column.

Select Value as values column and in the advanced options choose Don't aggregate.

If I answered your question, please mark it as a solution to help other members find it more quickly.

[smpa01]

@silvias  try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjRU0lFKTixKTC4BscoSc0pTDZVidVBkjGAyRhAZI0w9xugycD0mSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, CARACT = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"CARACT", type text}, {"VALUE", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[CARACT]), "CARACT", "VALUE")
in
    #"Pivoted Column"

[Anonymous]

@silvias In PowerQuery select column 'CARACT' and choose 'Pivot Column' on Transform Tab.