Merge Columns based on one column from one table and two from the second

[Anonymous]

Hi,

I'm trying to merge two tables that has a unique id for each, but they do not match. I have three other columns that contain the unique id from the first table, but the ID differs on some because of different customers. Does anyone have a good solution to have to smoothly merge these two tables in power query? I want to join them so that I can get deal id to the first table.

Sample table 1:

id	name
1001	Store 1
1002	Store1
1003	Store 3
1004	Store 3
1005	Store 6
1006	Store 6
1007	Store 8
1008	Store 9
1009	Store 10
1010	Store 11
1011	Store 12
1012	Store 12
1013	Store 14
1014	Store 15
1015	Store 16
1016	Store 17
1017	Store 18
1018	Store 19
1019	Store 20
1020	Store 21
1021	Store 21
1022	Store 23
1023	Store 24
1024	Store 25
1025	Store 25
1026	Store 27
1027	Store 28
1028	Store 29
1029	Store 29

dealer	name	vehicleid	warranty id	customer id
1	Store 1	1002	1001	1001
3	Store 25			1024
4	Store 6	1006
10	Store 12			1012
11	Store 21	1021	1020	1020
29	Store 29	1028	1029	1029

[dax]

Hi PBISea,

It seems that you want to merge two tables, what is your expected output of merge result? If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

You are going to have to explain clearly how you would expect this join to work. If they both have an ID field, but they aren't the same ID, they won't join. Even a fuzzy merge won't work well here. That is for merging things like North Carolina and NorthCarolina.

You could create a Transformation Table for the fuzzy merge if you want to hand point ID1 to ID2. See this article for a really good overview of how that works.