error simply referencing a DirectQuery table: "Unable to convert an M query in table..."

[LastThird]

When I simply reference a DirectQuery table, then attempt to apply the change, I get error: "Failed to save modifications to the server. Error returned: 'Unable to convert an M query in table... into a native source query'."

At the same moment as this, it also throws "Object reference not set to an instance of an object." (for which I have the error details, stack trace, snapshots, etc.). The instructions in PowerBI Desktop indicated that I should submit all those details here, but I don't see a way to attach them, so when you reach out to me, we can figure out a way for me to get them to you.

This is in preparation for building an agg table based on a DirectQuery fact table, so this seems to be a total blocker.

[LastThird]

in lieu of figuring out some way to upload attachments, here's the stack trace:

Feedback Type:
Frown (Error)

Error Message:
Object reference not set to an instance of an object.

Stack Trace:
at Microsoft.PowerBI.Client.Windows.Modeling.DataModelExtensions.GetDirectQueryResourceInfo(ITable table, Report report)
at Microsoft.PowerBI.Client.Windows.QueryFolding.FoldedArtifactsGenerator.GetDQResource(Report report, String tableName)
at Microsoft.PowerBI.Client.Windows.QueryFolding.FoldedArtifactsGenerator.TryGetFoldedArtifacts(Report report, String tableName, IReadOnlyDictionary`2 queryDependencyGraph, FoldedArtifacts& artifacts)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesCallbackHelper.TryGetFoldedArtifacts(Report report, IReadOnlyDictionary`2 queryDependencyGraph, IFoldedArtifactsGenerator foldedArtifactsGenerator, String tableName, IPowerBIWindowService windowService, IUIHost uiHost, IExceptionHandler exceptionHandler, IUIBlockingService uiBlockingService, ILoadToModelQueryEvaluationService evaluationService, FoldedArtifacts& artifacts)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesLoadContextFactory.OnFoldedArtifactsMessage(IMessageChannel channel, FoldedArtifactsRequestMessage requestMessage)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.MashupLoadContextFactoryBase.MessageDispatchWorker(NamedPipeServerStream inputPipeStream, NamedPipeServerStream outputPipeStream, IMessenger messenger)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
Object reference not set to an instance of an object.

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
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__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.<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.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 System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(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.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialog(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IQueryServices queryServices, IModelingService modelingService, IUIHost uiHost, LocalizedString title, LoadToModelContext loadToModelContext, IEventAggregationService eventAggregationService)
at Microsoft.PowerBI.Client.Windows.Services.QueriesEditorWindowManager.ApplyQueryChanges(IEnumerable`1 queriesToLoad)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
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.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 System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(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.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass1_0.<Main>b__1()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

[LastThird]

in lieu of figuring out some way to upload attachments, here are my formulas:

Formulas:

section Section1;

shared view_TenantUserPolicies = let
Source = Sql.Database("000dcy1sb2sbt1", "TenantUserPolicies"),
dbo_view_TenantUserPolicies = Source{[Schema="dbo",Item="view_TenantUserPolicies"]}[Data]
in
dbo_view_TenantUserPolicies;

shared view_TenantACPUsage = let
Source = Sql.Database("000dcy1sb2sbt1", "TenantUserPolicies"),
dbo_view_TenantACPUsage = Source{[Schema="dbo",Item="view_TenantACPUsage"]}[Data]
in
dbo_view_TenantACPUsage;

shared dim_Tenant = let
Source = view_TenantUserPolicies,
#"Removed Other Columns" = Table.SelectColumns(Source,{"TenantID"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
#"Removed Duplicates";

shared dim_AllowedDataLocation = let
Source = view_TenantUserPolicies,
#"Removed Other Columns" = Table.SelectColumns(Source,{"AllowedDataLocation"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
#"Removed Duplicates";

shared dim_Forest = let
Source = view_TenantUserPolicies,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Forest", "Region"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Region] <> null and [Region] <> "")
in
#"Filtered Rows";

shared dim_Region = let
Source = view_TenantUserPolicies,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Region"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Region] <> null and [Region] <> "")
in
#"Filtered Rows";

shared dim_TeamsUpgradeEffectiveMode = let
Source = view_TenantUserPolicies,
#"Removed Other Columns" = Table.SelectColumns(Source,{"TeamsUpgradeEffectiveMode"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
#"Removed Duplicates";

shared dim_TenantsPerForest = let
Source = view_TenantUserPolicies
in
Source;

[v-chuncz-msft]

@LastThird 

The latest version works fine for me. You may check Power Query query folding.

[LastThird]

@v-chuncz-msft I actually just installed the (internal; early) February 2021 release yesterday. Referencing the DirectQuery table works now, but Duplicating it still hits the error. My apologies for not mentioning earlier that Duplicating also triggers this.

Do you actually get the crash snapshots, etc. when I submit them? Should I repro the issue when Duplicating that query, and resubmit?

[LastThird]

...oh, and to your question about query folding: the duplicated query absolutely is folding... here's the underlying native query on the duplicated query:

select [$Table].[Date] as [Date],
[$Table].[TenantID] as [TenantID],
[$Table].[TotalUsers] as [TotalUsers],
[$Table].[IsEdu] as [IsEdu],
[$Table].[IsTestTenant] as [IsTestTenant],
[$Table].[Forest] as [Forest],
[$Table].[Region] as [Region],
[$Table].[PolicyNumber] as [PolicyNumber],
[$Table].[TeamsUpgradeEffectiveMode] as [TeamsUpgradeEffectiveMode],
[$Table].[IsMNC] as [IsMNC],
[$Table].[AllowedDataLocation] as [AllowedDataLocation]
from [dbo].[view_TenantUserPolicies] as [$Table]

[v-chuncz-msft]

@LastThird 

I cannot reproduce it with Version: 2.88.1682.0 64-bit (December 2020). You may also try earlier versions from Power BI Desktop updates archive.