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