Forum Discussion
Solved
Duplicate Value Error in Power Query Merge
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 mergi...
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.
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"
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"
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.