How to Marge Multiple Table into One Table Without data duplication

[Anonymous]

Hi,

When I Merge or append to combine all tables into one table, then data duplicate one-row repeat Multiple time.

let
Source = Table.NestedJoin(Location_Details, {"Location_Id"}, PMSR_Profit, {"Location_ID"}, "PMSR_Profit", JoinKind.LeftOuter),
#"Expanded PMSR_Profit" = Table.ExpandTableColumn(Source, "PMSR_Profit", {"Date", "PMSR_P_ID", "(P) Credit Applications", "(P) Total Activations", "(P) Prime Activations", "(P) Sub-Prime Activations", "(P) Prime AAL Activations", "(P) Sub-Prime AAL Activations", "(P) Prime MI Activations", "(P) Sub-Prime MI Activations", "(P) Prepaid Voice Activations", "(P) Prepaid MI Activations", "(P) Total TMFB Activations", "(P) Total PostPaid Activations", "(P) TMFB Voice Activations", "(P) TMFB MI Activations", "(P) LineLink Activations", "(P) Upgrades"}, {"PMSR_Profit.Date", "PMSR_Profit.PMSR_P_ID", "PMSR_Profit.(P) Credit Applications", "PMSR_Profit.(P) Total Activations", "PMSR_Profit.(P) Prime Activations", "PMSR_Profit.(P) Sub-Prime Activations", "PMSR_Profit.(P) Prime AAL Activations", "PMSR_Profit.(P) Sub-Prime AAL Activations", "PMSR_Profit.(P) Prime MI Activations", "PMSR_Profit.(P) Sub-Prime MI Activations", "PMSR_Profit.(P) Prepaid Voice Activations", "PMSR_Profit.(P) Prepaid MI Activations", "PMSR_Profit.(P) Total TMFB Activations", "PMSR_Profit.(P) Total PostPaid Activations", "PMSR_Profit.(P) TMFB Voice Activations", "PMSR_Profit.(P) TMFB MI Activations", "PMSR_Profit.(P) LineLink Activations", "PMSR_Profit.(P) Upgrades"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded PMSR_Profit", {"Location_Id"}, PMSR_Quanity, {"Location_ID"}, "PMSR_Quanity", JoinKind.LeftOuter),
#"Expanded PMSR_Quanity" = Table.ExpandTableColumn(#"Merged Queries", "PMSR_Quanity", {"PMSR_Q_ID", "(Q) Credit Applications", "(Q) Total Activations", "(Q) Prime Activations", "(Q) Sub-Prime Activations", "(Q) Prime AAL Activations", "(Q) Sub-Prime AAL Activations", "(Q) Prime MI Activations", "(Q) Sub-Prime MI Activations", "(Q) NCC Activations", "(Q) Prepaid Voice Activations", "(Q) Prepaid MI Activations", "(Q) Total TMFB Activations", "(Q) Total PostPaid Activations", "(Q) TMFB Voice Activations", "(Q) TMFB MI Activations", "(Q) LineLink Activations", "(Q) Handset Spiff (0.5 x MRC)", "(Q) Upgrades", "(Q) Total Features", "(Q) COE", "(Q) Feature Only Sales (Total)", "(Q) JUMP Feature", "(Q) Accessories", "(Q) FOS - Prime", "(Q) FOS - Sub-Prime", "(Q) FOS - After 120 Days", "(Q) Trade-In", "(Q) SCORE! (Prepaid)", "(Q) SCORE! (SCNC)", "(Q) SCORE! (Prime)", "(Q) SCORE! (Sub-Prime)", "(Q) SCORE! (FOS)", "(Q) Simply Prepaid ($25)", "(Q) Simply Prepaid ($40, $50, $60)", "(Q) SC Pay in Advance", "(Q) TMFB Data Features", "(Q) TMFB Leads", "(Q) US/CAN/MEX ROAMING FOS", "(Q) 20GB DATA FOR $10", "(Q) JOD Activations", "(Q) JOD Upgrades", "(Q) Paid Data", "(Q) Unlimited Data", "(Q) Paid Data ($10+ MRC)", "(Q) Paid Data ($20+ MRC)", "(Q) Paid Data ($30+ MRC)", "(Q) Paid Data ($40+ MRC)", "(Q) Total Boxes", "(Q) PHP", "(Q) M4G (Pay in Advance) Plans", "(Q) Simply Prepaid Plans", "(Q) Total GP", "(Q) Total Accessories", "(Q) Accessories - Cases", "(Q) Accessories - Chargers", "(Q) Accessories - Audio", "(Q) Accessories - BT Earpieces", "(Q) Accessories - Memory Cards", "(Q) Accessories - Miscellaneous", "(Q) Accessories - Wearables", "(Q) Accessories - Screen Protectors", "(Q) Total Accessories - Categories", "(Q) T-Mobile One First Line Activations ($70)", "(Q) T-Mobile One Second Line Activations ($50)", "(Q) T-Mobile One AAL Activations ($20, $30 & $40)", "(Q) T-Mobile One MI Activations ($20, $60, $70)", "(Q) T-Mobile One Wearable Activations ($5, $15)", "(Q) T-Mobile One TMFB First Line Activations ($70)", "(Q) T-Mobile One TMFB Second Line Activations ($50)", "(Q) T-Mobile One TMFB AAL ($20, $30 & $40)", "(Q) T-Mobile One TMFB MI Activations ($20, $60, $70)", "(Q) T-Mobile One TMFB Wearable Activations ($5, $15)", "(Q) Total T-Mobile One (Voice) Activations", "(Q) One+ International ($25) - Acquisition Only", "(Q) One+ ($10) - Acquisition Only", "(Q) iPhone 8 Direct Ship", "(Q) iPhone 8+ Direct Ship", "(Q) Apple Watch 4 - Direct Ship", "(Q) TOTAL DEVICES", "(Q) SYNCUP DEVICE SALES", "(Q) T-Mobile One Promo 2 Lines for $100", "(Q) T-Mobile One @Work Promo 2 Lines for $100", "(Q) SmartPick Handsets", "(Q) Samsung S9 Direct Ship", "(Q) Samsung S9+ Direct Ship", "(Q) Motorola Z2 Direct Ship", "(Q) TOTAL MI (IOT) - BEYOND THE SMARTPHONE", "(Q) iPhone Xs Direct Ship", "(Q) Samsung Note 9 Direct Ship", "(Q) Prepaid Voice Activations 2", "(Q) total acts", "(Q) NEST SALES", "(Q) APPLE WATCH 3 SALES", "(Q) SAMSUNG WATCH SALES", "(Q) DIGITS MI", "(Q) T-Mobile One Military 1st Line Activations ($55)", "(Q) T-Mobile One Military 2nd Line Activations ($25)", "(Q) T-Mobile One Military 3-6 Line Activations ($10)", "(Q) T-Mobile One Military 7-8 Line Activations ($20)", "(Q) T-Mobile One Military 9-12 Line Activations ($25)", "(Q) SYNCUP ACTIVATIONS", "(Q) 55+ Plan Acts", "(Q) T-Mobile One Military Acts", "(Q) Rebellion (Knox Only) Prepaid", "(Q) FamilyMode Features - Acquisition", "(Q) FamilyMode Features - Added Later", "(Q) T-Mobile Essentials (Postpaid)", "(Q) T-Mobile One w/ One+ Family", "(Q) One+ ($15) - Acquisition Only", "(Q) TMFB T-Mobile One w/ One+ Family", "(Q) TMFB T-Mobile Essentials", "(Q) Layer3 In-Store Activations", "(Q) Layer3 Salesforce Leads", "(Q) Protection 360 Add-ons", "(Q) iPhone Xs Max Direct Ship", "(Q) Apple Watch 4 Direct Ship", "(Q) iPhone Xr Direct Ship", "(Q) Global Plus ONE ($50)", "(Q) Samsung S10e Direct Ship", "(Q) Samsung S10 Direct Ship", "(Q) Samsung S10+ Direct Ship"}, {"PMSR_Quanity.PMSR_Q_ID", "PMSR_Quanity.(Q) Credit Applications", "PMSR_Quanity.(Q) Total Activations", "PMSR_Quanity.(Q) Prime Activations", "PMSR_Quanity.(Q) Sub-Prime Activations", "PMSR_Quanity.(Q) Prime AAL Activations", "PMSR_Quanity.(Q) Sub-Prime AAL Activations", "PMSR_Quanity.(Q) Prime MI Activations", "PMSR_Quanity.(Q) Sub-Prime MI Activations", "PMSR_Quanity.(Q) NCC Activations", "PMSR_Quanity.(Q) Prepaid Voice Activations", "PMSR_Quanity.(Q) Prepaid MI Activations", "PMSR_Quanity.(Q) Total TMFB Activations", "PMSR_Quanity.(Q) Total PostPaid Activations", "PMSR_Quanity.(Q) TMFB Voice Activations", "PMSR_Quanity.(Q) TMFB MI Activations", "PMSR_Quanity.(Q) LineLink Activations", "PMSR_Quanity.(Q) Handset Spiff (0.5 x MRC)", "PMSR_Quanity.(Q) Upgrades", "PMSR_Quanity.(Q) Total Features", "PMSR_Quanity.(Q) COE", "PMSR_Quanity.(Q) Feature Only Sales (Total)", "PMSR_Quanity.(Q) JUMP Feature", "PMSR_Quanity.(Q) Accessories", "PMSR_Quanity.(Q) FOS - Prime", "PMSR_Quanity.(Q) FOS - Sub-Prime", "PMSR_Quanity.(Q) FOS - After 120 Days", "PMSR_Quanity.(Q) Trade-In", "PMSR_Quanity.(Q) SCORE! (Prepaid)", "PMSR_Quanity.(Q) SCORE! (SCNC)", "PMSR_Quanity.(Q) SCORE! (Prime)", "PMSR_Quanity.(Q) SCORE! (Sub-Prime)", "PMSR_Quanity.(Q) SCORE! (FOS)", "PMSR_Quanity.(Q) Simply Prepaid ($25)", "PMSR_Quanity.(Q) Simply Prepaid ($40, $50, $60)", "PMSR_Quanity.(Q) SC Pay in Advance", "PMSR_Quanity.(Q) TMFB Data Features", "PMSR_Quanity.(Q) TMFB Leads", "PMSR_Quanity.(Q) US/CAN/MEX ROAMING FOS", "PMSR_Quanity.(Q) 20GB DATA FOR $10", "PMSR_Quanity.(Q) JOD Activations", "PMSR_Quanity.(Q) JOD Upgrades", "PMSR_Quanity.(Q) Paid Data", "PMSR_Quanity.(Q) Unlimited Data", "PMSR_Quanity.(Q) Paid Data ($10+ MRC)", "PMSR_Quanity.(Q) Paid Data ($20+ MRC)", "PMSR_Quanity.(Q) Paid Data ($30+ MRC)", "PMSR_Quanity.(Q) Paid Data ($40+ MRC)", "PMSR_Quanity.(Q) Total Boxes", "PMSR_Quanity.(Q) PHP", "PMSR_Quanity.(Q) M4G (Pay in Advance) Plans", "PMSR_Quanity.(Q) Simply Prepaid Plans", "PMSR_Quanity.(Q) Total GP", "PMSR_Quanity.(Q) Total Accessories", "PMSR_Quanity.(Q) Accessories - Cases", "PMSR_Quanity.(Q) Accessories - Chargers", "PMSR_Quanity.(Q) Accessories - Audio", "PMSR_Quanity.(Q) Accessories - BT Earpieces", "PMSR_Quanity.(Q) Accessories - Memory Cards", "PMSR_Quanity.(Q) Accessories - Miscellaneous", "PMSR_Quanity.(Q) Accessories - Wearables", "PMSR_Quanity.(Q) Accessories - Screen Protectors", "PMSR_Quanity.(Q) Total Accessories - Categories", "PMSR_Quanity.(Q) T-Mobile One First Line Activations ($70)", "PMSR_Quanity.(Q) T-Mobile One Second Line Activations ($50)", "PMSR_Quanity.(Q) T-Mobile One AAL Activations ($20, $30 & $40)", "PMSR_Quanity.(Q) T-Mobile One MI Activations ($20, $60, $70)", "PMSR_Quanity.(Q) T-Mobile One Wearable Activations ($5, $15)", "PMSR_Quanity.(Q) T-Mobile One TMFB First Line Activations ($70)", "PMSR_Quanity.(Q) T-Mobile One TMFB Second Line Activations ($50)", "PMSR_Quanity.(Q) T-Mobile One TMFB AAL ($20, $30 & $40)", "PMSR_Quanity.(Q) T-Mobile One TMFB MI Activations ($20, $60, $70)", "PMSR_Quanity.(Q) T-Mobile One TMFB Wearable Activations ($5, $15)", "PMSR_Quanity.(Q) Total T-Mobile One (Voice) Activations", "PMSR_Quanity.(Q) One+ International ($25) - Acquisition Only", "PMSR_Quanity.(Q) One+ ($10) - Acquisition Only", "PMSR_Quanity.(Q) iPhone 8 Direct Ship", "PMSR_Quanity.(Q) iPhone 8+ Direct Ship", "PMSR_Quanity.(Q) Apple Watch 4 - Direct Ship", "PMSR_Quanity.(Q) TOTAL DEVICES", "PMSR_Quanity.(Q) SYNCUP DEVICE SALES", "PMSR_Quanity.(Q) T-Mobile One Promo 2 Lines for $100", "PMSR_Quanity.(Q) T-Mobile One @Work Promo 2 Lines for $100", "PMSR_Quanity.(Q) SmartPick Handsets", "PMSR_Quanity.(Q) Samsung S9 Direct Ship", "PMSR_Quanity.(Q) Samsung S9+ Direct Ship", "PMSR_Quanity.(Q) Motorola Z2 Direct Ship", "PMSR_Quanity.(Q) TOTAL MI (IOT) - BEYOND THE SMARTPHONE", "PMSR_Quanity.(Q) iPhone Xs Direct Ship", "PMSR_Quanity.(Q) Samsung Note 9 Direct Ship", "PMSR_Quanity.(Q) Prepaid Voice Activations 2", "PMSR_Quanity.(Q) total acts", "PMSR_Quanity.(Q) NEST SALES", "PMSR_Quanity.(Q) APPLE WATCH 3 SALES", "PMSR_Quanity.(Q) SAMSUNG WATCH SALES", "PMSR_Quanity.(Q) DIGITS MI", "PMSR_Quanity.(Q) T-Mobile One Military 1st Line Activations ($55)", "PMSR_Quanity.(Q) T-Mobile One Military 2nd Line Activations ($25)", "PMSR_Quanity.(Q) T-Mobile One Military 3-6 Line Activations ($10)", "PMSR_Quanity.(Q) T-Mobile One Military 7-8 Line Activations ($20)", "PMSR_Quanity.(Q) T-Mobile One Military 9-12 Line Activations ($25)", "PMSR_Quanity.(Q) SYNCUP ACTIVATIONS", "PMSR_Quanity.(Q) 55+ Plan Acts", "PMSR_Quanity.(Q) T-Mobile One Military Acts", "PMSR_Quanity.(Q) Rebellion (Knox Only) Prepaid", "PMSR_Quanity.(Q) FamilyMode Features - Acquisition", "PMSR_Quanity.(Q) FamilyMode Features - Added Later", "PMSR_Quanity.(Q) T-Mobile Essentials (Postpaid)", "PMSR_Quanity.(Q) T-Mobile One w/ One+ Family", "PMSR_Quanity.(Q) One+ ($15) - Acquisition Only", "PMSR_Quanity.(Q) TMFB T-Mobile One w/ One+ Family", "PMSR_Quanity.(Q) TMFB T-Mobile Essentials", "PMSR_Quanity.(Q) Layer3 In-Store Activations", "PMSR_Quanity.(Q) Layer3 Salesforce Leads", "PMSR_Quanity.(Q) Protection 360 Add-ons", "PMSR_Quanity.(Q) iPhone Xs Max Direct Ship", "PMSR_Quanity.(Q) Apple Watch 4 Direct Ship", "PMSR_Quanity.(Q) iPhone Xr Direct Ship", "PMSR_Quanity.(Q) Global Plus ONE ($50)", "PMSR_Quanity.(Q) Samsung S10e Direct Ship", "PMSR_Quanity.(Q) Samsung S10 Direct Ship", "PMSR_Quanity.(Q) Samsung S10+ Direct Ship"})
in
#"Expanded PMSR_Quanity"

[v-juanli-msft]

Hi @Anonymous 

If i merge these two tables

quantity

location id	data1	data2
1	a	1
1	b	2
2	c	3
3	d	4
4	e	5
4	f	6

profit

location id	data3	data4
1	a	1
1	b	2
1	c	3
2	d	4
2	e	5
3	f	6
3	a	7
3	b	8
4	c	9

As there are duplication in "location id' in each table, so it can't detemine which id they match each other.

Best Regards
Maggie

[Anonymous]

Thanks, @v-juanli-msft FOr Reply.

IF I want to Combine all table so how can I merge. Any Solution?