Error when connecting Tables

[Anonymous]

Hi When I am trying to connect two tables together I am getting the error message

Feedback Type:
Frown (Error)

Error Message:
Failed to save modifications to the server. Error returned: 'Relationship 'ec5e8cac-c563-4d3d-a0d8-9c414d81f293' between columns 'Cl_details[Dealer_no]' and 'Dealers[Dealer_no]' has the TO end cardinality set to Many. TO end cardinality must always be set to One.
'.

Stack Trace:
at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveOptions saveOptions)
at Microsoft.PowerBI.Modeler.Relationships.CreateRelationship(Column fromColumn, Column toColumn, Boolean isActiveSuggestion, Boolean isAutoCrossFiltering, Boolean isAutoDirection, Boolean referentialIntegrity, Nullable`1 fromCardinality, Nullable`1 toCardinality, Nullable`1 crossFilteringBehavior, Nullable`1 securityFilteringBehavior)
at Microsoft.PowerBI.Modeler.ModelManager.CreateRelationship(Column fromColumn, Column toColumn, Boolean isActiveSuggestion, Boolean isAutoCrossFiltering, Boolean isAutoDirection, Boolean referentialIntegrity, Nullable`1 fromCardinality, Nullable`1 toCardinality, Nullable`1 crossFilteringBehavior, Nullable`1 securityFilteringBehavior)
at Microsoft.PowerBI.Client.Windows.Modeling.SchemaChangeApplier.Visit(CreateRelationshipSchemaChange schemaChange)
at Microsoft.PowerBI.Client.Windows.Modeling.SchemaChangeApplier.Apply(IModelManager modelManager, IReportManager reportManager, IEnumerable`1 changes)
at Microsoft.PowerBI.Client.Windows.Modeling.ModelAuthoringExceptionHandler.AddModelChangeResultToExceptions(Action action, ModelChangeResult modelChangeResult)
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.ModelAuthoringTransaction.ExecuteChange(ModelChange modelChange)
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.ExtendedModelChangeScopeBase.ExecuteChange(ModelChange modelChange)
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.<>c__DisplayClassf.<RunExtendedModelChangeAsync>b__e()
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__3.MoveNext()

Stack Trace Message:
Failed to save modifications to the server. Error returned: 'Relationship 'ec5e8cac-c563-4d3d-a0d8-9c414d81f293' between columns 'Cl_details[Dealer_no]' and 'Dealers[Dealer_no]' has the TO end cardinality set to Many. TO end cardinality must always be set to One.
'.

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3.<SendAndMarshalExceptions>b__0(Object null)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
at System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state)
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__3.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
at System.Windows.Threading.DispatcherOperation.InvokeImpl()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at MS.Internal.CulturePreservingExecutionContext.Run(CulturePreservingExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Threading.DispatcherOperation.Invoke()
at System.Windows.Threading.Dispatcher.ProcessQueue()
at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__FabricatedMethod8(IWindowHandle )
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClassb.<Main>b__0()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass7.<HandleExceptionsWithNestedTasks>b__6()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\cjack\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot118581042.zip

Performance Trace Logs:
C:\Users\cjack\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_newFromWeb
PBI_qnaExplore
PBI_canvasTooltips

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
CustomConnectors
AdobeAnalytics_UseHierarchicalNavigation
PBI_variationUIChange
PBI_showIncrementalRefreshPolicy

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

ModelChangeResult:
{"changes":[{"relationshipCreate":{"isActiveSuggestion":true,"isAutoCrossFilteringBehavior":true,"referentialIntegrity":false,"isAutoDirection":true,"fromTableName":"Cl_details","toTableName":"Dealers","fromColumnName":"Dealer_no","toColumnName":"Dealer_no"}}],"hasQueryImpact":true}

ModelChangeStartingStackTrace:
at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
at System.Environment.get_StackTrace()
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.RunExtendedModelChangeAsync(Action`1 extendedModelChange, CancellationToken cancellationToken, Boolean blockUI)
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.ExecuteSingleAsync(ModelChange change, CancellationToken cancellationToken, Boolean blockUI)
at Microsoft.PowerBI.Client.Windows.SchemaChangeRoutingVisitor.<>c__DisplayClass52.<RouteCore>b__4d()
at Microsoft.PowerBI.Client.Windows.Modeling.ModelingTelemetryHelper.ExecuteAndLogModelChangeEvent(ModelChange value, EditorView source, IPowerBITelemetryService telemetryService, Func`1 modelChangeFunc)
at Microsoft.PowerBI.Client.Windows.SchemaChangeRoutingVisitor.RouteCore(ModelChange modelChange)
at Microsoft.PowerBI.Client.Windows.Modeling.ModelAuthoringRoutingService.<>c__DisplayClass4.<RouteAsync>b__3(SchemaChangeRoutingVisitor visitor)
at Microsoft.PowerBI.Client.Windows.Modeling.ModelAuthoringRoutingService.UseSchemaChangeRoutingVisitor[T](Func`2 useVisitor)
at Microsoft.PowerBI.Client.Windows.Modeling.ModelAuthoringRoutingService.RouteAsync(ModelChange change, CancellationToken cancellationToken, Boolean blockUI)
at Microsoft.PowerBI.Client.Windows.Commands.ApplicationCommands.ModelingCommands.CreateRelationship(IExceptionHandler exceptionHandler, CreateRelationshipCommandParameter relationshipParameters)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__3.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAwaitableAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAsyncExceptions>d__0.MoveNext()
at System.Runtime.CompilerServices.AsyncVoidMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
at Microsoft.PowerBI.Client.Windows.Commands.CommandFactory.<>c__DisplayClass24`1.<FromAsyncHandlers>b__21(CommandParameter`1 p)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.<>c__DisplayClass2.<.ctor>b__0(Object arg)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.<Execute>d__7.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.Execute(Object parameter)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.<System.Windows.Input.ICommand.Execute>d__4.MoveNext()
at System.Runtime.CompilerServices.AsyncVoidMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.System.Windows.Input.ICommand.Execute(Object parameter)
at Microsoft.Practices.Prism.Commands.CompositeCommand.Execute(Object parameter)
at Microsoft.DiagramView.Diagram.Visual.ERDiagram.ERDiagramActionCreateRelationship.<>c__DisplayClass30.<Do>b__2e()
at Microsoft.PowerBI.Client.Windows.AnalysisServices.Relationships.RelationshipExceptionHelpers.ConvertOperationExceptions(Action action)
at Microsoft.DiagramView.Diagram.Visual.ERDiagram.ERDiagramActionCreateRelationship.Do(IDiagramActionInstance actionInstance)
at Microsoft.DiagramView.Diagram.Visual.DiagramDisplay.ExecuteActionDoPhase(String& futureObjectToFocusDiagramKey)
at Microsoft.DiagramView.Diagram.Visual.DiagramDisplay.ContinueExistingAction(DiagramActionPhase targetPhase, String& futureObjectToFocusDiagramKey)
at Microsoft.DiagramView.Diagram.Visual.DiagramDisplay.AdvanceAction(IDiagramActionInstance actionInstance, DiagramActionPhase targetPhase)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.DiagramView.Diagram.DragDrop.ItemsControlDragDropHelper.DropTarget_PreviewDrop(Object sender, DragEventArgs e)
at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
at System.Windows.OleDropTarget.RaiseDragEvent(RoutedEvent dragEvent, Int32 dragDropKeyStates, Int32& effects, DependencyObject target, Point targetPoint)
at System.Windows.OleDropTarget.MS.Win32.UnsafeNativeMethods.IOleDropTarget.OleDrop(Object data, Int32 dragDropKeyStates, Int64 point, Int32& effects)
at MS.Win32.UnsafeNativeMethods.DoDragDrop(IDataObject dataObject, IOleDropSource dropSource, Int32 allowedEffects, Int32[] finalEffect)
at MS.Win32.UnsafeNativeMethods.DoDragDrop(IDataObject dataObject, IOleDropSource dropSource, Int32 allowedEffects, Int32[] finalEffect)
at System.Windows.OleServicesContext.OleDoDragDrop(IDataObject dataObject, IOleDropSource dropSource, Int32 allowedEffects, Int32[] finalEffect)
at System.Windows.DragDrop.OleDoDragDrop(DependencyObject dragSource, DataObject dataObject, DragDropEffects allowedEffects)
at System.Windows.DragDrop.DoDragDrop(DependencyObject dragSource, Object data, DragDropEffects allowedEffects)
at Microsoft.DiagramView.Diagram.DragDrop.ItemsControlDragDropHelper.DragSource_PreviewMouseMove(Object sender, MouseEventArgs e)
at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)
at System.Windows.Input.InputManager.ProcessStagingArea()
at System.Windows.Input.InputManager.ProcessInput(InputEventArgs input)
at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)
at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)
at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__FabricatedMethod8(IWindowHandle )
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClassb.<Main>b__0()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass7.<HandleExceptionsWithNestedTasks>b__6()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

Formulas:

section Section1;

shared tb_Pers = let
Source = Access.Database(File.Contents("Y:\ecm_hol.mdb"), [CreateNavigationProperties=true]),
_tb_Pers = Source{[Schema="",Item="tb_Pers"]}[Data]
in
_tb_Pers;

shared Cl_details = let
Source = Access.Database(File.Contents("C:\Users\cjack\Desktop\ecm_clm.mdb"), [CreateNavigationProperties=true]),
_Cl_details = Source{[Schema="",Item="Cl_details"]}[Data]
in
_Cl_details;

shared Cl_Driver = let
Source = Access.Database(File.Contents("C:\Users\cjack\Desktop\ecm_clm.mdb"), [CreateNavigationProperties=true]),
_Cl_Driver = Source{[Schema="",Item="Cl_Driver"]}[Data]
in
_Cl_Driver;

shared Da_inv = let
Source = Access.Database(File.Contents("C:\Users\cjack\Desktop\ecm_clm.mdb"), [CreateNavigationProperties=true]),
_Da_inv = Source{[Schema="",Item="Da_inv"]}[Data]
in
_Da_inv;

shared Dealers = let
Source = Access.Database(File.Contents("C:\Users\cjack\Desktop\ecm_clm.mdb"), [CreateNavigationProperties=true]),
_Dealers = Source{[Schema="",Item="Dealers"]}[Data],
#"Removed Duplicates" = Table.Distinct(_Dealers, {"Dealer_no"})
in
#"Removed Duplicates";

Any Help Appreciated.

[v-yuezhe-msft]

@Anonymous,

Based on the above error message, there are some issues in the relationship between Cl_details and Dealers tables. 

Could you please sample data in 'Cl_details[Dealer_no]' field and 'Dealers[Dealer_no] field? Do each field contain unique values? Which table is Many side of the relationship and which table is One side of the relationship?

Regards,
Lydia

[Anonymous]

Hi Lydia,

In my power query i have removed duplicate rows on the dealers No.

The many is CL_Details and the one is Dealer table.

HTH Thanks

Chris

[v-yuezhe-msft]

@Anonymous，

What is the result if you disable the following options when you importing data?

Regards,
Lydia

[Anonymous]

Hi Lydia, 

Just checked the values and it seem that the dealer no field on the cl details table does not have any values in, so it is trying to connect null.

Thanks for help.

[Anonymous]

Actually it is still doing it with a different column, I have unticked the boxes you suggest and I am still getting the error.  There is value in it so it is not all null.

Could it be that there are mulitples nulls in each column?

Feedback Type:
Frown (Error)

Error Message:
Failed to save modifications to the server. Error returned: 'Relationship '4b8cc42c-a421-40c6-8d09-fd36ac59ee24' between columns 'Cl_details[cldeta_dealer0]' and 'Dealers[Dealer_no]' has the TO end cardinality set to Many. TO end cardinality must always be set to One.

[v-yuezhe-msft]

@Anonymous,

What is the result if you remove nulls from your table? And could you please share sample data of your tables here so that we can test?

Regards,
Lydia