I have a table that was set up in a “wide” format. It tracks quarterly and annual reports from organizations. The person who set it up used the organization as the key, and then has Attachment-class columns for each quarter, Q1-2020, Q2-2020, etc., with the relevant report in each field.
I’d like to convert this table to a “long” format one row per organization per quarter, with a single Attachment-class column. I’m used to calling this operation “pivoting” or “reshaping” the table, but I can’t seem to get the Pivot Table App to do anything useful for this goal.
Currently, there are about 16 organizations and 12 quarter columns, so it’s doable by hand, but I’d much prefer an automated solution. (And there are a couple other wide tables I’d like to convert to long format as well). Is there a good way to do this?
# Current table fields:
Org (text), Q1-2019 (attachment), Q2-2019 (attachment), Q3-2019 (attachment)...
# Desired table:
ID (concatenate Org and Quarter), Org (text), Quarter (date), Report (attachment)