Forum Discussion

chratnaa5's avatar
chratnaa5
Frequent Visitor
4 years ago
Solved

Remove column based on condition

Hi All, I have multiple columns with mulitple producted. I would like to filter or prepare report customer Vs products not opted. For Example: Table 1: Customer_Num Customer_Name Location Man...
Tips and Tricks
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi chratnaa5 ,

     

    First select the Customer_Num and Customer_Name, then unpivot other columns. 

     

     

    If you have a requirement for the order between columns in the matrix, you can add that custom column to sort the Attribute column. 

     

    = if [Attribute] = "Opted" then 11 else if [Attribute] = "NotApplicable" then 12 else if [Attribute] = "Not Opted" then 13 else Text.Select([Attribute], {"0".."9"})

     

    Then create and use the following measure.

     

    Measure = 
CALCULATE (
    MAX ( 'Table 2'[Value] ),
    FILTER (
        'Table 2',
        'Table 2'[Value] = "N" 
            || ( 'Table 2'[Attribute] = "Not Opted" && MAX ( 'Table 2'[Value] ) <> "0" )
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi chratnaa5 ,

 

First select the Customer_Num and Customer_Name, then unpivot other columns. 

 

 

If you have a requirement for the order between columns in the matrix, you can add that custom column to sort the Attribute column. 

 

= if [Attribute] = "Opted" then 11 else if [Attribute] = "NotApplicable" then 12 else if [Attribute] = "Not Opted" then 13 else Text.Select([Attribute], {"0".."9"})

 

Then create and use the following measure.

 

Measure = 
CALCULATE (
    MAX ( 'Table 2'[Value] ),
    FILTER (
        'Table 2',
        'Table 2'[Value] = "N" 
            || ( 'Table 2'[Attribute] = "Not Opted" && MAX ( 'Table 2'[Value] ) <> "0" )
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

chratnaa5's avatar
chratnaa5
Frequent Visitor
4 years ago

Hi,

Please find attached Power BI file. Getting error message:

 

Feedback Type:
Frown (Error)

Error Message:
No handler was found for message type 'Microsoft.Mashup.Evaluator.MessageBasedOutputStream+BinaryChunkMessage'.

Stack Trace:
Microsoft.Mashup.Host.Document.SerializedException
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(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.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.TranslateCancelExceptions(Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.IgnoreCancelExceptions(Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.Finish()
at Microsoft.Mashup.Evaluator.Interface.IDataReaderSourceExtensions.<>c__DisplayClass1_0.<AfterDispose>b__0()
at Microsoft.Mashup.Evaluator.Interface.NotifyingDataReaderSource.Dispose()
at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.Dispose[T](EvaluationResult2`1 result)
at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation`1.<>c__DisplayClass10_0.<Complete>b__0()
at Microsoft.Mashup.Evaluator.Interface.NotifyingDataReaderSource.Dispose()
at Microsoft.Mashup.Evaluator.DocumentEvaluator.<>c__DisplayClass7_1`1.<BeginGetResult>b__1()
at Microsoft.Mashup.Evaluator.Interface.NotifyingDataReaderSource.Dispose()
at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.Dispose[T](EvaluationResult2`1 result)
at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation`1.<>c__DisplayClass10_0.<Complete>b__0()
at Microsoft.Mashup.Evaluator.Interface.NotifyingDataReaderSource.Dispose()
at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.Dispose[T](EvaluationResult2`1 result)
at Microsoft.Mashup.OleDbProvider.DataHost.MashupEvaluator.<>c__DisplayClass4_1.<Evaluate>b__1()
at Microsoft.Mashup.Evaluator.Interface.NotifyingDataReaderSource.Dispose()
at Microsoft.Mashup.Engine.Interface.Tracing.TracingDataReaderSource.Dispose()
at Microsoft.Mashup.Evaluator.Interface.IDataReaderSourceExtensions.<>c__DisplayClass1_0.<AfterDispose>b__0()
at Microsoft.Mashup.Evaluator.Interface.NotifyingDataReaderSource.Dispose()
at Microsoft.Mashup.OleDbProvider.MashupCommandEvaluation.Dispose()
at Microsoft.Mashup.OleDbProvider.TracingMultipleResults.Dispose()

Stack Trace Message:
No handler was found for message type 'Microsoft.Mashup.Evaluator.MessageBasedOutputStream+BinaryChunkMessage'.

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__DisplayClass15_0.<HandleException>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_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, IDesktopModelingHost modelingHost, LocalizedString title, LoadToModelContext loadToModelContext)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass16_0.<TryShowDialogForQueries>b__0()
at Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass60_0`1.<SynchronizeLooselyWithQueries>b__0()
at Microsoft.PowerBI.Client.Windows.Report.<SynchronizeLooselyWithQueriesAsync>d__61`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueriesAsync[T](Func`1 getTask)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueries[T](Func`1 action)
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.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_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)


PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.95.983.0","ModuleName":"","Component":"Microsoft.Mashup.Evaluator.MessageHandlers","Error":"Microsoft.Mashup.Host.Document.SerializedException - System.InvalidOperationException","MethodDef":"Dispatch","ErrorOffset":""}

Snapshot Trace Logs:
C:\Users\SadgunYashveer\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot4c44e12b-93e3-4f09-9d1d-129d17658e4a.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

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

Enabled Preview Features:
PBI_JsonTableInference
PBI_NewWebTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_rdlNativeVisual

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_dynamicParameters
PBI_enhancedTooltips
PBI_enableWebView2

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBINonFatalError_ErrorDescription:
System.InvalidOperationException

PowerBIUserFeedbackServices_IsReported:
True

Formulas:


section Section1;

shared Customer_Details = let
Source = Excel.Workbook(File.Contents("C:\PowerBI\Test Data\Test_Data.xlsx"), null, true),
Customer_Details_Sheet = Source{[Item="Customer_Details",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Customer_Details_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer_Num", type text}, {"Customer_Name", type text}, {"Location", type text}, {"Manager_Name", type text}})
in
#"Changed Type1";

  • shared Product_details = let
    Source = Excel.Workbook(File.Contents("C:\PowerBI\Test Data\Test_Data.xlsx"), null, true),
    Product_details_Sheet = Source{[Item="Product_details",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Product_details_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer_Num", type text}, {"Customer_Name", type text}, {"Product1", type text}, {"Product2", type text}, {"Product3", type text}, {"Product4", type text}, {"Product5", type text}, {"Product6", type text}, {"Product7", type text}, {"Product8", type text}, {"Product9", type text}, {"Product10", type text}, {"Total_PROD", Int64.Type}, {"NotApplicable", Int64.Type}, {"Not Opted", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer_Num", "Customer_Name"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Order", each if [Attribute] = "Opted" then 11 else if [Attribute] = "NotApplicable" then 12 else if [Attribute] = "Not Opted" then 13 else Text.Select([Attribute], {"0".."9"}))
    in
    #"Added Custom";

 

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi chratnaa5 ,

     

    I could not find attached Power BI file, could you please share your PBIX file again?

     

    Best Regards,
    Winniz

    • chratnaa5's avatar
      chratnaa5
      Frequent Visitor
      4 years ago

       Sorry some issue with my profile, I am not able to attach file.