• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query - Logic Categorize and Group by

jdutle

New Member
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
Example.png
 

Attachments

  • Categorize Example.xlsx
    19.4 KB · Views: 3
One way

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Auto_col_types = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"Item", type text}, {"Cost", type number}, {"Expiration Date", type date}, {"Location", type text}, {"Owner", type text}}),
    Self_join = Table.NestedJoin(Auto_col_types, {"Order Number"}, Auto_col_types, {"Order Number"}, "Auto_col_types", JoinKind.LeftOuter),
    Add_Category = Table.AddColumn(Self_join, "Item Category", each if List.ContainsAll([Auto_col_types][Item], {"Coke", "Ice"}) then "Coke & Ice"
else if List.Contains([Auto_col_types][Item], "Ice")  and [Item] = "Ice" then "Ice Only"
else if List.ContainsAny([Auto_col_types][Item], {"Beef Burger", "Veggie Burger"}) then "Burger"
else if List.Contains([Auto_col_types][Item], "Coke")  then "Coke & Ice" else
"undefined"),
    Reme_col_self_join = Table.RemoveColumns(Add_Category,{"Auto_col_types"}),
    #"Grouped Rows" = Table.Group(Reme_col_self_join, {"Order Number", "Item Category"}, {{"Cost", each List.Sum([Cost]), type nullable number}, {"Expiration Date", each List.Min([Expiration Date]), type nullable date}, {"Location", each List.Min([Location]), type nullable text}, {"Owner", each List.Min([Owner]), type nullable text}})
in
    #"Grouped Rows"
 

Attachments

  • Categorize Example.xlsx
    22.3 KB · Views: 4
One way

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Auto_col_types = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"Item", type text}, {"Cost", type number}, {"Expiration Date", type date}, {"Location", type text}, {"Owner", type text}}),
    Self_join = Table.NestedJoin(Auto_col_types, {"Order Number"}, Auto_col_types, {"Order Number"}, "Auto_col_types", JoinKind.LeftOuter),
    Add_Category = Table.AddColumn(Self_join, "Item Category", each if List.ContainsAll([Auto_col_types][Item], {"Coke", "Ice"}) then "Coke & Ice"
else if List.Contains([Auto_col_types][Item], "Ice")  and [Item] = "Ice" then "Ice Only"
else if List.ContainsAny([Auto_col_types][Item], {"Beef Burger", "Veggie Burger"}) then "Burger"
else if List.Contains([Auto_col_types][Item], "Coke")  then "Coke & Ice" else
"undefined"),
    Reme_col_self_join = Table.RemoveColumns(Add_Category,{"Auto_col_types"}),
    #"Grouped Rows" = Table.Group(Reme_col_self_join, {"Order Number", "Item Category"}, {{"Cost", each List.Sum([Cost]), type nullable number}, {"Expiration Date", each List.Min([Expiration Date]), type nullable date}, {"Location", each List.Min([Location]), type nullable text}, {"Owner", each List.Min([Owner]), type nullable text}})
in
    #"Grouped Rows"
Thats great! Thank you for you help!
 
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Back
Top