Forum Discussion

Rich_Webb's avatar
Rich_Webb
Frequent Visitor
3 years ago

Getting Single Product Sales from Mixed Product

Hi I work in a Brewery and we sell mixed cases of beer alongside single beers. I'm looking for a way to split the mixed cases and show a total sales number for the individual beers. In Power Query ...
Need Help
Rich_Webb's avatar
Rich_Webb
Frequent Visitor
3 years ago

If the category is "Bottles & Cans" it's always a single product, so 1 individual bottle or can of beer. A case of 12 identical beers is under the category "12 Pack".

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

Here is my proposal - as mentioned earlier I would expand the transaction data to go down to bottle level.  For case transactions I would explode the case contents into individual rows, for bottle transactions I would repeat the data in the row.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\2022-10-01 Example Bookkeepping Data.xlsx"), null, true),
    BookKeeping_2022_11_15_1802_Sheet = Source{[Item="BookKeeping_2022_11_15_1802",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(BookKeeping_2022_11_15_1802_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Quantity", Int64.Type}, {"Date/Time", type datetime}, {"Category", type text}, {"Product", type text}, {"Device Name", type text}, {"Location Name", type text}, {"Staff", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.From([#"Date/Time"]), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Items", each if [Category]="Bottles & Cans" then #table({"Product","Beer Name","Units Sold"},{{[Product],[Product],1}}) else let p = [Product] in Table.SelectRows(#"Case Mix",each [Product]=p)),
    #"Expanded Items" = Table.ExpandTableColumn(#"Added Custom1", "Items", {"Beer Name", "Units Sold"}, {"Beer Name", "Units Sold"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Items",{{"Units Sold", Int64.Type}})
in
    #"Changed Type1"

 

That will then give you the flexibility to look at various reporting scenarios.

 

Note that a new column is multiplying the transacton quantity with the detail quantity.

 

See attached for the pbix. I added a couple of dimension tables - they are not mandatory but may help with cutting the data a cetain way.

  • Rich_Webb's avatar
    Rich_Webb
    Frequent Visitor
    3 years ago

    That's great. It looks like you've solved it but I can't figure out how you exploded the mixed cases in the transactions. I've tried copying your steps but it's not working for me.
    Any chance you could give me some advice on exploding cases?

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

      I love the innuendo this topic produces!  I am also concerned about  getting thirsty...

       

      Anyway - here is the main piece:

       

      #"Added Custom1"
  = Table.AddColumn(
    #"Added Custom", 
    "Items", 
    each 
      if [Category] = "Bottles & Cans" then
        #table({"Product", "Beer Name", "Units Sold"}, {{[Product], [Product], 1}})
      else
        let
          p = [Product]
        in
          Table.SelectRows(#"Case Mix", each [Product] = p)
  )

       

      If the category is "Bottles & Cans"  then I create a fake table that lists the beer name both as the product and the beer name, and sets the quantity to 1.  However, if the category is different then I am doing a lookup on the Case Mix table based on the Product name.  That will pull in all the rows for that case, including the participating beers, and their quantity.

       

      The next step then brings that into a usable format by expanding to new rows where needed.