Forum Discussion
Convert single column to row
Hi.
I have table as follow, want to convert the "Account" as row. What is the best way to transform this? Thanks.
Current:
| Site # | Year | Month | Account | Value |
| Site 1 | 2019 | January | Count1 | 100 |
| Site 2 | 2019 | January | Count2 | 200 |
| Site 3 | 2019 | January | Count3 | 300 |
| Site 1 | 2019 | February | Count1 | 100 |
| Site 2 | 2019 | February | Count2 | 200 |
| Site 3 | 2019 | February | Count3 | 300 |
| Site 1 | 2020 | January | Count1 | 100 |
| Site 2 | 2020 | January | Count2 | 200 |
| Site 3 | 2020 | January | Count3 | 300 |
Post:
| Site # | Year | Month | Count1 | Count2 | Count3 |
| Site 1 | 2019 | January | 100 | 200 | 300 |
| Site 1 | 2019 | February | 100 | 200 | 300 |
| Site 1 | 2020 | January | 100 | 200 | 300 |
Hi Tevon713 ,
I have not gotten to the post result you wished for, but here somethinng that maybe gets you closer to a solution:
What I did was that I pivoted on the Account column using the Value column as value. To do this, mark the Account column and click on Pivot column in the transform section:
What puzzles me is how you would want to return 200 for Site 1 in the Column Count2, since in your raw data Site 1 never has the attribute Count2. Same thing for Count3...
Hope this helps you anyway 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
2 Replies
Hi Tevon713 ,
I have not gotten to the post result you wished for, but here somethinng that maybe gets you closer to a solution:
What I did was that I pivoted on the Account column using the Value column as value. To do this, mark the Account column and click on Pivot column in the transform section:
What puzzles me is how you would want to return 200 for Site 1 in the Column Count2, since in your raw data Site 1 never has the attribute Count2. Same thing for Count3...
Hope this helps you anyway 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi tackytechtom.,
Thanks for your help. Sorry I typed up too quick. The result you showed is correct.
