Forum Discussion
Pivot/unpivot without aggregation gives error
I'm trying to figure out how to pivot my data on the Attribute column from long to wide format.
| Team | Attribute | Value |
| A | Score | 123 |
| A | Year | 1999 |
| B | Score | 156 |
| B | Year | 1999 |
| A | Score | 168 |
| A | Year | 2000 |
| B | Score | 198 |
| B | Year | 2000 |
So it looks like this:
| Team | Score | Year |
| A | 123 | 1999 |
| B | 156 | 1999 |
| A | 168 | 2000 |
| B | 198 | 2000 |
Obviously trying to pivot this way is giving me the "list" error because it's trying to reduce score and year to single value for each team. Is this even doable by using the transform interface alone?
Hi,
to obtain this i have added a new column
= List.Combine( {List.RemoveFirstN( Source[Value],1),{"null"}})
transposed it and then added to the original table (which has been stransposed)
Then transpose one more time and you get your result
The advantage is that is all dynamic
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
1 Reply
Hi,
to obtain this i have added a new column
= List.Combine( {List.RemoveFirstN( Source[Value],1),{"null"}})
transposed it and then added to the original table (which has been stransposed)
Then transpose one more time and you get your result
The advantage is that is all dynamic
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!