Remove column based on condition

[chratnaa5]

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	Manager_Name
CUST_001	John C	Offshore	Mathhew A
CUST_002	Mathhew A	Onsite	Mathhew A
CUST_003	Paul P	Onsite	Mathhew A
CUST_004	Tom R	Onsite	Mathhew A
CUST_005	Darren C	Onsite	Mathhew A
CUST_006	Paul H	Offshore	Mathhew A
CUST_007	Johnathan R	Onsite	Johnathan R
CUST_008	Marc P	Offshore	Johnathan R
CUST_009	Mark A	Offshore	Johnathan R
CUST_010	Nisha P	Onsite	Johnathan R
CUST_011	Kumar K	Offshore	Johnathan R

Table 2:

Customer_Num

Customer_Name

Product1

Product2

Product3

Product4

Product5

Product6

Product7

Product8

Product9

Product10

Opted

NotApplicable

Not Opted

CUST_001

John C

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

10

0

0

CUST_002

Mathhew A

N

Y

N

Y

Y

Y

Y

Y

Y

Y

8

0

2

CUST_003

Paul P

NA

Y

NA

Y

Y

Y

Y

Y

Y

Y

8

2

0

CUST_004

Tom R

Y

Y

Y

Y

N

Y

Y

Y

Y

Y

9

0

1

CUST_005

Darren C

N

Y

NA

Y

NA

Y

Y

Y

Y

Y

7

2

1

CUST_006

Paul H

NA

Y

Y

Y

Y

Y

Y

Y

Y

Y

9

1

0

CUST_007

Johnathan R

Y

Y

N

Y

Y

Y

Y

Y

Y

Y

9

0

1

CUST_008

Marc P

N

Y

NA

Y

Y

Y

Y

Y

Y

Y

8

1

1

CUST_009

Mark A

NA

Y

Y

Y

N

Y

Y

Y

Y

Y

8

1

1

CUST_010

Nisha P

Y

Y

Y

Y

NA

Y

Y

Y

Y

Y

9

1

0

CUST_011

Kumar K

N

Y

Y

Y

Y

Y

Y

Y

Y

Y

9

0

1

Report Required: 

Customer_Num	Customer_Name	Manager_Name	Product1	Product3	Product5	Not Opted
CUST_004	Tom R				N	1
CUST_005	Darren C		N			1
CUST_007	Johnathan R			N		1
CUST_008	Marc P		N			1
CUST_009	Mark A				N	1
CUST_011	Kumar K		N			1
CUST_002	Mathhew A		N	N		2

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

[v-kkf-msft · Accepted solution]

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]

Solution is working fine. Many Thanks.

[chratnaa5]

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]

Hi @chratnaa5 ,

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

Best Regards,
Winniz

[chratnaa5]

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

[aj1973]

Hi @chratnaa5 

Check out this file

https://drive.google.com/drive/folders/1rRwGYf8kYfdrj0nU0xhUoiSpFH7tVcF1?usp=sharing

[chratnaa5]

@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]

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