Forum Discussion
Separating data in a single column
I have data exported from a ticketing/retails system which lumps all the items purchased by each customer in one column, with values separated by commas. There's potentially 100 different items they could purchase. Most lines just have two or three items, others have 20-30 if they are bringing school groups, booking workshops at set times, lunch room access, etc.
I need to be able to analyse this data - so, for example, what secondary purchases did someone with ticket type 1 also purchase?
I can (and have) split the individual items into several columns, but what I'd really like is each ticket type in its own column to make filtering data much more straightforward.
I need to the process to be automatable, so I can import data every month and run the same analysis on it. I originally wanted to work with the full year's data, but with over 150k lines, that becomes unworkable when you add in the other data already in the spreadsheet.
Any idea of how I can do this? It feels like it might be a PowerQuery challenge, but I've got very little knowledge of how that works. Copilot seems to be balking at everything at the moment.
3 Replies
Trying to analyze secondary purchases by ticket type. I’ve split items into columns, but ideally want each ticket type in its own column for easier filtering. Needs to be automatable monthly—feels like a PowerQuery task, but I’m new to it.https://wattsbuilt.com/
hahaha
PQ indeed seems to be right tool for this. Can you share some example data, perhaps 20 rows of transactions and indicate how that should be summarised.
Save your file to OneDrive (or similar) and post a link that gives access to it here.
