How to Create Sales Summaries with Power Query

Sales data analy­sis often requires sum­ma­riz­ing results by spe­cif­ic cat­e­gories to gain valu­able busi­ness insights. In this arti­cle, I demon­strate how to effi­cient­ly cal­cu­late sales sum­maries using Pow­er Query’s group­ing func­tion­al­i­ty, pro­vid­ing a pow­er­ful alter­na­tive to tra­di­tion­al Excel for­mu­las for ana­lyz­ing mer­chant per­for­mance, income totals, and prod­uct distribution.

The group­ing tech­nique shown allows for mul­ti­ple aggre­ga­tions in a sin­gle oper­a­tion, dra­mat­i­cal­ly improv­ing effi­cien­cy when work­ing with larg­er datasets.

Watch my detailed tuto­r­i­al video below to fol­low along with each step:

Understanding the Sales Summary Challenge

When ana­lyz­ing sales data, we often need to cre­ate sum­maries that show per­for­mance met­rics grouped by spe­cif­ic cat­e­gories. In this demon­stra­tion, I’m work­ing with a dataset con­tain­ing sales trans­ac­tions that include mer­chants, prod­ucts, dates, and income val­ues. My goal is to sum­ma­rize this data to show sev­er­al key met­rics for each merchant:

• Count of sales transactions
• Sum of income
• Aver­age income per transaction
• List of unique prod­ucts sold

While these cal­cu­la­tions can be done using Excel for­mu­las (which I’ve pre­pared in the work­sheet for com­par­i­son), using Pow­er Query offers sig­nif­i­cant advan­tages, espe­cial­ly for larg­er datasets or repet­i­tive analy­sis tasks.

Importing Data into Power Query

The first step in our process is to bring the data into Pow­er Query where we can per­form the group­ing operations:

1. Select the data table in Excel
2. Nav­i­gate to the Data tab in the Excel ribbon
3. Click “From Table/Range” to import the data into Pow­er Query

Once the data is loaded into the Pow­er Query Edi­tor, we can see all our sales records and begin the group­ing process to cre­ate our summary.

Creating Groups in Power Query

To sum­ma­rize our data by mer­chant, we need to use the Group By func­tion in Pow­er Query:

1. Select the Mer­chant col­umn in the Pow­er Query Editor
2. Go to the Home tab and click “Group By”

The default Group By dia­log offers basic func­tion­al­i­ty with just one col­umn for group­ing and a sin­gle aggre­ga­tion. For our needs, we require the Advanced option since we want to cre­ate mul­ti­ple aggre­ga­tions based on the mer­chant column.

Setting Up Advanced Grouping

In the advanced Group By dia­log, we need to con­fig­ure mul­ti­ple aggregations:

1. Set the group­ing col­umn to Mer­chant
2. Add the fol­low­ing aggregations: 
   • “Count Rows” for the num­ber of sales (named “Count”)
   • “Sum” of the Income col­umn (named “Sum”)
   • “Aver­age” of the Income col­umn (named “Aver­age”)
   • “All Rows” to cap­ture all relat­ed data (named “List”)

After click­ing OK, Pow­er Query cre­ates a new table with our mer­chant groups and the spec­i­fied aggre­ga­tions. The Count, Sum, and Aver­age val­ues match the Excel for­mu­las we pre­pared ear­li­er, con­firm­ing that our group­ing is work­ing correctly.

Customizing the Product List

The “All Rows” aggre­ga­tion cre­ates a nest­ed table for each mer­chant con­tain­ing all their sales records. How­ev­er, we only want a list of unique prod­ucts sold by each mer­chant, not all the row data. Since the Group By dia­log does­n’t offer this spe­cif­ic option, we need to mod­i­fy the M for­mu­la directly.

Modifying the M Formula

To cus­tomize our prod­uct list, we need to edit the M code that Pow­er Query generated:

1. The default “All Rows” aggre­ga­tion pro­duces tables with all columns for each merchant
2. We need to change this to extract only the Prod­uct col­umn values
3. Fur­ther­more, we need to elim­i­nate dupli­cates from these lists

The orig­i­nal for­mu­la includes {_} which ref­er­ences the entire row. We need to replace this with a ref­er­ence to just the Prod­uct col­umn: [Prod­uct]. Addi­tion­al­ly, we need to wrap this in the List.Distinct func­tion to remove duplicates:

The mod­i­fied for­mu­la sec­tion should look like: List.Distinct([Product])

Transforming the Product List into Text

Now that we have lists of unique prod­ucts for each mer­chant, we can trans­form these lists into com­ma-sep­a­rat­ed text val­ues for bet­ter readability:

1. Click on the expand but­ton in the List col­umn header
2. Select “Extract Val­ues…” rather than “Expand to New Rows”
3. Choose a cus­tom delim­iter (com­ma fol­lowed by space: “, ”)
4. Click OK

This trans­forms our prod­uct lists into read­able text strings show­ing all the unique prod­ucts each mer­chant has sold, sep­a­rat­ed by commas.

Loading the Results Back to Excel

Once we’ve cre­at­ed our sum­ma­ry with all the required met­rics, we can load the results back to Excel:

1. Go to the Home tab and click “Close & Load To…”
2. Select “Exist­ing Work­sheet” and choose where to place the results
3. Click OK

After load­ing, we can for­mat the Sum and Aver­age columns as cur­ren­cy to improve read­abil­i­ty. The final result is a clean sum­ma­ry table show­ing the count of sales, total income, aver­age income, and unique prod­ucts for each merchant.

Benefits of Using Power Query for Grouping

While our exam­ple used a rel­a­tive­ly small dataset, the advan­tages of using Pow­er Query for this type of analy­sis become even more appar­ent with larg­er data volumes:

• Effi­cien­cy — Per­forms cal­cu­la­tions much faster than Excel for­mu­las for large datasets
• Repeata­bil­i­ty — The query can be refreshed when data changes with­out redo­ing formulas
• Flex­i­bil­i­ty — Allows for com­plex aggre­ga­tions and trans­for­ma­tions that would be dif­fi­cult with Excel formulas
• Data clean­ing — Inte­grates with oth­er Pow­er Query trans­for­ma­tions for com­pre­hen­sive data preparation

By mas­ter­ing this group­ing tech­nique in Pow­er Query, you can cre­ate sophis­ti­cat­ed sales sum­maries and oth­er data aggre­ga­tions with just a few clicks, sav­ing sig­nif­i­cant time and effort com­pared to tra­di­tion­al Excel methods.