Forum Discussion
Transposing Data
Hello!
I have a data set that has an general ledger code that is vertical and an entity code that is horizontal. The table will have an amount that relates to a certain general ledger account for the certain entity. I need to create a list that goes horizontal that shows the general ledger account, the entity code and the amount in a horizontal list. I have attached an example below.
This is how the data is currently:
| Entity number | |||
| General ledger account | 1 | 2 | 3 |
| 100 | $1 | $0 | $2 |
| 101 | $4 | $0 | $1 |
| 102 | $1 | $1 | $1 |
I need the data to be in list form like below:
| General ledger account | Entity | Amount |
| 100 | 1 | $1 |
| 100 | 2 | $0 |
| 100 | 3 | $2 |
| 101 | 1 | $4 |
| 101 | 2 | $0 |
| 101 | 3 | $1 |
| 102 | 1 | $1 |
| 102 | 2 | $1 |
| 102 | 3 | $1 |
I have a large dataset and cannot transpose everything invididually.
Thank you!!
1 Reply
- You can do this by unpivoting with PowerQuery.
https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098
