Power Query Error Specified method is not supported

[Richard_Halsall]

Hi, I am struggling to understand this error message:

I have grouped a table of data with 4 columns and then attempted to pivot on the billable column with Total Hours as the value 

and get the message below, can somebody help. Thanks

Unexpected error: Specified method is not supported.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified method is not supported. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified method is not supported. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified method is not supported. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified method is not supported. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified method is not supported. ---> System.NotSupportedException: Specified method is not supported. ---> System.NotSupportedException: Specified method is not supported.
at Microsoft.Mashup.Salesforce.SoqlExpression.SoqlQueryExpressionVisitor.CheckType(TypeValue type)
at Microsoft.Mashup.Salesforce.SoqlExpression.SoqlQueryExpressionVisitor.VisitBinary(BinaryQueryExpression binaryExpression)
at Microsoft.Mashup.Salesforce.SoqlExpression.SoqlQueryExpressionVisitor.VisitBinary(BinaryQueryExpression binaryExpression)
at Microsoft.Mashup.Salesforce.SoqlExpression.SoqlQueryExpressionVisitor.VisitBinary(BinaryQueryExpression binaryExpression)
at Microsoft.Mashup.Salesforce.SoqlExpression.SoqlQueryExpressionVisitor.VisitBinary(BinaryQueryExpression binaryExpression)
at Microsoft.Mashup.Salesforce.SoqlExpression.SoqlQueryExpressionVisitor.VisitBinary(BinaryQueryExpression binaryExpression)
at Microsoft.Mashup.Salesforce.SoqlExpression..ctor(RecordTypeValue recordType, SoqlColumns columns, TypeValue argumentType, QueryExpression expression)
at Microsoft.Mashup.Salesforce.SoqlQuery.RowEnumerable.RowEnumerator.UpdateQueryText(IValueReference previousRow)
at Microsoft.Mashup.Salesforce.SoqlQuery.RowEnumerable.RowEnumerator.MoveNext()
at Microsoft.Mashup.Engine1.Language.Query.UnpivotQuery.UnpivotEnumerable.UnpivotTableEnumerator.MoveNext()
at Microsoft.Mashup.Engine1.Language.Query.DistinctQuery.DistinctEnumerable.DistinctEnumerator.MoveNext()
at Microsoft.Mashup.Engine1.Runtime.RetryQuery.RetryEnumerator.MoveNext()
at Microsoft.Mashup.Engine1.Runtime.ColumnReferenceListValue.<GetEnumerator>d__12.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
at Microsoft.Mashup.Engine1.Runtime.TableValue.Pivot(ListValue pivotValues, TextValue attributeColumn, TextValue valueColumn, Value aggregationFunction)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue5`6.Invoke(Value arg0, Value arg1, Value arg2, Value arg3, Value arg4)
at Microsoft.Mashup.Engine1.Runtime.FunctionValue.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.Library.Linker.BindFunctionValue.TypedInvoke(RecordValue environment, Value section, TextValue name)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3`4.Invoke(Value arg0, Value arg1, Value arg2)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()
at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.Instruction.ExecuteCondition(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.ExecuteCondition(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.IfInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
at Microsoft.Mashup.Evaluator.Interface.IDocumentEvaluatorExtensions.GetResult[T](IDocumentEvaluator`1 evaluator, DocumentEvaluationParameters parameters)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
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.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass3_0.<BeginGetResult>b__0(EvaluationResult2`1 result)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass3_0.<BeginGetResult>b__0(EvaluationResult2`1 result)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)
at Microsoft.Mashup.Evaluator.Interface.IDocumentEvaluatorExtensions.GetResult[T](IDocumentEvaluator`1 evaluator, DocumentEvaluationParameters parameters)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
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.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass9_1.<OnBeginGetPreviewValueSource>b__1()
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass0_0.<RunStub>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass17_0.<TryReportException>b__1()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_0`1.<OnBeginGetResult>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
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.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
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.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_TableSource()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

[Daryl-Lynch-Bzy · Accepted solution]

Another option is to use the Group Function to complete the Pivot:

let
  Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
  Timesheet = Source{[Name="IRIS_time_sheet__c"]}[Data],
  #"Filtered Rows" = Table.SelectRows(Timesheet, each [Site_Start_Date__c] > #date(2023, 11, 1)),
  #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([deleted__c] = "false") and ([taskType__c] <> "De-mobilisation" and [taskType__c] <> "Deduction" and [taskType__c] <> "Expense" and [taskType__c] <> "Mobilisation")),
  #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Billable", each if [Billable__c] = true then "Billable" else "Non-Billable", type text),
  #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Project__c", "Site_Start_Date__c"}, {{"Bilable", each List.Sum( Table.SelectRows(_, each [Billable] = "Billable")[Site_Duration__c] ), type nullable number}, {"Unbilable", each List.Sum( Table.SelectRows(_, each [Billable] = "Unbillable")[Site_Duration__c] ), type nullable number} })
in
  #"Grouped Rows"

[Richard_Halsall]

Many thanks, the second solution worked great unfortunately the first through up the same error

[Daryl-Lynch-Bzy]

Hi, @Richard_Halsall - odd - it looks valid.  The error message suggests that Saleforce is not allowing it.  I wonder if you can avoid with Table.Buffer.  Note I removed some uncessary select column steps due to the Group Rows step:

let
  Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
  Timesheet = Source{[Name="IRIS_time_sheet__c"]}[Data],
  #"Filtered Rows" = Table.SelectRows(Timesheet, each [Site_Start_Date__c] > #date(2023, 11, 1)),
  #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([deleted__c] = "false") and ([taskType__c] <> "De-mobilisation" and [taskType__c] <> "Deduction" and [taskType__c] <> "Expense" and [taskType__c] <> "Mobilisation")),
  #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Project__c", "Site_Start_Date__c", "Billable__c"}, {{"TotalHours", each List.Sum([Site_Duration__c]), type nullable number}}),
  #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Billable", each if [Billable__c] = true then "Billable" else "Non-Billable", type text),
  #"Remove Column" = Table.RemoveColumns( #"Added Conditional Column" , { "Billable__c" } ),
  #"Table Buffer" = Table.Buffer(#"Remove Column"),
  #"Pivot Column" = Table.Pivot(#"Table Buffer", List.Distinct(#"Table Buffer"[Billable]), "Billable", "TotalHours", List.Sum)
in
  #"Pivot Column"

 hopefully this works, but you may need to debug any typos.

Did you need "equals to and greater than" for the data filter?

[Daryl-Lynch-Bzy]

Hi @Richard_Halsall  - what you mean by Pivot on the Billable column?  Does this column have two values "Billable" and "Unbillable", so you want to have two columns with "Billable Hours" and "Unbillable Hours"?

Could you please share the M Query function that you are running?  I am expecting the following:

= Table.Pivot(Source, List.Distinct(Source[Billable]), "Billable", "TotalHours", List.Sum)

[Richard_Halsall]

Hi, the M query is below

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
Timesheet = Source{[Name="IRIS_time_sheet__c"]}[Data],
KeepColumns = Table.SelectColumns(Timesheet,{"Id", "Name", "approved__c", "deleted__c", "taskType__c", "Contractor__c", "Project__c", "Site_Duration__c", "Site_Start_Date__c", "Billable__c"}),
#"Filtered Rows" = Table.SelectRows(KeepColumns, each [Site_Start_Date__c] > #date(2023, 11, 1)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([deleted__c] = "false") and ([taskType__c] <> "De-mobilisation" and [taskType__c] <> "Deduction" and [taskType__c] <> "Expense" and [taskType__c] <> "Mobilisation")),
#"Removed Other Columns2" = Table.SelectColumns(#"Filtered Rows1",{"Project__c", "Site_Duration__c", "Site_Start_Date__c", "Billable__c"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns2", {"Project__c", "Site_Start_Date__c", "Billable__c"}, {{"TotalHours", each List.Sum([Site_Duration__c]), type nullable number}}),
#"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Billable", each if [Billable__c] = true then "Billable" else "Non-Billable", type text),
#"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Project__c", "Site_Start_Date__c", "TotalHours", "Billable"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Billable]), "Billable", "TotalHours", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column1",null,0,Replacer.ReplaceValue,{"Billable", "Non-Billable"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Replaced Value",{"Project__c", "Site_Start_Date__c", "Billable", "Non-Billable"})
in
#"Removed Other Columns1"

It throws the error at step #"Pivoted Column1"

Thanks