Getting a Pivot but not for numbers

Welcome to the forum!

I'm a little confused by your process. Is there a uniform rule for combining table data into updates? For the fruit, your update column contains one line for each row (lemon, orange), while for meat your update column contains one line for each column (beef, chicken). If each category has a separate rule in the way that it's fed into the output table, I don't think that a general pivot approach will suffice - you'll need to provide a specific list of categories and their associated rules.

The process is very antiquated, trying to fix but the data comes from a long manual chain so starting with what I can get.

I will have a list which I will need to maintain which will have all of the names like Lemon, orange, etc and it will say a few rules day zero being what group its in and if it is to do all or just the latest. I technically have multiple formats for all in my current monster version but the data just isnt clean enough so assume they are all like meat if they are all snapshots.

Something like below:

Component	Service	Version
Lemon	Fruit	Latest
Orange	Fruit	Latest
Sugar	Sugar	Latest
Meat	Meat	All

I think I might have chosen some bad examples for the dummy spreadsheet but basically a team has an ever growing list of things that come in to them that they need to put in individually, like the the lemon or the meat, the reference is usually a code for whatever it is but it could contain a long explaination. Then when things come to the end of the cycle which is were we get all this data we issue out a request for each group, the vast majority of groups will only have came from 1 line like the sugar in the example but most of the lines will fit into just a few groups, for example fruit goes on 1 request so we need it on 1 row as there is a lot more details for that row outside this process but in order to make sure they pick up eatch item the second row needs to break down the individual items and comment.

The way I have currently done it is that for every line it does a CONCATENATE version and a latest version through a long series of if's, both of which are not great if suddenly 10 more snapshots than expected come through

Then another table does a vlookup to pick whichever is relevent from the rules table and inserts the value but it still leaves a messy collection of data from the CONCATENATE and in a format which doesnt group them neatly without a copy paste job

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel
  
  you said
  
  Originally Posted by AJMAC88

  I tried the power pivott & power query for first time today, power query seemed potentially useful except the second column shows "table" and I couldnt work out how to then define how to get specific table info to show in its place

edit:
open PQ editor
see steps there (one by one)
edit steps to see how it was done