Using Power Query M Formula, from within TABLE_A, count related rows in TABLE_B

[BarrySmithCONSL]

From within [TRANSACTION], I need to, in the most efficient method possible, count the rows (there may be none) in [PERSON], via the field [PersonID].  I've searched, but, all the functions I've found seem to relate to the table I am working on, unless I'm completely misreading the articles.

TABLEs:

[TRANSACTION] > TransactionID, PersonID

[PERSON] > PersonID

[Jimmy801]

Hey @BarrySmithCONSL

were the answers of any help?

Have a nice time

Jimmy

[Anonymous]

You could use Table.Rowcount(Table.SelectRows(TRANSACTION, each List.Contains(PERSON[PersonID], [PersonID])))

[Jimmy801]

Hello @BarrySmithCONSL 

try some merging and transforming like this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIzjLGMgyArNMgCxjMMsUyDIBs8yALFMwyxzOsgCyzJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TransactionID = _t, PersonID = _t]),
    PERSON = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1cFGGuEgjTFIE6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PersonID = _t])

        in
        Source,
    #"Merged Queries" = Table.NestedJoin
        (
            Source, 
            {"PersonID"}, 
            PERSON, 
            {"PersonID"}, 
            "PERSON", 
            JoinKind.LeftOuter
        ),
    Transform = Table.TransformColumns
        (
            #"Merged Queries", 
            {{"PERSON", each if Table.IsEmpty(_) then 0 else Table.RowCount(_)}}
        )
    
in
    Transform

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy