Hello!
I have an Excel file that require me to do the following:
1. By Order Number, group the categories into product packages (based on mappings in product package groupings into package code), and then by pricepoint groupings (any order numbers that fall into the product package groupings, then mapped by pricepoint).
I'm currently doing this manually by filtering, grouping, copying, pasting. But am hoping there might please be an automated method via Power Query, Power Pivot, or even formulae, to speed up the process?
Please see attached example file.
Appreciate any assistance you can please provide, to automate this process.
Hi Danielle,
Please upload the file again. Be sure to click the 'Start Upload' button after selecting your file.
Mynda
Thank-you.
Hi Danielle,
It's great to see you're using the new XLOOKUP function. XLOOKUP has it's own error handling built in, so you don't need to use IFERROR anymore. e.g. you could change the formula in column J of the "New Raw Data with groupings" sheet to this:
=XLOOKUP(A3,Workings!A:A,Workings!B:B,"")
Anyhow, you can use Power Query to automate this process, see file attached. I recommend you complete the Power Query course so that you can follow the steps I've taken.
There are 3 queries in the file:
PackageCodes - this query assigns the codes to each order.
PricepointCodes - this query assigns the pricepoints to each order.
GroupedData - this is your raw data with the package code and pricepoint codes assigned.
Notes:
1. your raw data has many rows with blanks for quantity and extended amount. If you don't remove these from the dataset the query will assume the order includes the categories listed on those rows. e.g. Order number 267440 has 3 blank rows for categories Jersey, Gingham and Linen. These categories are included for the purpose of assigning package codes. If they shouldn't be included then you can add a step in the PackageCodes query straight after the 'Changed Type' step. You'll also need to do this in the PricepointCodes query.
2. In the PackageCodes query I've only written some of the rules in the 'if' formula for the codes - see 'Added Custom' step. You need to edit this step and add the 'if' rules for the remaining categories. You should complete the rules with the most categories first so they are assigned correctly. This is because once a rule in the 'if' formula evaluates to TRUE, the if stops evaluating.
I hope that gets you started.
Mynda
Awesome. Thank-you so much, Mynda.
This definitely helps! Very much appreciate your assistance.
