Expression.Error: We cannot convert a value of type List to type Number.Details: Value=List Type=Type

[jhazan]

Hi,

I'm getting this error in Power Query and it looks like it's in the GroupedRows step. Any help is appreciated!

let
    Source = Table.Combine({#"① S01_RR_Revenue", #"② S02_GDSF_CYPeriod", #"③ S03_GDSF_PYRemovals"}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([FIRM] = "1360") and ([PRODUCT] = "Bulk")),
    // Joins S01, S02, S03 original tables into 1 consolidated table.
    // Customer Fields (GDSF/REV) and SFID Field are concatenated into a single cell when join happens by "Group" and "TextCombine"
    #"E01_Join | Tbl Rows" = Table.Group(#"Filtered Rows", {"REGION", "PRODUCT", "FIRM", "SALES_REP", "SALES_TEAM", "DEAL_TYPE", "PERIOD_TYPE"}, {{"CUSTOMERs IN SCOPE", each Text.Combine([CUSTOMER]," , "), type text}, {"SFIDs IN SCOPE", each Text.Combine([SFID]," , "), type text}, {"GDSF CUSTOMERs IN SCOPE", each Text.Combine([GDSF_CUSTOMER]," , "), type text},{"CURRENT RR", each List.Sum([CURRENT_RR]), type number}, {"PRIOR RR", each List.Sum([PRIOR_RR]), type number}, {"CURRENT ASV", each List.Sum([CURRENT_PERIOD_ASV]), type number}, {"PRIOR ASV REMOVALS", each List.Sum([PRIOR_REMOVALS_ASV]), type number}}),
    // Removes any duplicated items in the "GDSF CUSTOMERs in SCOPE" field
    #"T01_Clean | GDSF Customers-Dups" = Table.TransformColumns(#"E01_Join | Tbl Rows",{{"GDSF CUSTOMERs IN SCOPE", each Text.Combine(List.Distinct(List.Transform(Text.Split(_," , "), Text.Trim)), ", ")}}),
    // Removes records where Current RR, Prior RR, Current ASV are null and Prior ASV removals are not null
    #"T02_Remove | GDSF PR Records" = Table.SelectRows(#"T01_Clean | GDSF Customers-Dups", each not List.IsEmpty(List.RemoveMatchingItems({[CURRENT RR],[PRIOR RR],[CURRENT ASV]},{"", null}))),
    // Remove any records where Firm equals null or # value
    #"T03_Remove | Null Firms" = Table.SelectRows(#"T02_Remove | GDSF PR Records", each [FIRM] <> "0" and [FIRM] <> "#" and [FIRM] <> null),
    #"T04_Replace | Nulls to Zero" = Table.ReplaceValue(#"T03_Remove | Null Firms",null,0,Replacer.ReplaceValue,{"CURRENT RR", "PRIOR RR", "CURRENT ASV", "PRIOR ASV REMOVALS"}),
    #"T05_Replace | Prior ASV Removals" = Table.ReplaceValue(#"T04_Replace | Nulls to Zero",each [PRIOR ASV REMOVALS],each if [CURRENT RR]=0 and [CURRENT RR]-[PRIOR RR]<0 then [PRIOR ASV REMOVALS] else 0,Replacer.ReplaceValue,{"PRIOR ASV REMOVALS"}),
    #"T06_Change | Prior ASV Removals Type" = Table.TransformColumnTypes(#"T05_Replace | Prior ASV Removals",{{"PRIOR ASV REMOVALS", type number}}),
    #"T07_Join | SalesExclusion" = Table.NestedJoin(#"T06_Change | Prior ASV Removals Type",{"REGION", "PRODUCT", "FIRM", "DEAL_TYPE"},MTbl_SalesComments_ExclUpdate,{"REGION", "PRODUCT", "FIRM", "DEAL_TYPE"},"Mtbl_SalesComments_ExclUpdate",JoinKind.LeftOuter),
    #"T08_Expand | SalesExclusion" = Table.ExpandTableColumn(#"T07_Join | SalesExclusion", "Mtbl_SalesComments_ExclUpdate", {"SALES_REASON", "FULL_REVENUE_DATE", "SALES_COMMENT", "CFO REVIEW COMMENTS"}, {"SALES_REASON", "FULL_REVENUE_DATE", "SALES_COMMENT", "CFO REVIEW COMMENTS"}),
    #"T09_Join | SalesComment" = Table.NestedJoin(#"T08_Expand | SalesExclusion",{"REGION", "PRODUCT", "FIRM", "DEAL_TYPE"},MTbl_SalesComments,{"REGION", "PRODUCT", "FIRM", "DEAL_TYPE"},"MTbl_SalesComments",JoinKind.LeftOuter),
    #"T10_Expand | SalesComment" = Table.ExpandTableColumn(#"T09_Join | SalesComment", "MTbl_SalesComments", {"CFO REVIEW COMMENTS", "SNAPSHOT_DATE"}, {"CFO REVIEW COMMENTS.1", "SNAPSHOT_DATE"}),
    #"Added Custom" = Table.AddColumn(#"T10_Expand | SalesComment", "SNAPSHOT_DATE_1", each Date.ToText([SNAPSHOT_DATE], "MMM-yy")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"SNAPSHOT_DATE", type text}, {"SNAPSHOT_DATE_1", type text}, {"CFO REVIEW COMMENTS.1", type text}, {"CFO REVIEW COMMENTS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"REGION", "PRODUCT", "FIRM", "SALES_REP", "SALES_TEAM", "DEAL_TYPE", "PERIOD_TYPE", "CUSTOMERs IN SCOPE", "SFIDs IN SCOPE", "GDSF CUSTOMERs IN SCOPE", "CURRENT RR", "PRIOR RR", "CURRENT ASV", "PRIOR ASV REMOVALS", "SALES_REASON", "FULL_REVENUE_DATE", "SALES_COMMENT"}, {{"SNAPSHOT_MONTH", each Text.Combine([SNAPSHOT_DATE_1]," , "), type text}}, {{"CFO_COMMENTS", each Text.Combine([CFO REVIEW COMMENTS.1]," , "), type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"REGION", "PRODUCT", "FIRM", "DEAL_TYPE", "PERIOD_TYPE", "CURRENT RR", "PRIOR RR", "CURRENT ASV", "PRIOR ASV REMOVALS", "SALES_TEAM", "SALES_REP", "CUSTOMERs IN SCOPE", "SFIDs IN SCOPE", "GDSF CUSTOMERs IN SCOPE", "SALES_REASON", "FULL_REVENUE_DATE", "SALES_COMMENT", "SNAPSHOT_MONTH", "CFO_COMMENTS"})
in
    #"Reordered Columns"