cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BarrySmithCONSL
New Member

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

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

 

 

3 REPLIES 3
Jimmy801
Super User III
Super User III

Hey @BarrySmithCONSL

were the answers of any help?

Have a nice time

Jimmy

watkinnc
Solution Specialist
Solution Specialist

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

Jimmy801
Super User III
Super User III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors