Power Query -- List.Sum & Parameter

[Comfy]

I think I'm missing the obvious so would appreciate alittle help.

I'm using the following parameter:

Excel 2010
	A	B	C
3	Filter	Date
4	Month End	31/07/2016
_control

dMonths

(myParameter) =>
let
    Source = Excel.CurrentWorkbook(){[Name="dMonths"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Date", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Filter] = myParameter)),
    ReturnParam = Record.Field(#"Filtered Rows"{0}, "Date")
in
    ReturnParam

tFact

MonthEnd = Date.ToText(Date.From(dMonths("Month End")),"dd-MMM-yy"),

My columns headers include the End of Month Date, which is why I'm using this (I also have one for Year End)

The following allows me to append the string:

 #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Description", "Plan #(lf)Month ending #(lf)"&MonthEnd, "Actual #(lf)Month ending #(lf)"&MonthEnd, "Variance #(lf)Month ending #(lf)"&MonthEnd, "Plan #(lf)YTD ending #(lf)"&MonthEnd, "Actual #(lf)YTD ending #(lf)"&MonthEnd, "Variance #(lf)YTD ending #(lf)"&MonthEnd, "Plan #(lf)Year ending #(lf)"&YearEnd, "Simple Forecast Year ending#(lf)"&YearEnd, "Forecast Adjustment Year ending#(lf)"&YearEnd, "Adjusted Forecast #(lf)Year ending#(lf)"&YearEnd, "Forecast Variance #(lf)Year ending#(lf)"&YearEnd}),

But I can't use this within List.Sum when grouping:

#"Grouped Rows" = Table.Group(#"Filtered Rows2", {"NewColumn.Sort Order", "NewColumn.Sub Total Heading", "NewColumn.Internal Reporting Header"}, {{"Plan This Month", each List.Sum([#"Plan #(lf)Month ending #(lf)"&MonthEnd]), type number}, {"Actual This Month", each List.Sum([#"Actual #(lf)Month ending #(lf)31-Jul-16"]), type number}, {"Var This Month", each List.Sum([#"Variance #(lf)Month ending #(lf)31-Jul-16"]), type number}, {"Plan YTD", each List.Sum([#"Plan #(lf)YTD ending #(lf)31-Jul-16"]), type number}, {"Actual YTD", each List.Sum([#"Actual #(lf)YTD ending #(lf)31-Jul-16"]), type number}, {"Var YTD", each List.Sum([#"Variance #(lf)YTD ending #(lf)31-Jul-16"]), type number}, {"Annual Plan", each List.Sum([#"Plan #(lf)Year ending #(lf)31-Mar-17"]), type number}, {"Simple Forecast", each List.Sum([#"Simple Forecast Year ending#(lf)31-Mar-17"]), type number}, {"Forecast Adjustment", each List.Sum([#"Forecast Adjustment Year ending#(lf)31-Mar-17"]), type number}, {"Adjusted Forecast", each List.Sum([#"Adjusted Forecast #(lf)Year ending#(lf)31-Mar-17"]), type number}, {"Forecast Var", each List.Sum([#"Forecast Variance #(lf)Year ending#(lf)31-Mar-17"]), type number}}),

The error is highlighted, "Invalid identifier"

 

[ImkeF]

Yes, this is a bit tricky, as you cannot pass any parameters into the square brackets.
You can use Table.SelectColumns instead, there the expression is expected to be a simple text-expression within a list:

List.Sum(Table.ToColumns(Table.SelectColumns(_, {ParameterName})){0})}

(Unfortunately just for this case) it doesn't return the column in list-form, but a table with just one column, so you need to transform that into a list first before you can create the sum.

... would also be interested if there is an easier way to do this.

 

[Comfy]

Thanks for the response.

As a quick fix I renamed the columns to remove the changing date:

RenameCols = Table.RenameColumns(FilterUnJoinedRows,{{"Plan #(lf)Month ending #(lf)"&MonthEnd, "Plan #(lf)Month ending"}, {"Actual #(lf)Month ending #(lf)"&MonthEnd, "Actual #(lf)Month ending"}, {"Variance #(lf)Month ending #(lf)"&MonthEnd, "Variance #(lf)Month ending"}, {"Plan #(lf)YTD ending #(lf)"&MonthEnd, "Plan #(lf)YTD ending"}, {"Actual #(lf)YTD ending #(lf)"&MonthEnd, "Actual #(lf)YTD ending"}, {"Variance #(lf)YTD ending #(lf)"&MonthEnd, "Variance #(lf)YTD ending"}, {"Plan #(lf)Year ending #(lf)"&YearEnd, "Plan #(lf)Year ending"}, {"Simple Forecast Year ending#(lf)"&YearEnd, "Simple Forecast Year ending"}, {"Forecast Adjustment Year ending#(lf)"&YearEnd, "Forecast Adjustment Year ending"}, {"Adjusted Forecast #(lf)Year ending#(lf)"&YearEnd, "Adjusted Forecast #(lf)Year ending"}, {"Forecast Variance #(lf)Year ending#(lf)"&YearEnd, "Forecast Variance #(lf)Year ending"}}),
    GroupRows = Table.Group(RenameCols, {"NewColumn.Sort Order", "NewColumn.Sub Total Heading", "NewColumn.Internal Reporting Header"}, {{"Plan This Month", each List.Sum([#"Plan #(lf)Month ending"]), type number}, {"Actual This Month", each List.Sum([#"Actual #(lf)Month ending"]), type number}, {"Var This Month", each List.Sum([#"Variance #(lf)Month ending"]), type number}, {"Plan YTD", each List.Sum([#"Plan #(lf)YTD ending"]), type number}, {"Actual YTD", each List.Sum([#"Actual #(lf)YTD ending"]), type number}, {"Var YTD", each List.Sum([#"Variance #(lf)YTD ending"]), type number}, {"Annual Plan", each List.Sum([#"Plan #(lf)Year ending"]), type number}, {"Simple Forecast", each List.Sum([#"Simple Forecast Year ending"]), type number}, {"Forecast Adjustment", each List.Sum([#"Forecast Adjustment Year ending"]), type number}, {"Adjusted Forecast", each List.Sum([#"Adjusted Forecast #(lf)Year ending"]), type number}, {"Forecast Var", each List.Sum([#"Forecast Variance #(lf)Year ending"]), type number}}),

 

[Useful]

Hello,

I've made tricky video about that see Video_020_View in Tabular Form and Running Total With_Power_Query