Custom Column in Datamart

[VarunGupta]

i am trying to creata a DataMart with 2 tables (Table1 & Table2) where i have create a relationship between these two table by common column (Column1).
Now Table 1 consist of unique list of employees and table consist to daily log of iteams where i need to create a custom column in Table 1 in which it show count of total iteam against each employee.
but in Edit query i am not able to create the logic in Table 1 and it is not showing values of Table2. 
So how can i create custom Column where we can get count from other table.

In Dax i can create a column with Calculate function like: { Calculate(count(Table2[Column2]), filter(Table2, Table2[Column1] = Table1[Column1]])) } 

[Peter_Beck]

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