Power Query for mail out
I need to transform the way in which some data is grouped in columns.
The source spreadsheet contains a series of records describing investment proposals.
Each record contains: descriptive title, name of potential investor, institution of investor, email address of investor, name of potential investor, institution of investor, email address of investor, name of potential investor, institution of investor, email address of investor. So each proposal is associated with a series of potential investors (typically 6 in total).
I wish to transform the data such that I have one output spreadsheet.
Each record in the output spreadsheet contains: descriptive title of proposal, name of potential investor, institution of investor, email address of investor.
So if there was only one investment proposal there would likely be 6 records in the output file. In actuality we are likely to be looking at circa 50 proposals and therefore around 300 potential investors to contact.
This output spreadsheet is used to make approaches to potential investors e.g. via some sort of mail out (mail merge).
Having done a little bit of investigation I believe the best way to do this might be via Power Query. Any guidance would be appreciated. Thanks.
RE: Power Query for mail out
Hi Matthew,
Thank you for the forum question,
Yes, Power Query is an excellent solution for this. What you have is a classic case of converting a wide table into a long (normalised) table using Unpivot. Power Query is specifically designed for this type of transformation. The general approach is covered in Power Query training materials that describe using Power Query to reshape and transform data structures.
Example Source Data
Proposal Investor1 Name Investor1 Institution Investor1 Email Investor2 Name Investor2 Institution Investor2 Email
Proposal A John Smith ABC Fund john@abc.com
Jane Brown XYZ Capital jane@xyz.com
Desired Output
Proposal Investor Name Institution Email
Proposal A John Smith ABC Fund john@abc.com
Proposal A Jane Brown XYZ Capital jane@xyz.com
________________________________________
Power Query Method
1. Load the data into Power Query
• Select the source table.
• Data → From Table/Range.
2. Keep the Proposal column
Select the proposal/title column(s) only.
3. Unpivot the Investor Columns
• Select the Proposal column(s).
• Transform → Unpivot Other Columns.
You will get something like:
Proposal Attribute Value
Proposal A Investor1 Name John Smith
Proposal A Investor1 Institution ABC Fund
Proposal A Investor1 Email john@abc.com
Proposal A Investor2 Name Jane Brown
4. Split the Attribute column
The Attribute values contain both:
• Investor number (Investor1, Investor2, etc.)
• Field type (Name, Institution, Email)
Split the Attribute column into:
• InvestorID
• FieldType
For example:
Proposal InvestorID FieldType Value
Proposal A Investor1 Name John Smith
Proposal A Investor1 Institution ABC Fund
Proposal A Investor1 Email john@abc.com
5. Pivot the FieldType column
Select FieldType and choose:
• Transform → Pivot Column
• Values Column = Value
Result:
Proposal InvestorID Name Institution Email
Proposal A Investor1 John Smith ABC Fund john@abc.com
Proposal A Investor2 Jane Brown XYZ Capital jane@xyz.com
6. Remove InvestorID
If not required for the mail merge, simply remove it.
Final output:
Proposal Name Institution Email
Proposal A John Smith ABC Fund john@abc.com
Proposal A Jane Brown XYZ Capital jane@xyz.com
________________________________________
Benefits
• Fully automated for 50, 500 or 5,000 proposals.
• Refreshable whenever a new spreadsheet is received.
• Produces an ideal structure for mail merge, Outlook mail-outs, CRM imports, or marketing tools.
• No VBA required.
If the investor fields follow a consistent naming pattern (e.g., Investor1 Name, Investor1 Institution, Investor1 Email, Investor2 Name, etc.), the entire process can be built once and refreshed with a single click.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
