Hello,
I'm having difficultly merging two tables through the merge query function.
Here's my situation:
Table A has numerous fields, but the ones of interest are [First Name], [Last Name], [Client ID], [Personal ID], [Date of Birth] and a concatenated field that's derived from combining both [First Name] & [Date of Birth] together, which I'll refer to as [Concatenated First/DOB].
Table B also has a variety of different fields AND also includes the fields of interest I listed for Table A.
Both tables essentially contain their own set of clients, who may or may not appear on both tables.
Using the Merge Query function, I'm only able to merge both tables based on a single matching column. For my purposes, I want my rows on both tables to merge if ANY of the following holds true:
TableA.[Client ID] matches TableB.[Client ID]
TableA.[Personal ID] matches TableB.[Personal ID]
TableA.[Concatenated First/DOB] matches TableB.[Concatenated First/DOB]
I'm new to Power BI and my assumption is that I'd need to add some combination of IF / OR statements on the Advanced Editor, but i'm not sure how to do this with M query language.
Here's what the M statement looks like on Advanced Editor when I merge both tables based on a single matching column:
= Table.NestedJoin(#"Added Custom", {"TableA.Client ID”}, #"XLS Export File (3)",{"TableB.Client ID" },"XLS Export File (3)",JoinKind.FullOuter
What additions would I have to add to my M statement in order to merge the two tables based on the criteria I listed above?
Also, I still want to keep all non-matching TableA Clients and TableB Clients on this newly merged table even though I know they would have all nulls on either the Table A Fields or Table B Fields. Am I correct in using the full outer option at the end of my M statement?
Thank you,
