The Antares Alien

Game Dev Blog

  • Brent Jones

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


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!

#PowerQuery #Aggregate #Expand #Merge #Tables #Tips #Speed #Performance

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Medium_With_Title_Cropp
Cover Art - Main_Large_With_Title.png