For context the table I am trying to transform came from an excel file in which the data was not stored directly into a table but by filtering and removing unnecessary columns and rows I was able to the data that I need but I want to put it into a better format and I am fairly new to Power BI and I am not sure what steps to take.
I have a table in the following format:
| Column 1 | Column 2 | Column 3 | Column 4 |
|---|---|---|---|
| Category | Jan | Feb | Mar |
| Cat 1 | 100 | 200 | 300 |
| Cat 2 | 400 | 500 | 600 |
| Cat 3 | 700 | 800 | 900 |
And I want to get the table into the format below:
| Category | Month | Value |
|---|---|---|
| Cat 1 | Jan | 100 |
| Cat 1 | Feb | 200 |
| Cat 1 | Mar | 300 |
| Cat 2 | Jan | 400 |
| Cat 2 | Feb | 500 |
| Cat 2 | Mar | 600 |
| Cat 3 | Jan | 700 |
| Cat 3 | Feb | 800 |
| Cat 3 | Mar | 900 |
I tried transposing, pivoting, merging but I can’t see to get any closer to my desired output. I did try to search for some solutions but could not find anything relevant, any help or suggestions would greatly be appreciated.
>Solution :
In PQ, do the following.
Initial table.
Use first row as headers.
Select Category column and unpivot other columns.
