Auto and Manual Categories

Whoa, we are getting so close 😊😁. I have managed to replicate this into my real data set. However, I struggled in 2 points.

You have 2 queries the statement one and the auto one, and I will use the same names to describe my steps and please tell me where I missed something.

1. Open blank workbook > Get Data > From SharePoint folder

2. Do all my transformations and add a unique column by merging a few ones (like you did with [ref])

3. Load back this query to Excel, add on that table the columns Category and Subcategory

4. Load back this query to PQ
5. Load my mapping (the one you called auto) table to PQ

So I am ending up having three queries

statement (the one I got from SP and loaded to Excel)

auto (my mapping table loaded to PQ)

statement (2) (the one loaded from Excel with the two new columns Category and Subcategory) 

Then I am stuck because in your case, your original query, also known as statement was loaded as a connection but if do the same, I will have an issue with the auto query at the step loaded_transactions, so how do we overcome this ?

Also is there a way to remove the [ref] column at the final step of the auto query ? I have tried but it messed ? Alternatively, can we do a merge query instead of creating a [ref] column ?

Really appreciate your help.