none
Avoiding creating columns just for grouping

    Question

  • I frequently find myself adding columns to my tables in power query just so that I can group by a function.

    A common example is I have a Date/Time field, and I want to group by the Date part, or something similar.

    Currently, I have to create a new custom column, use a formula, and then group by that column. 

    In essence, I create a duplicate of quite a lot of data, and then never use it again. I understand that I can delete the column, but the whole process just seems wasteful.

    Is there a better way?

    Sunday, July 13, 2014 6:53 AM

Answers

  • There is no other way to perform grouping. In terms of execution performance, it's not really more expensive to add a custom column, group by it, and then remove it again. If you found it advantageous, you could write a simple helper function which does all three steps, but then you'd lose the benefits of the editor.

    Monday, July 14, 2014 3:51 PM

All replies

  • There is no other way to perform grouping. In terms of execution performance, it's not really more expensive to add a custom column, group by it, and then remove it again. If you found it advantageous, you could write a simple helper function which does all three steps, but then you'd lose the benefits of the editor.

    Monday, July 14, 2014 3:51 PM
  • If your data is coming from a database server, you could write a SQL query which contains a GROUP BY clause for a field not in the resultset, otherwise you are doing it the only way you can in Power Query.

    Monday, July 14, 2014 3:58 PM
  • Why not just transform the date/time column to date, year...whatever, and then group by the transformed column?
    Monday, July 14, 2014 5:10 PM