Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Error in Table.AggregateTableColumn of Power Query M

Table.AggregateTableColumn of Power Query M doesn't allow usage of type as 4-th parameter of an aggregation for last aggregation. I.e if you do this:
Table.AggregateTableColumn(aTable, {"F1", List.Max, "NF1", type number}, {"F2", List.Max, "NF2", type number}) - no any column will be added, but if you do the same without type in the last aggregation - all works properly:

Table.AggregateTableColumn(aTable, {"F1", List.Max, "NF1", type number}, {"F2", List.Max, "NF2"})

1 vote
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Andrey Minakov shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • Sergei Baklan commented  ·   ·  Flag as inappropriate

    Andrey, with current implementation of AggregateTableColumn any transformation of the table works as far as it returns the table without changing of the initial table content. Localization in memory with Buffer, faking rows removing, Table.Combine({aTable}),... - doesn't matter, everything works if we move the reference from another step into the function.

  • Andrey Minakov commented  ·   ·  Flag as inappropriate

    Hi Sergei! Thanks a lot for the information, it' very useful.
    And, as far as I understand, such behavior of M in case of Table.RemoveFirstN looks like a bug. I would suggest the difference between Buffered and not buffered table (because Buffer makes the table immutable from M point of view, but therefore I don't see clear logic in that...), but for other operations I don't see any logic at all.

  • Sergei Baklan commented  ·   ·  Flag as inappropriate

    Andrey, it doesn't work if the function takes as parameter the table generated on one of previous steps. On the last aggregation with type it goes into the loop trying to load that table and assign the type.

    However, if the table is generated within the function when last aggregation with type defined works fine, i.e. the final result is as expected.

    Other words, if you use something like

    =Table.AggregateTableColumn( Table.Buffer(aTable),...
    or
    =Table.AggregateTableColumn( Table.RemoveFirstN(aTable, 0),...

    it works. Can't say I fully understand what is behind...

Feedback and Knowledge Base