Custom Column in Datamart

Hi -

One possible solution is to do a Merge on the two tables, using the Employee identifier (that I assume is common in both).

Here is an employee table:

Here is an associated table I want to aggregate:

Here is the raw data I am using for "Sales", you can paste this into the Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YGwTRFMc1Mw0wjItDSAM42NwUxjINPEDM60ADJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee_ID = _t, Sales_Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee_ID", Int64.Type}, {"Sales_Value", Int64.Type}})
in
#"Changed Type"

After following the steps I outline below, my results are as follows. (Note in my example I am doing a Sum on the sales column, while in your case it sounds like you need to do a Count - you can just adujst the type of summary in the Group By clause that I explain below):

Here is the script that populates the Employees table, and then uses Merge and then Group to come up with the summary. You can put this in Advanced Editor to walk through the steps one at a time:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8gDUi75qUqxOtFKRkB2cGJOTiWIzs0syQCLGgN5Tpk5OWD1eanFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee_ID = _t, Employee_FName = _t, Employee_LName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee_ID", Int64.Type}, {"Employee_FName", type text}, {"Employee_LName", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Employee_ID"}, Sales, {"Employee_ID"}, "Sales", JoinKind.LeftOuter),
#"Expanded Sales" = Table.ExpandTableColumn(#"Merged Queries", "Sales", {"Sales_Value"}, {"Sales.Sales_Value"}),
#"Grouped Rows" = Table.Group(#"Expanded Sales", {"Employee_ID", "Employee_FName", "Employee_LName"}, {{"SumOfSales", each List.Sum([Sales.Sales_Value]), type nullable number}})
in
#"Grouped Rows"

First, I use the Merge Queries option to Merge Employees and Sales. Merge Queries is located in the upper right of the home tab:

I select Employee and click merge queries, then select the employee id as the key from Employees and the employee id as the key from Sales, and pick Left Outer Join.

Next, I expand the new Sales column (which contains Tables). I only keep the Sales value:

Then I do a Transform... Group By, and group on columns of the Employee table. In my case I chose Sum, you might choose Count depending on your needs:

Final result:

Hope this helps!

Peter