Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Replicate countif excel function in PowerQuery

Hi,   I'm trying to replicate countif excel function into powerquery. =COUNTIF(C:C,C3)   End result should be as below Item Count of Items A 4 A 4 A 4 A 4 B 4 B 4 ...
Need Help
Show and Tell
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    =let a=Table.Buffer(Table.Group(PreviousStepName,"Item",{"n",Table.RowCount})) in Table.AddColumn(PreviousStepName,"Count of Items",each a{[Item=[Item]]}[n])

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
3 years ago
Table.AddColumn(#"previousStep", "Count of Items", each List.Count(Table.SelectRows(#"previousStep", (x) => x[Item] = "A")[Item]))

gets you...

Table.AddColumn(#"Changed Type", "Count of Items", each List.Count(Table.SelectRows(#"Changed Type", (x) => [Item] = x[Item])[Item]))

gets you...

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Unfortunately this does not work. Below is the formula Im using

= Table.AddColumn(#"Added Unit", "Count of Items", each List.Count(Table.SelectRows(#"Added Unit", (x) => [a.itm_nbr]=[a.itm)[a.itm]))