Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Richard_Halsall
Helper III
Helper III

Power Query Error Specified method is not supported

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 

Richard_Halsall_0-1703158227507.png

 

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)

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"

View solution in original post

5 REPLIES 5
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"

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

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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)

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors