Hello,
I have forecast and demand data for certain products for each month for 12 months. There hundreds of products, so for each month there are hundreds of rows with the forecast/demand data. I am calculating the difference between the actual demand data and the forecast data and wish to display a column that shows the total summed difference for each month. So, for all the products demanded in the month of 5/1/2015, the column will display the total difference between the forecast data and the actual demand for each product during that month. Any help would be appreciated. I will also gladly provide any additional needed information.
Thanks in advance!
Hi Alexios,
Welcome to our forum! This should be done in the PivotTable rather than Power Query. If you want to share a sample Excel file we can help you further.
Mynda
Hello Mynda,
I appreciate the response. However, I am interested in a solution that can be done within Power Query. I will provide a sample text example below.
period part demand forecast variance TOTAL VARIANCE BY MONTH
5/1/2015 A 10 9 1 4
5/1/2015 B 5 7 2 4
5/1/2015 C 5 6 1 4
6/1/2015 A 3 3 0 7
6/1/2015 B 8 2 6 7
6/1/2015 C 7 6 1 7
7/1/2015 A 7 2 5 14
7/1/2015 B 6 9 3 14
7/1/2015 C 4 10 6 14
I hope the formatting is clear. The column on the far right is the column I wish to generate within Power Query.
Hi Alexios,
It's not recommended to do that in PQ, it's better to make the aggregations in a pivot table, see image attached. The table you have is perfect for that.
If you still need that, use the query below:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"period ", type datetime}, {"part ", type text}, {" demand ", Int64.Type}, {"forecast ", Int64.Type}, {" variance ", Int64.Type}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([#"period "]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([#"period "]), Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Month",{{" variance ", "variance"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", (x)=> List.Sum(Table.SelectRows(#"Renamed Columns", each _[Year]=x[Year] and _[Month]=x[Month])[variance]))
in
#"Added Custom"
Hello Catalin,
I appreciate the response. Thank you everybody for the assistance. I will try the attached query, and make the aggregation in a Pivot Table.
Thanks,
Alexios
