Forum Discussion

silvias's avatar
silvias
Helper I
4 years ago
Solved

Transpose only two row

HI,

I have a table like the following:

 

IDCARACTVALUE
a1caract1value1
a1caract2value2
a2caract1value3
a2caract2value4

 

I would like to transform it into:

IDcaract1caract2
a1value1value2
a2value3value4

 

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.

 

 

Need Help
  • jppv20's avatar
    jppv20
    4 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • jppv20's avatar
    jppv20
    Solution Sage
    4 years ago

    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's avatar
    smpa01
    Community Champion
    4 years ago

    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"