Forum Discussion
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...
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"
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
