Forum Discussion

AlessandraMacedo's avatar
AlessandraMacedo
Copper Contributor
Oct 01, 2020
Solved

POWER QUERY - NEW COLUMN

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?

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 01, 2020

    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 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 01, 2020

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 01, 2020

    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

Resources