Duplicate Value Error in Power Query Merge

[Hap76]

I'm simply denormalizing between a sales header and sales detail table. There are definitely no duplicate SalesOrder values in the header file (I've checked in mgmt studio as well). I am simply merging with, and then expanding the sales detail table. A very straightforward one-many merge. Yet for some reason I'm getting the following:

Column 'SalesOrder' in Table 'Merge1' contains a duplicate value '000000000033696' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
The current operation was cancelled because another operation in the transaction failed.

Even if I remove the apparently offending record from the table, it finds another random one (not the next record incidentally) to pick on. It seems like a bug. Has anyone seen this?

[KNP]

I've not seen this before but can you share your Power Query code please so we can see your steps?

Also, if you're certain the header table doesn't have or shouldn't have duplicates, select your key and 'remove duplicates' immediately prior to the merge.

Or add this code...

= Table.Distinct(PreviousStep, {"YourKey"})

[Hap76]

Thanks- yes I had tried (begrudgingly) removing duplicates with no change. It's really strange.

Here is the merge query:

let
	Source =
		Table.NestedJoin(
			#"Staging - SorMaster",
			{"SalesOrder"},
			#"Staging - SorDetail",
			{"SalesOrder"},
			"Staging - SorDetail",
			JoinKind.LeftOuter
		),

	#"Expanded Staging - SorDetail" =
		Table.ExpandTableColumn(
			Source,
			"Staging - SorDetail",
			{
				"SalesOrderLine",
				"LineType",
				"MStockCode",
				"MStockDes",
				"MWarehouse",
				"MBin",
				"MOrderQty",
				"MShipQty",
				"MBackOrderQty",
				"MUnitCost",
				"MBomFlag",
				"MParentKitType",
				"MQtyPer",
				"MScrapPercentage",
				"MPrintComponent",
				"MComponentSeq",
				"MQtyChangesFlag",
				"MOptionalFlag",
				"MDecimals",
				"MOrderUom",
				"MStockQtyToShp",
				"MStockingUom",
				"MConvFactOrdUm",
				"MMulDivPrcFct",
				"MPrice",
				"MPriceUom",
				"MCommissionCode",
				"MDiscPct1",
				"MDiscPct2",
				"MDiscPct3",
				"MDiscValFlag",
				"MDiscValue",
				"MProductClass",
				"MTaxCode",
				"MLineShipDate",
				"MAllocStatSched",
				"MFstTaxCode",
				"MStockUnitMass",
				"MStockUnitVol",
				"MPriceCode",
				"MConvFactAlloc",
				"MMulDivQtyFct",
				"MTraceableType",
				"MMpsFlag",
				"MPickingSlip",
				"MMovementReqd",
				"MSerialMethod",
				"MZeroQtyCrNote",
				"MAbcApplied",
				"MMpsGrossReqd",
				"MContract",
				"MBuyingGroup",
				"MCusSupStkCode",
				"MCusRetailPrice",
				"MTariffCode",
				"MLineReceiptDat",
				"MLeadTime",
				"MTrfCostMult",
				"MSupplementaryUn",
				"MReviewFlag",
				"MReviewStatus",
				"MInvoicePrinted",
				"MDelNotePrinted",
				"MOrdAckPrinted",
				"MHierarchyJob",
				"MCustRequestDat",
				"MLastDelNote",
				"MUserDef",
				"MQtyDispatched",
				"MDiscChanged",
				"MCreditOrderNo",
				"MCreditOrderLine",
				"MUnitQuantity",
				"MConvFactUnitQ",
				"MAltUomUnitQ",
				"MDecimalsUnitQ",
				"MEccFlag",
				"MVersion",
				"MRelease",
				"MCommitDate",
				"QtyReserved",
				"NComment",
				"NCommentFromLin",
				"NMscChargeValue",
				"NMscProductCls",
				"NMscChargeCost",
				"NMscInvCharge",
				"NCommentType",
				"NMscTaxCode",
				"NMscFstCode",
				"NCommentTextTyp",
				"NMscChargeQty",
				"NSrvIncTotal",
				"NSrvSummary",
				"NSrvChargeType",
				"NSrvParentLine",
				"NSrvUnitPrice",
				"NSrvUnitCost",
				"NSrvQtyFactor",
				"NSrvApplyFactor",
				"NSrvDecimalRnd",
				"NSrvDecRndFlag",
				"NSrvMinValue",
				"NSrvMaxValue",
				"NSrvMulDiv",
				"NPrtOnInv",
				"NPrtOnDel",
				"NPrtOnAck",
				"NTaxAmountFlag",
				"NDepRetFlagProj",
				"NRetentionJob",
				"NSrvMinQuantity",
				"NChargeCode",
				"IncludeInMrp",
				"ProductCode",
				"LibraryCode",
				"MaterialAllocLine",
				"ScrapQuantity",
				"FixedQtyPerFlag",
				"FixedQtyPer",
				"MultiShipCode",
				"User1",
				"CreditReason",
				"OrigShipDateAps",
				"TpmUsageFlag",
				"PromotionCode",
				"TpmSequence",
				"SalesOrderInitLine",
				"PreactorPriority",
				"SalesOrderDetStat",
				"SalesOrderResStat",
				"QtyReservedShip",
				"TimeStamp",
				"QtyReleasedToPick",
				"PickNumber"
			},
			{
				"Staging - SorDetail.SalesOrderLine",
				"Staging - SorDetail.LineType",
				"Staging - SorDetail.MStockCode",
				"Staging - SorDetail.MStockDes",
				"Staging - SorDetail.MWarehouse",
				"Staging - SorDetail.MBin",
				"Staging - SorDetail.MOrderQty",
				"Staging - SorDetail.MShipQty",
				"Staging - SorDetail.MBackOrderQty",
				"Staging - SorDetail.MUnitCost",
				"Staging - SorDetail.MBomFlag",
				"Staging - SorDetail.MParentKitType",
				"Staging - SorDetail.MQtyPer",
				"Staging - SorDetail.MScrapPercentage",
				"Staging - SorDetail.MPrintComponent",
				"Staging - SorDetail.MComponentSeq",
				"Staging - SorDetail.MQtyChangesFlag",
				"Staging - SorDetail.MOptionalFlag",
				"Staging - SorDetail.MDecimals",
				"Staging - SorDetail.MOrderUom",
				"Staging - SorDetail.MStockQtyToShp",
				"Staging - SorDetail.MStockingUom",
				"Staging - SorDetail.MConvFactOrdUm",
				"Staging - SorDetail.MMulDivPrcFct",
				"Staging - SorDetail.MPrice",
				"Staging - SorDetail.MPriceUom",
				"Staging - SorDetail.MCommissionCode",
				"Staging - SorDetail.MDiscPct1",
				"Staging - SorDetail.MDiscPct2",
				"Staging - SorDetail.MDiscPct3",
				"Staging - SorDetail.MDiscValFlag",
				"Staging - SorDetail.MDiscValue",
				"Staging - SorDetail.MProductClass",
				"Staging - SorDetail.MTaxCode",
				"Staging - SorDetail.MLineShipDate",
				"Staging - SorDetail.MAllocStatSched",
				"Staging - SorDetail.MFstTaxCode",
				"Staging - SorDetail.MStockUnitMass",
				"Staging - SorDetail.MStockUnitVol",
				"Staging - SorDetail.MPriceCode",
				"Staging - SorDetail.MConvFactAlloc",
				"Staging - SorDetail.MMulDivQtyFct",
				"Staging - SorDetail.MTraceableType",
				"Staging - SorDetail.MMpsFlag",
				"Staging - SorDetail.MPickingSlip",
				"Staging - SorDetail.MMovementReqd",
				"Staging - SorDetail.MSerialMethod",
				"Staging - SorDetail.MZeroQtyCrNote",
				"Staging - SorDetail.MAbcApplied",
				"Staging - SorDetail.MMpsGrossReqd",
				"Staging - SorDetail.MContract",
				"Staging - SorDetail.MBuyingGroup",
				"Staging - SorDetail.MCusSupStkCode",
				"Staging - SorDetail.MCusRetailPrice",
				"Staging - SorDetail.MTariffCode",
				"Staging - SorDetail.MLineReceiptDat",
				"Staging - SorDetail.MLeadTime",
				"Staging - SorDetail.MTrfCostMult",
				"Staging - SorDetail.MSupplementaryUn",
				"Staging - SorDetail.MReviewFlag",
				"Staging - SorDetail.MReviewStatus",
				"Staging - SorDetail.MInvoicePrinted",
				"Staging - SorDetail.MDelNotePrinted",
				"Staging - SorDetail.MOrdAckPrinted",
				"Staging - SorDetail.MHierarchyJob",
				"Staging - SorDetail.MCustRequestDat",
				"Staging - SorDetail.MLastDelNote",
				"Staging - SorDetail.MUserDef",
				"Staging - SorDetail.MQtyDispatched",
				"Staging - SorDetail.MDiscChanged",
				"Staging - SorDetail.MCreditOrderNo",
				"Staging - SorDetail.MCreditOrderLine",
				"Staging - SorDetail.MUnitQuantity",
				"Staging - SorDetail.MConvFactUnitQ",
				"Staging - SorDetail.MAltUomUnitQ",
				"Staging - SorDetail.MDecimalsUnitQ",
				"Staging - SorDetail.MEccFlag",
				"Staging - SorDetail.MVersion",
				"Staging - SorDetail.MRelease",
				"Staging - SorDetail.MCommitDate",
				"Staging - SorDetail.QtyReserved",
				"Staging - SorDetail.NComment",
				"Staging - SorDetail.NCommentFromLin",
				"Staging - SorDetail.NMscChargeValue",
				"Staging - SorDetail.NMscProductCls",
				"Staging - SorDetail.NMscChargeCost",
				"Staging - SorDetail.NMscInvCharge",
				"Staging - SorDetail.NCommentType",
				"Staging - SorDetail.NMscTaxCode",
				"Staging - SorDetail.NMscFstCode",
				"Staging - SorDetail.NCommentTextTyp",
				"Staging - SorDetail.NMscChargeQty",
				"Staging - SorDetail.NSrvIncTotal",
				"Staging - SorDetail.NSrvSummary",
				"Staging - SorDetail.NSrvChargeType",
				"Staging - SorDetail.NSrvParentLine",
				"Staging - SorDetail.NSrvUnitPrice",
				"Staging - SorDetail.NSrvUnitCost",
				"Staging - SorDetail.NSrvQtyFactor",
				"Staging - SorDetail.NSrvApplyFactor",
				"Staging - SorDetail.NSrvDecimalRnd",
				"Staging - SorDetail.NSrvDecRndFlag",
				"Staging - SorDetail.NSrvMinValue",
				"Staging - SorDetail.NSrvMaxValue",
				"Staging - SorDetail.NSrvMulDiv",
				"Staging - SorDetail.NPrtOnInv",
				"Staging - SorDetail.NPrtOnDel",
				"Staging - SorDetail.NPrtOnAck",
				"Staging - SorDetail.NTaxAmountFlag",
				"Staging - SorDetail.NDepRetFlagProj",
				"Staging - SorDetail.NRetentionJob",
				"Staging - SorDetail.NSrvMinQuantity",
				"Staging - SorDetail.NChargeCode",
				"Staging - SorDetail.IncludeInMrp",
				"Staging - SorDetail.ProductCode",
				"Staging - SorDetail.LibraryCode",
				"Staging - SorDetail.MaterialAllocLine",
				"Staging - SorDetail.ScrapQuantity",
				"Staging - SorDetail.FixedQtyPerFlag",
				"Staging - SorDetail.FixedQtyPer",
				"Staging - SorDetail.MultiShipCode",
				"Staging - SorDetail.User1",
				"Staging - SorDetail.CreditReason",
				"Staging - SorDetail.OrigShipDateAps",
				"Staging - SorDetail.TpmUsageFlag",
				"Staging - SorDetail.PromotionCode",
				"Staging - SorDetail.TpmSequence",
				"Staging - SorDetail.SalesOrderInitLine",
				"Staging - SorDetail.PreactorPriority",
				"Staging - SorDetail.SalesOrderDetStat",
				"Staging - SorDetail.SalesOrderResStat",
				"Staging - SorDetail.QtyReservedShip",
				"Staging - SorDetail.TimeStamp",
				"Staging - SorDetail.QtyReleasedToPick",
				"Staging - SorDetail.PickNumber"
			}
		)
in
	#"Expanded Staging - SorDetail"

[KNP]

So we can do some more troubleshooting, could you try deleting your relationships temporarily and perhaps check out these settings.

The auto creating and updating relationships has caused me issues in the past.

Then you may be able to better figure out what is going on.

Bonus tip (not related to your issue, please ignore if you're not interested):

So you don't have to deal with the extreme verbosity that PBI creates when expanding columns and to make it more dynamic, you can use this method (PBIX demo attached). Alter to suit your query obviously, e.g. replace "financials" with "Staging - SorDetail" and step names etc.

...
 #"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"financials"}),
 ColumnNames = Table.ColumnNames(Table.Combine(#"Removed Other Columns"[financials])),
 #"Expanded financials" = Table.ExpandTableColumn(#"Removed Other Columns", "financials", ColumnNames, ColumnNames)
in
 #"Expanded financials"

[Hap76 · Accepted solution]

Thanks for your suggestions. Somehow the issue seems to have resolved itself, although it's not clear how. I built up a new dummy set in a new workbook and merged them (just to make sure I wasn't crazy) worked no problem. I went back to my dataset and started restricting down - this year, then this week. Each time it picked a random salesorder to accuse of being a duplicate. At one point I unchecked 'Add to Data Model', saved, and then re-checked it. It merged. So, I'm going to chalk it up to something glichy in Power Pivot so as not be driven to the drink.