How do I count matching merged records?

[Netrelemo]

Table 1 is merged with table 2, (left outer)

1 Record in Table 1 has many records in Table 2.

All I want is a column with the record count of matching records in table 2. 

This doesn;t work

...

#"Merged Table" = Table.NestedJoin(#"Sorted Rows", {"ID"}, table2, {"ID"}, "table2", JoinKind.LeftOuter),
#"Expanded Table" = each Table.RowCount([ID]),

...

[Greg_Deckler · Accepted solution]

@Netrelemo - Try this:

let
    Source = Table.NestedJoin(#"Table (11)", {"Date"}, #"Table (12)", {"Date"}, "Table (12)", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.RowCount([#"Table (12)"])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table (12)"})
in
    #"Removed Columns"

[Netrelemo]

I'm getting exactly the same number on every row.... meaning it's counting the number of rows in the entire table. 

I want the number of MATCHING rows. 

Table1 has 10,000 rows. 342 rows in Table2 match in total. 

Your formula gives me 342 on every row. 

I'm looking for 

Table1 row #6 has 7 matching rows in Table2

Table1 row #92 has 12 matching rows in Table2

etc

[Greg_Deckler]

@Netrelemo - Not sure what is going on on your end, but I'm pretty sure I understood what you were after and mine works just fine. PBIX is uploaded below sig. Tables 11 and 12 and Merge1. Are you sure you referenced the Column of "Table (12)" and not the original "Table (12)". Your other table reference should have brackets [ ] around it because you are referring to the column that is created when you Merge that has the word "Table" in it for every row since each row contains a table of matching records from the second table.

[Netrelemo]

Rightyo, found the problem. There are a number of records without a match in table2. And due to the sorting applied they appeared first in my list, sending me down a rabbithole. 

Thanks. 

[Greg_Deckler]

@Netrelemo Happens! 🙂