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

Occasional Visitor

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.


Screenshot 2022-01-09 224731.png


2 Replies



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.


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.