Power Query - Group by

Hi Angela,

Thankyou for your forum question.

When you group by a main category such as dates to show, for example, all sales summarised by a specific date, the other columns will disappear automatically.

The only way to avoid this happening is if the other columns are part of the 'group by' process.

Say you had a set of expenses for each date, department and expense type. You need to then group by the broadest category first then the next and the next after that. If you want to group by dates by month do the following:

1. In Power Query, right click the date heading and go to
Transform > Month > Start of Month
2. Again right click the date heading and click on 'Group by'
3. Select 'Advanced' > Add grouping
4. Ensure 'Date' is displayed > Add grouping
5. Select department > Add grouping
6. Select expense type
7. For 'New column name' type a name for the value you need to aggregate
8 Select Operation eg. Sum
9. Select value column to aggregate
10. Click OK

I hope this resolves your problem and please us know if it works

Kind regards
Martin
(STL technical trainer)