none
Count Weeks Based on Set Criteria RRS feed

  • Question

  • Hi all,

    I have the following scenario with which I need help in Power Query: multiple Customers with multiple Products and multiple Promotions. Each Product is being sold to a Customer on Promotion.

    The period when this happens is represented in the 3rd column. (WeekNum function that represents the calendar week).

    What I need is to be able to identify the 1st, 2nd, 3rd (and so on) week for each combination of Customer - Product. Rule: If a promotion for a Customer-Product stops for 3 or more weeks, then when it resumes we re-start counting from 1.

    I forgot to mention that the Year should also be considered. (basically I have another column in my original data with the year)

    For example if we are in 2016 week 52 and that is week promo 10, then 2017 week 1 will be week promo 11.

    Please let me mention that I'm seeking a solution using Power Query.

      



    Monday, July 18, 2016 7:14 PM

Answers

  • Yes, you need to apply a very different technique in Power Query if you want to avoid recursion (which is slow):

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        AddIndex = Table.Buffer(Table.AddIndexColumn(Source, "Index", 1, 1)),
        DifferenceToPreviousRow = Table.AddColumn(AddIndex, "Custom", each [PromoWeek]-AddIndex[PromoWeek]{[Index]-2}),
        #"Replaced Errors" = Table.ReplaceErrorValues(DifferenceToPreviousRow, {{"Custom", 10}}),
        PrepareGrouping = Table.AddColumn(#"Replaced Errors", "Custom.1", each if [Custom]<=3then null else [Index]),
        NewGroups = Table.FillDown(PrepareGrouping,{"Custom.1"}),
        GroupWithNestedCountIndex = Table.Group(NewGroups, {"Customer", "Product", "Custom.1"}, {{"NestedIndex", each Table.AddIndexColumn(_, "Count", 1,1), type table}}),
        #"Expanded NestedIndex" = Table.ExpandTableColumn(GroupWithNestedCountIndex, "NestedIndex", {"PromoWeek", "Index", "Count"}, {"PromoWeek", "Index", "Count"})
    in
        #"Expanded NestedIndex"
    It looks as if you should be able to solve your other question with these techniques as well - if not pls let me know.


    Imke Feldmann TheBIccountant.com

    Tuesday, July 19, 2016 8:04 AM
    Moderator
  • Hi 007 :)

    "And if there’s a better/faster way of invoking previous rows in Power Query (w/o using the Index column), I’d be really keen to learn."

    When you're comparing data between rows in a column, using an index column is your only choice. Below is another possible solution, but I can't say whether the performance will be any better than Imke's - you'll have to let us know. I've added a "Year" column to the table to account for year boundaries. In order to calculate the week difference over a year boundary (week difference is negative), we must get the week number for the last day of year, subtract the number from the last week number in the table, and then add the first week number in the new year, e.g. :

    Week number for last week of 2016 = Date.WeekOfYear(#date(2016,21,31)) = 53
    Last week number for 2016 in table = 50
    First week number for 2017 in table = 1
    Number of week between 50 and 1 = 53-50+1 = 4

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        GroupedRows = Table.Group(Source, {"Customer", "Product"}, {{"Table", each _, type table}}),
        AddedIndexColumn = Table.TransformColumns(
                               GroupedRows, 
                               {"Table", (i)=> Table.AddIndexColumn(i,"Index")}
                           ),
        RemovedColumns = Table.TransformColumns(
                             AddedIndexColumn, 
                             {"Table", (i)=> Table.RemoveColumns(i, {"Customer", "Product"})}
                         ),
        WeekDifference = Table.TransformColumns(
                             RemovedColumns, 
                             {
                                 "Table", 
                                 (i)=> Table.AddColumn(
                                           i, 
                                           "Difference", 
                                           each try 
                                                   if [Promo Week] - i[Promo Week]{[Index]-1} < 0 then
                                                      Date.WeekOfYear(#date(i[Year]{[Index]-1}, 12, 31)) - i[Promo Week]{[Index]-1} + [Promo Week]
                                                   else
                                                      [Promo Week] - i[Promo Week]{[Index]-1}
                                                otherwise 99
                                       )
                              }
                          ),
        AddedGroupColumn = Table.TransformColumns(
                               WeekDifference, 
                               {
                                   "Table", 
                                   (i)=> Table.AddColumn(
                                             i, 
                                             "GroupColumn", 
                                             each if [Difference] <=3 then null else [Index] + 1
                                         )
                                }
                            ),
        FilledGroupColumn = Table.TransformColumns(
                                AddedGroupColumn, 
                                {
                                    "Table", 
                                    (i)=> Table.FillDown(
                                              i, 
                                              {"GroupColumn"}
                                          )
                                 }
                             ),
        ExpandedTable = Table.ExpandTableColumn(
                            FilledGroupColumn, 
                            "Table", 
                            {"Year", "Promo Week", "GroupColumn"}, 
                            {"Year", "Promo Week", "GroupColumn"}
                        ),
        GroupedRows2 = Table.Group(
                           ExpandedTable, 
                           {"Customer", "Product", "GroupColumn"}, 
                           {{"Table", each _, type table}}
                       ),
        AddedIndexColumn2 = Table.TransformColumns(
                                GroupedRows2, 
                                {
                                    "Table", 
                                    (i)=> Table.AddIndexColumn(i, "Week Promo", 1)
                                }
                            ),
        ExpandedTable2 = Table.ExpandTableColumn(
                             AddedIndexColumn2, 
                             "Table", 
                             {"Year", "Promo Week", "Week Promo"}, 
                             {"Year", "Promo Week", "Week Promo"}
                         )
    in
        ExpandedTable2
    Pasting the code has screwed up my "perfect" indenting somewhat.


    Wednesday, July 20, 2016 2:06 PM

All replies

  • Yes, you need to apply a very different technique in Power Query if you want to avoid recursion (which is slow):

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        AddIndex = Table.Buffer(Table.AddIndexColumn(Source, "Index", 1, 1)),
        DifferenceToPreviousRow = Table.AddColumn(AddIndex, "Custom", each [PromoWeek]-AddIndex[PromoWeek]{[Index]-2}),
        #"Replaced Errors" = Table.ReplaceErrorValues(DifferenceToPreviousRow, {{"Custom", 10}}),
        PrepareGrouping = Table.AddColumn(#"Replaced Errors", "Custom.1", each if [Custom]<=3then null else [Index]),
        NewGroups = Table.FillDown(PrepareGrouping,{"Custom.1"}),
        GroupWithNestedCountIndex = Table.Group(NewGroups, {"Customer", "Product", "Custom.1"}, {{"NestedIndex", each Table.AddIndexColumn(_, "Count", 1,1), type table}}),
        #"Expanded NestedIndex" = Table.ExpandTableColumn(GroupWithNestedCountIndex, "NestedIndex", {"PromoWeek", "Index", "Count"}, {"PromoWeek", "Index", "Count"})
    in
        #"Expanded NestedIndex"
    It looks as if you should be able to solve your other question with these techniques as well - if not pls let me know.


    Imke Feldmann TheBIccountant.com

    Tuesday, July 19, 2016 8:04 AM
    Moderator
  • Thank you for the query, Imke, it does work, however, it takes extremely long time for my dataset, almost 100k rows. It wasn’t done “thinking” after about 30 minutes, and I’ve closed it.

    Because of this, I’ve tried calculating the “DifferenceToPreviousRow” in Excel, instead of Power Query.

    I’m using the rest of your code, and it is much faster this way. 

    Many thanks, you’re a star.

    And if there’s a better/faster way of invoking previous rows in Power Query (w/o using the Index column), I’d be really keen to learn.

    Tuesday, July 19, 2016 12:47 PM
  • Hi 007 :)

    "And if there’s a better/faster way of invoking previous rows in Power Query (w/o using the Index column), I’d be really keen to learn."

    When you're comparing data between rows in a column, using an index column is your only choice. Below is another possible solution, but I can't say whether the performance will be any better than Imke's - you'll have to let us know. I've added a "Year" column to the table to account for year boundaries. In order to calculate the week difference over a year boundary (week difference is negative), we must get the week number for the last day of year, subtract the number from the last week number in the table, and then add the first week number in the new year, e.g. :

    Week number for last week of 2016 = Date.WeekOfYear(#date(2016,21,31)) = 53
    Last week number for 2016 in table = 50
    First week number for 2017 in table = 1
    Number of week between 50 and 1 = 53-50+1 = 4

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        GroupedRows = Table.Group(Source, {"Customer", "Product"}, {{"Table", each _, type table}}),
        AddedIndexColumn = Table.TransformColumns(
                               GroupedRows, 
                               {"Table", (i)=> Table.AddIndexColumn(i,"Index")}
                           ),
        RemovedColumns = Table.TransformColumns(
                             AddedIndexColumn, 
                             {"Table", (i)=> Table.RemoveColumns(i, {"Customer", "Product"})}
                         ),
        WeekDifference = Table.TransformColumns(
                             RemovedColumns, 
                             {
                                 "Table", 
                                 (i)=> Table.AddColumn(
                                           i, 
                                           "Difference", 
                                           each try 
                                                   if [Promo Week] - i[Promo Week]{[Index]-1} < 0 then
                                                      Date.WeekOfYear(#date(i[Year]{[Index]-1}, 12, 31)) - i[Promo Week]{[Index]-1} + [Promo Week]
                                                   else
                                                      [Promo Week] - i[Promo Week]{[Index]-1}
                                                otherwise 99
                                       )
                              }
                          ),
        AddedGroupColumn = Table.TransformColumns(
                               WeekDifference, 
                               {
                                   "Table", 
                                   (i)=> Table.AddColumn(
                                             i, 
                                             "GroupColumn", 
                                             each if [Difference] <=3 then null else [Index] + 1
                                         )
                                }
                            ),
        FilledGroupColumn = Table.TransformColumns(
                                AddedGroupColumn, 
                                {
                                    "Table", 
                                    (i)=> Table.FillDown(
                                              i, 
                                              {"GroupColumn"}
                                          )
                                 }
                             ),
        ExpandedTable = Table.ExpandTableColumn(
                            FilledGroupColumn, 
                            "Table", 
                            {"Year", "Promo Week", "GroupColumn"}, 
                            {"Year", "Promo Week", "GroupColumn"}
                        ),
        GroupedRows2 = Table.Group(
                           ExpandedTable, 
                           {"Customer", "Product", "GroupColumn"}, 
                           {{"Table", each _, type table}}
                       ),
        AddedIndexColumn2 = Table.TransformColumns(
                                GroupedRows2, 
                                {
                                    "Table", 
                                    (i)=> Table.AddIndexColumn(i, "Week Promo", 1)
                                }
                            ),
        ExpandedTable2 = Table.ExpandTableColumn(
                             AddedIndexColumn2, 
                             "Table", 
                             {"Year", "Promo Week", "Week Promo"}, 
                             {"Year", "Promo Week", "Week Promo"}
                         )
    in
        ExpandedTable2
    Pasting the code has screwed up my "perfect" indenting somewhat.


    Wednesday, July 20, 2016 2:06 PM