Power Query: Merged Tables, Aggregate and Non-Aggregate Values

April 8, 2019

 

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 either or...

 

Expand Options (Group By):

 

Aggregate Options (Sum/Count):

 

We can get around this by selecting the Aggregate options for whatever fields we want - in this example, 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 M code ever-so-slightly to give us what we want.

 

After selecting "Count of Location" and "Sum of Sales", we will get the following M code (step):

 

 

All we need to do is change the "List.Count" to "List.First", so it will now look like this:

= Table.AggregateTableColumn(

   #"Renamed Columns",

   "Employee Sales", {

    {"Location", List.Count, "Count of Location"},

    {"Sales", List.Sum, "Sum of Sales"}

  })

= 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.

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!

 

Please reload

About the Author

Brent Jones loves spending time with his wife and trying out new activities together.  They traveled as digital nomads to many countries in South America, Europe and Asia. He can also speak Japanese!

Our Recent Posts

Please reload

Tags

Address

7000 Central Pkwy #1100, Atlanta GA 30328

Follow

  • Twitter - Black Circle
  • Black Pinterest Icon

©2019 BY ANTARES ANLYTICS. PROUDLY CREATED WITH WIX.COM