# Power Query -- List.Sum & Parameter



## Comfy (Sep 14, 2016)

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

I'm using the following parameter:


Excel 2010ABC3FilterDate4Month End31/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 (Sep 19, 2016)

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 (Sep 19, 2016)

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 (Sep 20, 2016)

Hello,

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


----------

