Tens of thousands of rows of data.
Fields are voterID, Date-1, Date-2, Date-3, Date-4 and Date-5
Rows are sorted by voterID. There are between 1 and 5 rows with the same voter ID.
Each row has a value in one date field only. So, a common pattern for a voter that voted in 5 elections is:
voterID Date-1 Date-2 Date-3 Date-4 Date-5
voter01 null voter01 null null null
voter01 voter01 null null null null
voter01 null null null voter01 null
voter01 null null null null voter01
How can I merge all rows to?:
voter01 voter01 voter01 null voter01 voter01
I don't need to keep the first column. I just need to know when voters voted by date.
What is the best way to approach this data shaping? Power Query? Pivot Table? Power Pivot? Impossible?
Thank you for any insights you share.
Hi Francis,
Perhaps the PQ example in the attached file will help you on your way. It's a straight-forward Unpivot, Sort and Pivot scenario.
Riny
Thank you for your reply, it was very helpful. The result is precisely what I need.
I used the steps to Unpivot. (Found online.) Unfortunately, they didn't work for me. I'm using the latest version of Excel from Office 365. Do you mind briefly describing the procedures you used to obtain the second table?
1. Put your data into an Excel Table
To put your data into a table, click any cell in the dataset and go to the "Insert" tab in the top toolbar. Under the "Tables" section, select "Table." A box appears labeled "Create Table." In this box, verify that the cell range is correct and edit as needed. If not already checked, add a check mark to the "My table has headers" box, if you have column headings, and click the "OK" button. This converts any of your tabular data in the range you selected into an Excel table.
2. Get and transform
Once your data is in an Excel table, you can transform the data to unpivot. Start by clicking any data in a cell within a table. Click on the "Data" tab in the top toolbar. Find the "Get & Transform Data" section and select "From Table / Range." A "Create Table" box opens where you can click "OK." This opens a query editor to unpivot your data.
3. Unpivot your data
In the "Query Editor," right-click on the first column and click "Unpivot Other Columns." This unpivots the data in your other columns within your Excel table. Consider changing the names of the new columns to better represent the data, if you prefer.
When you look at the query in my file you should be able to follow the steps I took.
Not sure what source exactly you found on-line but I just created the blue table first. Then I connected to it with PQ and then applied three basic steps.
Unpivot other columns, Sort and Pivot. The last step is just to remove the first column. Then Close and Load to a table. So what exactly isn't clear?
Sorry, I'm too much of a newbie at this.
I don't know which column to sort and how, and I don't know how to create a Pivot table. I've tried several different combinations to no avail. I even tried the Pivot tables recommended by Excel, but none are what I want.
I'm just not able to duplicate your final table, though it is exactly what I want.
Hi Francis,
I understand this might be difficult as you don't know how to create a pivot table, but there's no substitute for learning. So I'd recommend you fill the gaps in your knowledge through our courses, otherwise you'll always struggle to do things like this.
Pivot Table Quick Start Course
You may also find these articles useful
Regards
Phil
