Converting a table into a more comprehensive format in Power BI

Question

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 seem 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.

Answer 1

In PQ, do the following.

Initial table.

Use first row as headers.

Select Category column and unpivot other columns.