Adding a category to each product based on the groups title

This can be done with PowerQuery. Here's the M code:

Please Login or Register  to view this content.

Workbook attached - let me know if you woudl like to learn how to do it.

Yes, of course. Just give me a few minutes.

OK - here's what you need to do:

1. In the original workbook (not my copy) select the whole of your source table (B1 to K377).
2. With this range selected, go to the Data ribbon and click on From Table/Range in the Get & Transform area of the ribbon (on the left). This will load the table into PowerQuery.
3. In the PQ editor that is now open, click on Advanced Editor in the ribbon (near the left).
4. In the dialog that opens, replace all the code that appears with the code that I gave you above, then click on Done (bottom right).
5. Finally, click on Close & Load at the far left of the PQ ribbon.

All you need to do now is remember to refresh the table when changes are made to the source table (Refresh on the Data ribbon). This can be automated by setting auto-refresh in the query properties. To access the query properties, click Queries & Connections on the Data ribbon, then right-click the query in the pane that appears to the right and choose Properties.

If you just want to use this as a one-off to get your data into a shape you are happy with going forward, then there is no need to worry about refreshing the data.

You can step through the stages set up in the query to see what was done to reshape the data.

Well, yes - the workbook you are using is not the same as the one you shared here.

Does the M code work if you change cell B1 to 'Personal Computers - Computer Desktop' before loading the table into PQ? If not, then the workbook is significantly different to the one I created the M code with.

You should NOT select 'Table has headers'.

I will have a look at the new file.

It works perfectly here.

Things to check:

1. You selected the correct range (starting at B1, NOT A1).
2. You deselected 'My table has headers' )sorry - I should have mentioned this.
3. You copied and pasted my code OVER the code in the advanced window, NOT after it.

Glad to hear it's working.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Once bitten with PQ, and you'll be smitten. It's really useful - I use it every day in my job for data transformation (normalisation).

You, too. Sláinte!

I have no Irish ancestry according to my My Heritage DNA results: quite a lot of Scandinavian, though. Like my Viking ancestors, I'm a marauder: in my case, it's in the hunt of all things linguistic.

In what way? You can save any Excel file to .csv.