How to Transform Data in Excel so that 2 rows can be separated?

I have a dataset in Excel where month data is stated in rows along with ticket types, making it difficult to analyze. Here's a sample of what my data looks like:

Screen shot of my dataset, it can scroll more to the right until DEC

I need to transform this data so that month has its own columns. For example, I want each value to have its own month. Also, if possible, I want for example "NTT Creation" its own column (the same way goes for other ticket types ), and then another column "Month" beside it so I know this ticket type is for which month. The rest of the data seem already alright to me.

How can I achieve this transformation using Power Query or any other method in Excel?

I appreciate any suggestions or guidance on how to do this.

Thank you!

I tried several methods to transform the data:

• Transposing the data: This partially helped but did not achieve the desired structure since the months were still not aligned correctly.
• Pivoting the data: I attempted to use a pivot table, but I struggled to get the months to appear as separate columns.
• Filling in manually: This was very time-consuming and prone to errors.

As a newbie data analyst intern, I am looking for a more efficient and accurate method to transform this data. I am expecting a solution that will restructure my data so each ticket type value has its month.

This solution was documented online on onlinetraininghub. I used images from the website and will detail the process here and provide the link at the bottom for more details and scenarios on how to resolve this specific issue.

First, format the data as a table with Ctrl + T but uncheck the box for "My table has headers"

Next, load the data to power query from the data tab -> from table. This will open the power query editor window.

Next, Transpose the table in the "Transform" tab. Then, fill down the month labels by selecting the first column (which was previously the first row) and selecting "Fill down" from the transform tab. This will repeat the names of each of the months for each row.

Then, merge the first two columns. Since your month is only 3 characters you don't need a delimiter to separate them, later we'll extract these characters.

Then, transpose the table back to it's original layout and "Use the first row as Headers"

Next, unpivot the data columns. You can do this by highlighting all the data columns right-clicking and hitting "unpivot columns"

Then, split the new "Attribute" column by the number of characters (3) "Once, as far left as possible".

Then, Pivot the Attribute.2 & value columns. This will put them into separate columns. You're probably looking for "Sum" at this stage for the advanced options.

Lastly, rename the columns to the value fields you're using and "close & load"

For more information on this look at the nested column section of this site: Unpivot nested columns