Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Helper IV
2 years ago
Solved

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    and...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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"
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Richard_Halsall
Helper IV
2 years ago

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