Display only the first record after merging 2 tables

[garich]

Hi,

I would like to merge 2 tables below linking Customer ID and Person ID where I only need to display the first top record of Table B.

Table: A

ID	CustomerID
1	123
2	456
3	789

Table: B

ID	PersonID	PRSName
1	123	Doe, Jane
2	123	Doe, Mary Jane
2	456	Smith, John
3	789	White, Robert
4	789	White, Robert Jr

I have this M language query which results to the Actual below and I'm stuck on what to do next...

    #"Merged Queries1" = Table.NestedJoin(#"Expanded PRS DateExtensions", {"CustomerID"}, #"PRS HistoricPersonRecords", {"PersonID"}, "PRS HistoricPersonRecords", JoinKind.LeftOuter),
    #"Expanded PRS HistoricPersonRecords" = Table.ExpandTableColumn(#"Merged Queries1", "PRS HistoricPersonRecords", {"PRSName"}, {"PRS HistoricPersonRecords.PRSName"}),

Expected Result:

ID	CustomerID	PRSName
1	123	Doe, Jane
2	456	Smith, John
3	789	White, Robert

Actual Result:

ID	CustomerID	PRSName
1	123	Doe, Jane
1	123	Doe, Mary Jane
2	456	Smith, John
3	789	White, Robert
3	789	White, Robert Jr

[garich]

@slorin thank you! But how do I handle null values? It is throwing an error

[slorin]

Hi,

try [#"PRS HistoricPersonRecords"]{0}[PRSName] otherwise null

if Table.IsEmpty([#"PRS HistoricPersonRecords"]) then null else [#"PRS HistoricPersonRecords"]{0}[PRSName]

Stéphane 

[slorin · Accepted solution]

Hi,

Step1 = Table.NestedJoin(
   #"Expanded PRS DateExtensions", {"Customer ID"},
   #"PRS HistoricPersonRecords", {"PersonID"},
   "PRS HistoricPersonRecords", JoinKind.LeftOuter),
Step2 = Table.AddColumn(Step1, "PRSName", each [#"PRS HistoricPersonRecords"]{0}[PRSName])

 Stéphane