Append Queries and Group By in Power Query

In short: Home > Append Queries puts the rows of a second query below the first (Table.Combine). Home > Group By collapses rows with the same value and counts or sums them on the way (Table.Group).

Try it now: Group By

How many jobs are there per site? Group the list and count the rows.

Task How many jobs are there per site? Group by Site and count the rows in a new column called Count.

	A	B	C
1	Job	Site	Amount
2	A-01	Berlin	450
3	A-02	Hamburg	1200
4	A-03	Berlin	300
5	A-04	Leipzig	780
6	A-05	Berlin	150
7	A-06	Hamburg	640
8	A-07	Leipzig	90

How Group By works

1. Select the column to group by, here Site.
2. Choose Group By. The dialog asks for the grouping column, a name for the new column, the Operation and, where needed, the column to aggregate.
3. Operations include Sum, Average, Min, Max and Count Rows. Switch to Advanced to group by several columns or add several aggregations in one step.

The result has one row per value of the grouping column. Count Rows does what COUNTIF does and Sum does what SUMIF does, except you never have to list the categories first. New sites simply appear after a refresh.

Append Queries

Appending stacks tables on top of each other. Think monthly files, branch offices or exports from two systems with the same layout.

• Power Query matches columns by name, not by position. "Qty" and "Quantity" end up as two separate columns.
• If a column is missing from one table, its cells stay empty (null).
• The three-or-more-tables option appends any number of queries in one step.
• Append Queries as New creates the result as a separate query and leaves the first one unchanged.

People often mix up appending and merging. Append adds rows (stacked), Merge adds columns (side by side, like VLOOKUP).

One more exercise: add up two branches

Append the South branch's orders below the North branch's, then sum the quantity per item.

Task The North and South branches send their orders in two tables with the same layout. Append South below North, then group by Item with the sum of Qty in a new column called Total qty.

	A	B	C	D	E
1	Item	Qty		Item	Qty
2	Paper	40		Toner	4
3	Toner	6		Paper	30
4	Binders	25		Pens	50
5	Paper	15

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

Grouping by more than one column

To see quantities per branch and item, you need two grouping columns. Select both before running the command, or switch to Advanced in the dialog and add a second grouping. The result then has one row per combination. So you can still tell which branch a row came from after appending, add a column with the branch name to each query before you append them.

The M code of the second exercise

= Table.Combine({Source, South})
= Table.Group(#"Appended Query", {"Item"}, {{"Total qty", each List.Sum([Qty]), type nullable number}})

Table.Combine takes a list of tables, hence the curly braces. With three branches, you would simply add a third query to that list. In Table.Group, the grouping columns are followed by a list of aggregations: new column name, the calculation per group, and the data type. For counting it reads each Table.RowCount(_), the number of rows in each group. The keyword each means "do this for every group".

Common mistakes

Duplicate columns after appending

The headers differ, for example "Qty" and "Qty " with a trailing space. Give the columns identical names in both queries before you append.

Sum shows Error

The value column contains text. Set its data type to Whole Number or Decimal Number before grouping.

Groups appear twice

"Berlin", "Berlin " and "berlin" are three different values to Power Query. Trim the grouping column and standardize the case first.

In German Excel

English	German
Append Queries	Abfragen anfügen
Group By	Gruppieren nach
Sum, Average, Count Rows	Summe, Durchschnitt, Zeilen zählen

Which Excel versions support this?

Both commands are part of the Power Query Editor in Excel 2016 and later for Windows and in Microsoft 365. On a Mac they need Microsoft 365. See the Power Query overview for details.

FAQ