Forum Discussion
Solved
Display only the first record after merging 2 tables
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 |
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
3 Replies
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
Hi,
try [#"PRS HistoricPersonRecords"]{0}[PRSName] otherwise nullif Table.IsEmpty([#"PRS HistoricPersonRecords"]) then null else [#"PRS HistoricPersonRecords"]{0}[PRSName]Stéphane
