How can I combine a column with a row to generate new columns?

Question

I have data stored as an excel file taken from the U.S. Energy Information Administration. The below is a snippet of that data. I want to transfer this to a GIS-usable format. As such, I want to combine the data in the MSN column by code with the data in the year columns. Take the image below as the current data:

I want to have a resulting table that looks like this:

Edit: I've semi-solved this with just a pivot table. I created a pivot table with the MSN codes as Columns, the StateCodes as rows and the year data as a Sum value. If I was only manipulating data in excel, that'd be good enough. The MSN codes are an overall title with each yearly sum a column/title underneath that. Is there a way to combine the overall title with each individual column so that the output resembles the above second image?

Answer 1

Assuming I've read this correctly and you want another 4 columns to the right, but with combined values, like this:

Let's call the current columns A to F (as in the picture)

In cell G1 enter the formula =$B1&C1

Copy this across the next 3 columns, and the copy all 4 columns down to the bottom of the data

Answer 2

You should be able to do this with Power Query aka Get & Transform

with the cursor in the original table

• Data --> Get & Transform Data --> From Table/Range
• The Power Query Editor should open
  • Select the MSN and StateCode columns
  • Unpivot other columns

• Select the Attribute column and replace Y with _
• Merge the MSN and Attribute columns
• Sort the Merged column or the StateCode if you like

• Select the Merged column and Pivot, with the Values column being Value, and the aggregate function being Sum

  Close and Load