02-07-2019 11:37 AM
02-07-2019 11:37 AM
I have a large file of Excel data I’m attempting to transform to prepare for use as a radial flow map within GIS, and I was hoping to receive some advice on how to use either pivot tables or formulas to achieve this result. I have a list of creator IDs for projects as well as the project start dates and locations.
Essentially, the table looks something like this (I removed irrelevant columns and kept on the first 30 rows as a sample: the full file is about 70k rows long):
The column headers are largely self-explanatory (sequence is a numerical representation of the order in which the projects happened: "1"=1st, "2"=2nd, etc.). My hope is that a finished product could look something along the lines of this:
There would be only one founder_id per row; and t1 and t2 would contain the first and last location values for each founder_id (I don’t need help finding lat and long values; I"ll do that later). Is there a way for Excel to utilize either some complex variation on an IF formula, pivot table magic, or some other method to automate this process?
Alternatively, would there be a method to grab every location value for each founder_id and have the final table include t1, t2, t3, etc.? That might even be preferable.
Any advice on how to even get started with this would be much appreciated! Thanks.
02-07-2019 05:20 PMSolution
You could solve this using Power Query if that is an option for you. This is the final Product.
02-07-2019 07:22 PM
Oh nice, what you have there looks about perfect! I do appear to have access to the Power Query function, although I'm unfamiliar with it at this point in time. I've managed to follow your directions up to this point in time:
The bullet points listed direct me to "Select Pivot Column" and that the "Values Column Should = Location". I apologize if this is something fairly obvious I'm missing, but could you provide me with step-by-step directions on how to complete the operations from the point I posted until the end so I can create a table similar to the table you provided? I really appreciate your assistance!
02-08-2019 07:46 AM
Highlight sequence column, Go to Transform Tab, Select Pivot Column
Values Column = Location, Advanced options "Don't Aggregate"
Hope the snips help! : )