How to convert multiple columns into rows with some cells being consistent

[VisualCPA]

I have a list of projects being funded by different departments. Some are funded by one department, others by multiple departments. The original list that I am given has the department funding to the right of the basic project information. I am having difficulty converting that lump of information into a list where the basic project information remains the same, but the department and funding amounts are separated into different multiple rows.

If it was one or two departments, I can manually make the changes, but I have thousands of projects with fifteen different departments.

I have attached a screenshot of the sample beginning data and what I am trying to accomplish below. Can anyone give any advice? I appreciate any help you can give.

[#2]

@VisualCPA 

Hello!

I think that you can do that with power query.

Attach is an excel file with the sample.

First create your information into a table, then go to data > get & transform data > from table/ range.

You need to select the columns year, project number, project name and total budget then go to transform > unpivot other columns.

[#3]

Sorry! I forgot to tell you. Power query is like a pivot table, every time you get new data you need to go to data > refresh all.