Manage aggregations Error Count Table Rows : detailColumnName, was not set in setAggregation

[ByTheWay1534]

Hello,

I was testing the "Manage aggregations" feature in Power BI Desktop

Tables :

Fact Sale Detail Direct Query => DirectQuery

Fact Sale Agregate Import => Import

Dimension Date => Dual

Dimension Customer => Dual

Dimension City => Dual

The Fact Sale Detail Direct Query and Fact Sale Agregate Import tables have the same database as source, but the Fact Sale Agregate Import is imported, and a group by step agregates it by City Key, Customer Key, Invoice Date Key. That step also sum the Total Excluding Tax and Total Including Tax fields, and creates a CountRows field, with the number of rows agregated.

I clicked on "Manage Agregations" on the Fact Sale Agregate Import table and configure it :

=> Three Group By fields for the three linked dimensions

=> Two Sum fields for the two agregated fields

=> A Count table rows field for the CountRows field in the agregate table

With this configuration i got an error message, please look the attached screenshot

Without the "Count table rows" it worked fine, so the configuration of this field produces the error but i don't know how to fix it.

Please find also the full detail of the error :

Feedback Type:
Frown (Error)

Error Message:
Required property: detailColumnName, was not set in setAggregation.

Stack Trace:
Microsoft.PowerBI.Modeler.ModelerException
   at Microsoft.PowerBI.Modeling.Engine.Utilities.SchemaChangePropertyValidator.ValidateRequiredPropertyIsNotNull(PropertyInfo prop, Object parent, String schemaChangeJSName)
   at Microsoft.PowerBI.Modeling.Engine.Utilities.SchemaChangePropertyValidator.<>c.<ValidateProperties>b__0_0(ISchemaChange s)
   at System.Collections.Generic.List`1.ForEach(Action`1 action)
   at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.<RouteModelChangeAsync>d__16.MoveNext()
--- 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.Report.<>c__DisplayClass66_0.<<SynchronizeLooselyWithQueriesAsync>b__0>d.MoveNext()
--- 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.Report.<SynchronizeLooselyWithQueriesAsync>d__65`1.MoveNext()
--- 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.Modeling.ModelAuthoringRoutingService.<>c__DisplayClass18_0.<<RouteToLocalModel>b__0>d.MoveNext()
--- 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.Modeling.ModelAuthoringRoutingService.<RouteToLocalModel>d__18.MoveNext()
--- 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.ModelAuthoringHostService.<ApplyCore>d__8.MoveNext()
--- 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.ModelAuthoringHostService.<>c__DisplayClass5_0.<<ModelAuthoringApply>b__2>d.MoveNext()
--- 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.ModelAuthoringHostService.<ExecuteAndShowFrownOnException>d__16`1.MoveNext()

Stack Trace Message:
Required property: detailColumnName, was not set in setAggregation.

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.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.PowerBI.Client.Windows.Utilities.PowerBIFormUnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.PowerBI.Client.Windows.ModelAuthoringHostService.<ExecuteAndShowFrownOnException>d__16`1.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.Client.Windows.ModelAuthoringHostService.ExecuteAndShowFrownOnException[T](Func`1 func)
   at Microsoft.PowerBI.Client.Windows.ModelAuthoringHostService.<>c__DisplayClass5_0.<ModelAuthoringApply>b__1(CancellationToken cancelToken)
   at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass9_0`1.<<ExecuteAndHandlePromise>b__0>d.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass9_0`1.<ExecuteAndHandlePromise>b__0()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.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.HostServiceDispatcher.<>c__DisplayClass7_0.<ExecuteOnUIThreadAsync>b__0(Object t)
   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.MinervaDialog.Show(IWindowHandle owner, String dialogDefinitionJson, String urlAllowedForFrame, String urlAllowedForPopup)
   at Microsoft.PowerBI.Client.Windows.Services.MinervaModalDialogManager.<>c__DisplayClass9_1.<ShowMinervaModalDialog>b__0(IWindowHandle ownerWindow)
   at Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowServiceExtensions.<>c__DisplayClass0_0.<ExecuteWithLegacyOwnerWindow>b__0(IWindowHandle ownerWindow)
   at Microsoft.PowerBI.Client.Windows.Services.MinervaModalDialogManager.ShowMinervaModalDialog(String dialogDefinitionJson, ILegacyUIHost uiHost, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, String urlAllowedForFrame, String urlAllowedForPopup, Action onCloseAction)
   at Microsoft.PowerBI.Client.Windows.ModalDialogHostService.<>c__DisplayClass10_0.<ShowHostModalDialogCore>b__1()
   at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass6_0`1.<ExecuteOnUIThread>b__0()
   at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass5_0.<ExecuteOnUIThread>b__0()
   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.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_1.<Main>b__2()
   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)


PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.108.997.0","ModuleName":"Microsoft.PowerBI.Modeling.Engine.dll","Component":"Microsoft.PowerBI.Modeling.Engine.Utilities.SchemaChangePropertyValidator","Error":"Microsoft.PowerBI.Modeler.ModelerException - RequiredPropertyNotSet","MethodDef":"ValidateRequiredPropertyIsNotNull","ErrorOffset":"40"}

Snapshot Trace Logs:
C:\Users\XXXXXXXXXXXXXX\Microsoft\Power BI Desktop Store App\FrownSnapShot67a88830-92a6-406a-8315-639445b50b28.zip

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\XXXXXXXXXXXXXX\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_enableWebView2
PBI_mobileAuthoringFormattingUI
PBI_sparklines

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_compositeModelsOverAS
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_scorecardVisual
PBI_fieldParametersSuperSwitch

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBINonFatalError_ErrorDescription:
RequiredPropertyNotSet

PowerBIUserFeedbackServices_IsReported:
True

Formulas:


section Section1;

shared #"Fact Sale Detail Direct Query" = let
    Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
    #"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
    Fact_Sale = #"WideWorldImportersDW-Standard"{[Schema="Fact",Item="Sale"]}[Data]
in
    Fact_Sale;

shared #"Dimension City" = let
    Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
    #"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
    Dimension_City = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="City"]}[Data]
in
    Dimension_City;

shared #"Dimension Customer" = let
    Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
    #"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
    Dimension_Customer = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="Customer"]}[Data]
in
    Dimension_Customer;

shared #"Dimension Date" = let
    Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
    #"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
    Dimension_Date = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="Date"]}[Data]
in
    Dimension_Date;

shared #"Dimension Employee" = let
    Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
    #"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
    Dimension_Employee = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="Employee"]}[Data]
in
    Dimension_Employee;

shared #"Dimension Stock Item" = let
    Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
    #"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
    #"Dimension_Stock Item" = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="Stock Item"]}[Data]
in
    #"Dimension_Stock Item";

shared #"Fact Sale Agregate Import" = let
    Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
    #"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
    Fact_Sale = #"WideWorldImportersDW-Standard"{[Schema="Fact",Item="Sale"]}[Data],
    #"Reordered Columns" = Table.ReorderColumns(Fact_Sale,{"City Key", "Customer Key", "Invoice Date Key", "Sale Key", "Description", "Bill To Customer Key", "Stock Item Key", "Salesperson Key", "WWI Invoice ID", "Delivery Date Key", "Package", "Quantity", "Unit Price", "Tax Rate", "Total Excluding Tax", "Tax Amount", "Profit", "Total Including Tax", "Total Dry Items", "Total Chiller Items", "Lineage Key", "Dimension.City", "Dimension.Customer(Bill To Customer Key)", "Dimension.Customer(Customer Key)", "Dimension.Date(Delivery Date Key)", "Dimension.Date(Invoice Date Key)", "Dimension.Employee", "Dimension.Stock Item"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Sale Key", "Description", "Bill To Customer Key", "Stock Item Key", "Salesperson Key", "WWI Invoice ID", "Delivery Date Key", "Package", "Unit Price", "Tax Rate", "Tax Amount", "Profit", "Total Dry Items", "Total Chiller Items", "Lineage Key", "Quantity"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"City Key", "Customer Key", "Invoice Date Key"}, {{"TotalExcludingTax", each List.Sum([Total Excluding Tax]), type number}, {"TotalIncludingTax", each List.Sum([Total Including Tax]), type number}, {"CountRows", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows";

Do you have an idea ?

Thanks,

Regards,

Romain

[fraserward]

Not sure when it happened but sometime after the August 2022 version and the December 2022 version but this now works again as it should. 

[gtacchini]

Same error here. Any solution?

[srkstl]

I had the same issue and downgraded my PowerBI Destkop version to May 2022 and it works now.

Good luck.

[fraserward]

Thanks at @srkstl .  After much effort the only fix for my was a downgrade version.  I went to July 2022 and this worked.

[fraserward]

I have this exact same issue and nothing can get round it. 

I dont think [count table rows] is working whatsoever

[amitchandak]

@ByTheWay1534 ,Scroll down in that popup and check anything left out. Also, delete the second one and try