power query group

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Power Query - Group by

Power Query - Group by

resolvedResolved · Urgent Priority · Version 365

Angela has attended:
Excel Intermediate course
Excel Advanced course

Power Query - Group by

How to use the group by option and group by date and keep the rest of the columns?

RE: 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)

Wed 31 May 2023: Automatically marked as resolved.


 

Excel tip:

Percentage format

Ctrl+Shift+% applies the Percentage format, with no decimal places.

View all Excel hints and tips


Server loaded in 0.08 secs.