Countifs & Sumifs in excel power query gives wrong output for large dataset-CodePudding

I've a table which contains shop, shelf and products & a result table which has countifs & sumifs formula embedded. Below is the screenshot attached of table and result table.

countifs formula

=COUNTIFS(F6:F17,J6:J17,G6:G17,"p1")

sumifs formula

=SUMIFS(K:K,I:I,I6)

I'm trying to use this formula in excel power query. Below is the screenshot of powerquery and custom column formula for countifs

countifs formula in powerquery

    = List.Count(
    Table.SelectRows(
        Table3,
        (Var) => (Var)[shelf]=[result_shelf]
    and 
    (Var)[product]="p1"
    )
[product]
)

I'm getting the result in powerquery for countifs for small dataset but I've something around 5k lines where this powerquery formula doesn't work and only shows 0 .

Powerquery for sumifs : I'm unable to achieve the formula

CodePudding user response：

Probably about 5 ways but how about

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"shop", type text}, {"shelf", type text}, {"product", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "countifs", each if [product]="p1" then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"shop"}, {{"sumifs", each List.Sum([countifs]), type number}, {"data", each _, type table [shop=nullable text, shelf=nullable text, product=nullable text, countifs=number]}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"shelf", "product", "countifs"}, {"shelf", "product", "countifs"})
in  #"Expanded data"

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"shop", type text}, {"shelf", type text}, {"product", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"shop"}, {
    {"data", each Table.AddColumn(_, "countifs", each if [product]="p1" then 1 else 0), type table },
    {"sumifs", each Table.RowCount(Table.SelectRows(_, each [product] = "p1")),type number   }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"shelf", "product", "countifs"}, {"shelf", "product", "countifs"})
in #"Expanded data"

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"shop", type text}, {"shelf", type text}, {"product", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "countifs", each if [product]="p1" then 1 else 0),
#"Added Custom2" = Table.AddColumn(#"Added Custom","sumifs",(i)=>Table.RowCount(Table.SelectRows(#"Added Custom", each [shop]=i[shop] and [product]="p1")), type number)
in #"Added Custom2"