POWER QUERY - NEW COLUMN

[AlessandraMacedo]

Hi

I have to add new column in power query in "Base Usuario".

This new column must have this excel related formula:

=COUNT.IFS('Base Integrantes'!AC:AC;"ATIVO";'Base Integrantes'!E:E;[@[Cod_UA]])

This column will count a column of another table with 2 criterios as show in excel formula.

Could would help me?

[#2]

@AlessandraMacedo 

As variant, for such sample

you may add custom column with code like

let
    Source = Excel.CurrentWorkbook(){[Name="BaseUsuario"]}[Content],
    AddCount = Table.AddColumn(
        Source,
        "Count",
        each
            let
                currentField=[UA],
                filterTable=Table.SelectRows(
                    BaseIntegrantes,
                    each (Record.Field(_, "AC") = "ATIVO") and
                         (Record.Field(_, "E") = currentField)
                ),
                countRows = Table.RowCount(filterTable)
            in
                countRows
    )
in
    AddCount

[#3 · Accepted solution]

@AlessandraMacedo 

Another variant - make reference on lookup table, filter it on constant (ATIVO), add custom column with value 1

Merge first table with it on E, expand ID column only

and group on all columns but ID with SUM aggregation

[#4]

Thank you!!! @Sergei Baklan 

[#5]

@AlessandraMacedo 

You are welcome