Problem
Recently I found myself wondering why Power Query doesn't give you the option to expand a merged table with both non-aggregate values AND aggregate values.
For example, let's say you have an Employee table and want to merge (left join) it with a Sales table. The Sales table has a "Location" field that you want to group by and a "Sales Amount" field that you want to sum by. Well, it seems that Power Query wants you choose one or the other, not both...
1) Expand Options (Group By):
2) Aggregate Options (Sum/Count):
Solution
We can get around this by selecting the Aggregate options for whatever fields we want.
In this example, we are going to choose Count of Location and Sum of Sales. Don't worry, I know we don't actually want the count of location. We are going to modify the formula ever-so-slightly to give us what we want.
After selecting "Count of Location" and "Sum of Sales", we will get the following formula (step):
All we need to do is change the "List.Count" to "List.First".
From this:
= Table.AggregateTableColumn(
#"Renamed Columns",
"Employee Sales", {
{"Location", List.Count, "Count of Location"},
{"Sales", List.Sum, "Sum of Sales"}
})To this:
= Table.AggregateTableColumn(
#"Renamed Columns",
"Employee Sales", {
{"Location", List.First, "First Location"},
{"Sales", List.Sum, "Sum of Sales"}
})Of course, if there are multiple Locations for an employee, it'll only show the first one that appears, so be cautious of using this method.
Bonus Tip: Query Speed
Alien Says: Speaking of merged columns, you can DRAMATICALLY increase the speed of the query by setting a primary key value on your tables that you are merging together.
Take a look at Chris's blog to see more details (blog.crossjoin.co.uk), but basically he describes two ways to do this.
1. Use the function "Remove duplicate values".
2. Table.AddKey(tableName, {columnName}, true)
Honestly I didn't expect it to have this much of an impact on me, but it ended up reducing my query loading time from 42 seconds to just 6 seconds!!
I hope you found something useful here - Cheers!
