Pivot and Group data
Pivot the data
1. Import data
2. Check which column contains the column names
3. Check which column contains the values
4. Click Transform Data
5. Click the Transform tab, then select the column containing the new column names
6. Click Pivot Column
7. Select the column containing the values
8. Expand Advanced Options, then select Don't Aggregate. Click OK
9. While still using the pivoted table, select the Home tab, then Merge Queries as New
10. Select the columns containing the primary key that links the two tables. Click OK
11. Expand the merged table to show the new items (this is normally the end)
Group the data (to remove null values)
1. If there are duplicate primary keys (or duplicates in multiple columns) and lots of null values then you need to group the data
2. Click Transform Data
3. Highlight all the columns that contain duplicate values (i.e., primary keys, address)
4. Click the Transform tab, then select Group By
5. Click Advanced
6. For each column that contains nulls:
a. New column name: the existing column name
b. Operation: Max (so it selects the duplicate row with the data and not the row(s) with the null)
c. Column: select the column to aggregate
7. Click Add aggregation and repeat for each column that contains null values
8. Click OK