Power Query - Logic Categorize and Group by

[jdutle]

Hello. I am new to using Power Query and have gotten stuck on something and am looking for some help.

In a data set I would like to categorize the values in one of the fields using some kind of If/Then/And/Or logic. I can figure out the logic if I was using an Excel formula, but I am struggling to transfer to Power Query. After categorization, I want group the data by the new Category field and an Order Number. Below is was I am trying to accomplish. Its a made up situation, so the scenario might seem a little goofy.

Thank you in advance for any help you can provide.

1. Categorize - If under the same Order Number create the following values in a Item Category Field:

• "Coke" and "Ice" appear, I want to categorize to "Coke & Ice"
• "Ice" only appears, I want to categorize to "Ice Only"
• "Coke" only appears, I want to categorize to "Coke & Ice"
• "Veggie Burger" or "Beef Burger", I want to categorize to "Burger"

2. Group - If the same Order Number and Category combine the records
Item field is no longer needed

• Cost should be summed if same Order Number and Category
• Expiration Date: earliest date should be used for the grouping
• Location and Owner: should have the same data for the grouping, so only one value needs to be retained

[mussaenda · Accepted solution]

[jdutle]

That works perfectly. Thank you!