How can I calculate something which has other rows as inputs in power query?

Question

I have a table in Power query, which besides other fields has the following key fields:

As an example, some rows would be:

AB587 | 2019 | 12 | Tom | Purchase | Forecast |200

AB587 | 2019 | 12 | Tom | Sale | Forecast |15

AB587 |2019  | 11 | Tom | Stock | Actual |1455

This is a table with about 300,000 rows with all the SKUs and a couple of year's worth of transactions for all customers, and this gets into a very very useful pivot table that is used extensively. I now need to add something to the data to make the table even more useful.

I have the forecast for purchases and sales for the whole year along with the actuals of course and they follow the above pattern. I also have the stock for all the weeks but only the one in the past i.e. actuals only. I don't have the stock forecast, which is what I want to add. The calculation is as simple as:

Stock from previous week + Purchase forecast from this week - Sale forecast from this week

The end result which I am expecting is that there will now be more rows added which will have as an example:

AB587 |2019 | 12 | Tom | Stock | Forecast |1640

(I am using numbers from above to calculate)

This will now enable me not only to pivot Purchase and Sales but also stock levels which will be game changing.

I would love for anyone to help me with this in Power Query (I have tried a number of methods over weeks but have not cracked it)

To try and solve it myself:

I appended more rows essentially appending Week-1 data for all actual weeks from my source reducing potentially some calculation time. Then I pivoted my "Transaction column" leading to new columns i.e. Purchase, Sale, Stock and Stock-1, which made the Stock forecast calculation easy (that's what it appears to be).

The thing which I did not think about is: this is only good to calculate the first week stock forecast, but then there is no way that I know to use that just calculated stock forecast to calculate the next week's stock forecast.

Basically there is no way to save that stock forecast that I just calculated to be used for the next week's calculation.

Wedge Wedge · Accepted Answer · 2019-07-18T16:32:24

I'm not clear on what you are asking when you say you say you want to use the "calculated stock forecast to calculate the next week's stock forecast". If you just want to generate the formula and result you gave as an example as a component of your dataset though, that is pretty simple.

Starting from this as a sample table of your data loaded into PQ that I'm calling "Data Table":

I create two reference queries based off it called StockForecast and CombinedDataTable

In the "StockForecast" query we will add three custom columns. Two are the CalcYear and CalcWeek columns that take "Stock Actual" records and increase the week by one. The third is a CalcValue column that takes "Sale Forecast" records and makes the value in those negative. The code in the editor looks like this:

Source = DataTable,
#"Added Custom" = Table.AddColumn(Source, "CalcYear", each 
    if [Transaction] = "Stock" and [Type] = "Actual" then 
        (if [Week] = 52 then [Year] + 1 else [Year])
    else [Year]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "CalcWeek", each 
    if [Transaction] = "Stock" and [Type] = "Actual" then 
        (if [Week] = 52 then 1 else [Week] + 1)
    else [Week]
    ),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "CalcValue", each 
    if [Transaction] = "Sale" and [Type] = "Forecast" then 
        [Value] * -1 
    else [Value]
    ),

Then you use the Group function and aggregate by Stock, Customer, CalcYear and CalcWeek, with a Sum on the CalcValue function. This gets the Stock Forecast value you are looking for. After that it's just a matter of adding a couple columns for identification and some cleanup.

    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Stock", "Customer", "CalcYear", "CalcWeek"}, {{"Value", each List.Sum([CalcValue]), type number}}),
    #"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Transaction", each "Stock"),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Type", each "Forecast"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom4",{{"CalcYear", "Year"}, {"CalcWeek", "Week"}})
in
    #"Renamed Columns"

Then end result of the data looks like this:

Then just go to the CombinedDataTable query, append the StockForecast query, and you have Stock Forecast values in your dataset.

How can I calculate something which has other rows as inputs in power query?

1 Answers