Excel Power Query failing on adding Date Column

Question

So, I have data with a date (CLOSE_DATE) / value (KG_A7) pair where I am grouping (sum) the value over a month (using the first of the month as the column to group by). I can do this manually but am trying to write a function (fnGroupByMonth) as I need to do this transformation for different data sources.

I have working code:

let 
    fnGroupByMonth = (SourceTable, GroupBy as text, Values as text) =>
    let
        #"Inserted Start of Month" = Table.AddColumn(SourceTable, "Month", each Date.From(Date.StartOfMonth([CLOSE_DATE])), type date),
        #"Grouped Rows" = Table.Group(#"Inserted Start of Month", {"Month"}, {{Values, each List.Sum([KG_A7]), type number}}),
        #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Month", Order.Ascending}})
    in
        #"Sorted Rows"
in
    fnGroupByMonth

But when I try to change (1) [CLOSE_DATE] for Table.Column(SourceTable, GroupBy) or (2) [KG_A7] for Table.Column(SourceTable, Values) I get the following errors:

(1) The Date value must contain the Date component Details: [List]

(2) The same sum of Values for each month

I have tried without the as text and I have tried sending the column names as columns (so [CLOSE_DATE] instead of "CLOSE_DATE", in which case I get the error "There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

ChemEnger ChemEnger · Accepted Answer · 2020-07-21T17:28:26

OK, so I will confess that I don't understand the usage but I cribbed an answer from a similar question that I've just come across and replaced the Table.Column(SourceTable, GroupBy) with Record.Field(_, GroupBy) (and the same for Values) and it works! Now I've just got to learn why!!

Excel Power Query failing on adding Date Column

1 Answers