Forum Discussion

rcarmichael1990's avatar
rcarmichael1990
New Member
1 year ago
Solved

Removing Promotional Data From Sales

Good afternoon, I have a data set in PQ which comprises of a list of dates, product codes, the sales quantity, and a flag (where an item on promotion is denoted by a number greater than 0) which sho...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
    let
    // duration step
    one_day = #duration(1, 0, 0, 0),
    // function to generate list of dates and sales
    calc = (x, y, n) => 
        [daily_sales = (y{1} - x{1}) / n, 
        lst = List.Zip({List.Dates(x{0} + one_day, n - 1, one_day), List.Numbers(x{1} + daily_sales, n - 1, daily_sales)})][lst],
    // Source data - replac with yours
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tp = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    // filter out promo sales and get list from table
    nopromo_table = Table.SelectRows(tp, (x) => x[Promo Flag] = 0),
    nopromo = List.Buffer(Table.ToList(nopromo_table, (x) => {x{0}, x{2}})),
    // generate list of missing dates and sales 
    generate = List.Generate(
        () => [i = 0, s = nopromo{0}, n = 0, res = {}],
        (x) => x[i] < List.Count(nopromo),
        (x) => 
            [
                i = x[i] + 1, 
                s = nopromo{i}, 
                n = Duration.Days(s{0} - x[s]{0}), 
                res = if n <= 1 then {} else calc(x[s], s, n)
            ],
        (x) => x[res]
    ), 
    // generated list >> to table
    baseline_sales = Table.FromList(List.Combine(generate), (x) => x, {"Date", "Sale Qty"}),
    // combine and sort new sales table, fill down product column
    all = Table.Sort(nopromo_table & baseline_sales, "Date"), 
    fd = Table.FillDown(all, {"Product"})
in
    fd

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

rcarmichael1990 , 

Here’s a step-by-step guide:

Create a Calculated Column for Non-Promo Sales: First, create a calculated column that only includes sales quantities for non-promotion days. This will help in identifying the previous and next non-promotion sales quantities.
NonPromoSales = IF('Table'[Promo Flag] = 0, 'Table'[Sale Qty], BLANK())


Create a Calculated Column for Previous Non-Promo Sales: Create a calculated column to get the previous non-promotion sales quantity.

PreviousNonPromoSales =
VAR CurrentDate = 'Table'[Date]
RETURN
CALCULATE(
MAX('Table'[NonPromoSales]),
FILTER(
'Table',
'Table'[Date] < CurrentDate && NOT(ISBLANK('Table'[NonPromoSales]))
)

 

Create a Calculated Column for Next Non-Promo Sales: Create a calculated column to get the next non-promotion sales quantity.
NextNonPromoSales =
VAR CurrentDate = 'Table'[Date]
RETURN
CALCULATE(
MIN('Table'[NonPromoSales]),
FILTER(
'Table',
'Table'[Date] > CurrentDate && NOT(ISBLANK('Table'[NonPromoSales]))
)

 

Create a Calculated Column for Baseline Sales: Finally, create a calculated column that calculates the baseline sales by averaging the previous and next non-promotion sales quantities.
BaselineSales =
IF(
'Table'[Promo Flag] > 0,
DIVIDE('Table'[PreviousNonPromoSales] + 'Table'[NextNonPromoSales], 2),
'Table'[Sale Qty]
)

rcarmichael's avatar
rcarmichael
Frequent Visitor
1 year ago

Thank you for your reply!

Unfortunately as the code wasn't what Power Query accepts in the "Custom Column" dialogue, I was unable to proceed with your solution; 

ie RETURN, CALCULATE, MIN, & FILTER.