Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
Hi,
try [#"PRS HistoricPersonRecords"]{0}[PRSName] otherwise null
if Table.IsEmpty([#"PRS HistoricPersonRecords"]) then null else [#"PRS HistoricPersonRecords"]{0}[PRSName]
Stéphane
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