Forum Discussion
How to convert multiple columns into rows with some cells being consistent
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.
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.
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.
- 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.
alannavarro ; way cool. This was exactly what I needed. You saved me hours of agony.
