Calculated Columns in Power Query for Staggered Product Launches

[FONT=&quot]Hi,[/FONT]
[FONT=&quot]I'm creating a financial model where a "Product" will be launched several times over the coming years. The Product will have a predictable financial profile but I'm trying to build in flexibility to run scenarios where we specify how many of these products are launched in a given year. I'm trying to calculate the total amount for a given account e.g. Revenues in a year, taking into account the revenue profiles of all the products launched in the previous year.[/FONT]
[FONT=&quot]E.g. If a Product produces Revenues of 100, 200, and 400 in Years 1-3 respectively, and I launch 1 Product in each of the first 3 years, then the Revenues in Year 3 should be 600 (400 from the product launched in Year 1, 200 from the product launched in Year 2, and 100 from the product launched in Year 3).[/FONT]
[FONT=&quot]I have tabular data which includes:[/FONT]
[FONT=&quot]1) Annual Account profiles for each product e.g. Revenues from Year 1 through Year X[/FONT]
[FONT=&quot]2) Profile of the number of products launch in a given year i.e. Product launches in each of Years 1 through X[/FONT]
[FONT=&quot]I've figured out a way to do this with helper rows where I transpose the Product launches row (2) and then multiply it by the account profiles (1). The problem with this is that I'm modeling over many years and this creates an unnecessary amount of these "helper rows" for each account that I model e.g. Revenues, COGS, Opex, etc.[/FONT]
[FONT=&quot]I'm trying to figure out a way to do the above programmatically through Power Query but I'm getting tripped up by the staggered nature of this model. [/FONT]
[FONT=&quot]Below is a link to an Excel example, which is hopefully better than my explanation. Essentially, I've got the cells with blue text in data tables and I'm trying to get the cells highlighted in yellow, without using the cells with red text.[/FONT]
[FONT=&quot]Excel example: https://www.dropbox.com/s/noq4a1trasf6t4l/Staggered Product Launch Model.xlsx?dl=0[/FONT]
[FONT=&quot]I know this is a mouthful but I'd appreciate any help on this.[/FONT]
[FONT=&quot]Thanks in advance![/FONT]
[FONT=&quot]Kind regards,[/FONT]
[FONT=&quot]Uwais [/FONT]

This thread has been posted on at least three other forums. You will need to provide links to those threads here before we can offer any help, please. Thanks.

Thanks AliGW. I've posted this question at these forum links:

• https://www.mrexcel.com/board/threa...query-for-staggered-product-launches.1140720/
• https://chandoo.org/forum/threads/c...r-query-for-staggered-product-launches.44711/
• https://www.myonlinetraininghub.com...columns-for-staggered-product-launches#p15375
• https://www.reddit.com/r/excel/comments/hve62d/calculated_columns_in_power_query_for_staggered/
• https://www.excelforum.com/excel-fo...r-staggered-product-launches.html#post5368389

In the attached, Sheet1 has all the information on it for your solution (Your Model sheet isn't referred to).
The two small tables on the left are the source data tables.
The green table is a Power Query table.
The 4th (Pivot) table is that green table pivoted. You could do all the aggregating in Power Query if you wanted, but I suggest that having the intermediate green table (visible or not) can lead to:
1. More flexibility on how you pivot that data
2. With only a small change or two, you can tweak the Power Query to retain other data such as the identity (eg. Launch year and specific id of that launch (2020.1, 2020.2 sort of thing)) which you can choose to include in your pivot.

ps. note that because I've chosen to show the full table (green) you'll need to click refresh all twice, once to refresh the query, and a second time to update the pivot table.