Normalizing a web-based donation request input form

Power Query
1

The charitable foundation where I work uses a web-based portal for non-profits to request donations from us. There are a lot of questions and on the exported csv file, all the answers are spread on one row across numerous fields.

Here’s a simplified version of ONE row (field names separated by commas for this illustration):

program name,
amount requested,
date requested,
total amount already supplied by other funders,
amount already supplied by government contracts,
amt supplied by biggest govt contract,
name of biggest gov’t contract,
amt supplied by second biggest gov’t contract,
name of second gov’t contract,
amt supplied by third biggest gov’t contract,
name of third biggest gov’t contract,
amount already supplied by private donations,
amt supplied by biggest private donation,
name of biggest private donation,
amt supplied by second biggest private donation,
name of second private donation,
amt supplied by third biggest private donation,
name of third biggest private donation,
…and on and on thru a couple more categories,

then loops again thru a second program, amt & date with all of its rows. Geesh, what a nightmare!

I am struggling to create a normalized table out of this. I don’t seem to be able to unpivot like I would normally because of the multi-level attributes,

Here’s what I’d like to get:

Program name, amt requested, date, total amt already supplied, attribute, name, value

child abuse, $100000, 2/26/19, $20000, total go’vt, Total Gov’t, $5000
child abuse, $100000, 2/26/19, $20000, gov’t 1, DMH, $2500
child abuse, $100000, 2/26/19, $20000, gov’t 2, DHS, $2000
child abuse, $100000, 2/26/19, $20000, gov’t 3, Federal, $500
child abuse, $100000, 2/26/19, $20000, total private, Total Private, $50000
child abuse, $100000, 2/26/19, $20000, private 1, McKay Foundation, $25000
child abuse, $100000, 2/26/19, $20000, private 2, Ballmer Foundation, $15000
child abuse, $100000, 2/26/19, $20000, private 3, Julius Strauss Foundation, $10000
then begins a second program with all its own rows

Can anyone point me to an existing answer or help me find a way to produce a useful table? or give me a hint as to how to move forward?

2

Sounds like you just need to unpivot the data so that the table is long and thin?

You want all your answers basically to be in one column. This is how you want all your tables to be in Power BI.

Probably some images would be helpful if this doesn’t sort it for you.

3

Was hoping to be able to post a file, but instead, I have elaborated on my description. Really appreciate your help.

btw, you were the person who taught me how to unpivot in the first place!