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

 

[lrobbo314]

How about this?

Book2
	A	B	C	D	E	F
1	Order Number	Item	Cost	Expiration Date	Location	Owner
2	11111	Coke	$4.00	9/1/2023	Ohio	John Smith
3	11111	Ice	$0.50	10/1/2023	Ohio	John Smith
4	22222	Coke	$4.00	10/1/2023	Indiana	Dan Smith
5	33333	Ice	$0.50	10/1/2023	Kentucky	Sally Jones
6	33333	Veggie Burger	$6.00	10/3/2023	Kentucky	Sally Jones
7	33333	Beef Burger	$5.00	10/5/2025	Kentucky	Sally Jones
8
9	Order Number	Cost	Expiration Date	Location	Owner	Item Category
10	11111	$4.50	9/1/2023	Ohio	John Smith	Coke & Ice
11	22222	$4.00	10/1/2023	Indiana	Dan Smith	Coke & Ice
12	33333	$0.50	10/1/2023	Kentucky	Sally Jones	Ice Only
13	33333	$11.00	10/1/2023	Kentucky	Sally Jones	Burger
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Item_List = Table.AddColumn(Source, "Custom", (ro)=> Table.SelectRows(Table.SelectColumns(Source,{"Order Number","Item"}), (tbl)=> tbl[Order Number]=ro[Order Number])[Item]),
    Custom = Table.AddColumn(Item_List, "Item Category", each if [Item] = "Coke" or [Item] ="Ice" then if List.Contains([Custom],"Coke") and List.Contains([Custom],"Ice") then "Coke & Ice" else if List.Contains([Custom],"Coke") and not List.Contains([Custom],"Ice") then "Coke & Ice" else "Ice Only" else if Text.Contains([Item],"Burger") then "Burger" else [Item]),
    Group = Table.Group(Custom, {"Order Number", "Item Category"}, {{"Cost", each List.Sum([Cost]), type number}, {"Location", each [Location]{0}, type text},{"Owner", each [Owner]{0}, type text}}),
    Expiration = Table.AddColumn(Group, "Expiration Date", (ro)=> Table.SelectRows(Table.SelectColumns(Source,{"Order Number","Expiration Date"}), (tbl)=> tbl[Order Number]=ro[Order Number])[Expiration Date]{0}),
    Reorder = Table.ReorderColumns(Expiration,{"Order Number", "Cost", "Expiration Date", "Location", "Owner", "Item Category"}),
    Type = Table.TransformColumnTypes(Reorder,{{"Cost", Currency.Type}, {"Order Number", Int64.Type}, {"Expiration Date", type date}, {"Item Category", type text}})
in
    Type

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Power Query - Logic Categorize and Group by

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...

chandoo.org

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jdutle]

How about this?

Book2
	A	B	C	D	E	F
1	Order Number	Item	Cost	Expiration Date	Location	Owner
2	11111	Coke	$4.00	9/1/2023	Ohio	John Smith
3	11111	Ice	$0.50	10/1/2023	Ohio	John Smith
4	22222	Coke	$4.00	10/1/2023	Indiana	Dan Smith
5	33333	Ice	$0.50	10/1/2023	Kentucky	Sally Jones
6	33333	Veggie Burger	$6.00	10/3/2023	Kentucky	Sally Jones
7	33333	Beef Burger	$5.00	10/5/2025	Kentucky	Sally Jones
8
9	Order Number	Cost	Expiration Date	Location	Owner	Item Category
10	11111	$4.50	9/1/2023	Ohio	John Smith	Coke & Ice
11	22222	$4.00	10/1/2023	Indiana	Dan Smith	Coke & Ice
12	33333	$0.50	10/1/2023	Kentucky	Sally Jones	Ice Only
13	33333	$11.00	10/1/2023	Kentucky	Sally Jones	Burger
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Item_List = Table.AddColumn(Source, "Custom", (ro)=> Table.SelectRows(Table.SelectColumns(Source,{"Order Number","Item"}), (tbl)=> tbl[Order Number]=ro[Order Number])[Item]),
    Custom = Table.AddColumn(Item_List, "Item Category", each if [Item] = "Coke" or [Item] ="Ice" then if List.Contains([Custom],"Coke") and List.Contains([Custom],"Ice") then "Coke & Ice" else if List.Contains([Custom],"Coke") and not List.Contains([Custom],"Ice") then "Coke & Ice" else "Ice Only" else if Text.Contains([Item],"Burger") then "Burger" else [Item]),
    Group = Table.Group(Custom, {"Order Number", "Item Category"}, {{"Cost", each List.Sum([Cost]), type number}, {"Location", each [Location]{0}, type text},{"Owner", each [Owner]{0}, type text}}),
    Expiration = Table.AddColumn(Group, "Expiration Date", (ro)=> Table.SelectRows(Table.SelectColumns(Source,{"Order Number","Expiration Date"}), (tbl)=> tbl[Order Number]=ro[Order Number])[Expiration Date]{0}),
    Reorder = Table.ReorderColumns(Expiration,{"Order Number", "Cost", "Expiration Date", "Location", "Owner", "Item Category"}),
    Type = Table.TransformColumnTypes(Reorder,{{"Cost", Currency.Type}, {"Order Number", Int64.Type}, {"Expiration Date", type date}, {"Item Category", type text}})
in
    Type

Thank you! That looks like there the desired result. I am new to PQ, where do I paste this code?

 

[jdutle]

My apologies on cross posting. I also posted:
Microsoft Power BI Forum

 

[lrobbo314]

You can copy and paste this code in the Power Query Editor by going to the Home tab and clicking on the Advanced Editor.

 

[jdutle]

You can copy and paste this code in the Power Query Editor by going to the Home tab and clicking on the Advanced Editor.

That's fantastic! Thank you for your help!