Group By to roll up line items of order

[egspen2]

I have a data set with one line for each product ordered on a sales order. For example, if only one item was ordered on Order #12345, there is one line in the data set. If 10 items were ordered on Order #12346, there are 10 lines in the data set.

I am trying to use Power Query to "roll up" or summarize the orders based on whether or not the order includes my top-seller which I've called "Test 1" in this example. For any order that includes Test 1 as at least one of the order choices, I want to designate that Order # as a "Test 1 order" (regardless of how many other order types are on that order). Similarly, if an order does not have a Test 1 order choice, it should be considered a "non-Test 1 order".

I am ultimately trying to see what is my average order value for order choices that include a Test 1 versus those that do not. I was thinking Group By in PQ would be the solution here but having trouble getting the desired output.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Order #"}, {{"All", each _}}),
    AddCustom = Table.AddColumn(Group, "Category", each if List.Contains([All][Product], "Test 1") then "Test 1" else "Non-Test 1"),
    AddCustom1 = Table.AddColumn(AddCustom, "Custom.1", each List.Sum([All][#"$ Value"])),
    Group1 = Table.Group(AddCustom1, {"Category"}, {
        {"Total $", each List.Sum([Custom.1]), type number}, 
        {"Quantity", each Table.RowCount(_), Int64.Type}, 
        {"$ per qty", each List.Sum([Custom.1])/Table.RowCount(_), type number}})
in
    Group1

 

[egspen2]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Order #"}, {{"All", each _}}),
    AddCustom = Table.AddColumn(Group, "Category", each if List.Contains([All][Product], "Test 1") then "Test 1" else "Non-Test 1"),
    AddCustom1 = Table.AddColumn(AddCustom, "Custom.1", each List.Sum([All][#"$ Value"])),
    Group1 = Table.Group(AddCustom1, {"Category"}, {
        {"Total $", each List.Sum([Custom.1]), type number},
        {"Quantity", each Table.RowCount(_), Int64.Type},
        {"$ per qty", each List.Sum([Custom.1])/Table.RowCount(_), type number}})
in
    Group1

Thank you. I am not too experienced in using the M or DAX language for solutions at this point in my PQ journey. A few questions:

1) Does the code above represent steps that I could follow using transformations from the GUI / ribbons?
2) If no, what steps do I use to copy the code and apply it to my data to get the desired result?
3) Which portion of the code you provided is the part that rolls the order ID's up based on whether they include / don't include a Test 1 order?

 

[JGordon11]

Create a blank query.
Go to View, Advanced Editor.
Delete all the code found there.
Paste in the code from above.
Change the Table1 found in the Source step to the name of your table.

 

[egspen2]

What are the steps to just do the part to add a column designating a order as "Test 1" or "Non-Test 1" based on whether or not the Test 1 order choice is present?