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_NumCustomer_NameLocationManager_Name
CUST_001John COffshoreMathhew A
CUST_002Mathhew AOnsiteMathhew A
CUST_003Paul POnsiteMathhew A
CUST_004Tom ROnsiteMathhew A
CUST_005Darren COnsiteMathhew A
CUST_006Paul HOffshoreMathhew A
CUST_007Johnathan ROnsiteJohnathan R
CUST_008Marc POffshoreJohnathan R
CUST_009Mark AOffshoreJohnathan R
CUST_010Nisha POnsiteJohnathan R
CUST_011Kumar KOffshoreJohnathan R

 

Table 2:

Customer_NumCustomer_NameProduct1Product2Product3Product4Product5Product6Product7Product8Product9Product10OptedNotApplicableNot Opted
CUST_001John CYYYYYYYYYY1000
CUST_002Mathhew ANYNYYYYYYY802
CUST_003Paul PNAYNAYYYYYYY820
CUST_004Tom RYYYYNYYYYY901
CUST_005Darren CNYNAYNAYYYYY721
CUST_006Paul HNAYYYYYYYYY910
CUST_007Johnathan RYYNYYYYYYY901
CUST_008Marc PNYNAYYYYYYY811
CUST_009Mark ANAYYYNYYYYY811
CUST_010Nisha PYYYYNAYYYYY910
CUST_011Kumar KNYYYYYYYYY901

 

Report Required: 

Customer_NumCustomer_NameManager_NameProduct1Product3Product5Not Opted
CUST_004Tom R   N1
CUST_005Darren C N  1
CUST_007Johnathan R  N 1
CUST_008Marc P N  1
CUST_009Mark A   N1
CUST_011Kumar K N  1
CUST_002Mathhew A NN 2

 

Could anyone provide step by step process to create ABOVE DETAILS REPORT USING POWER Query and Power View. Many Thanks

 

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.

     

     

    Remove column based on condition.pbix48 KB

8 Replies

  • 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.

     

     

    Remove column based on condition.pbix48 KB
    • 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

      aj1973 - I had given sample data. In live system I am having 60 + unique products, i mean the column names are different. I could not find end results. 

       

      However thank you very much for your time.

       

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

        Hi chratnaa5 ,

         

        Has your problem been solved? 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