Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
silvias
Helper I
Helper I

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.

 

 

1 ACCEPTED SOLUTION
jppv20
Solution Sage
Solution Sage

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.

View solution in original post

3 REPLIES 3
jppv20
Solution Sage
Solution Sage

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
Super User
Super User

@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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.