SOLVED

POWER QUERY - NEW COLUMN

%3CLINGO-SUB%20id%3D%22lingo-sub-1734877%22%20slang%3D%22en-US%22%3EPOWER%20QUERY%20-%20NEW%20COLUMN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1734877%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20to%20add%20new%20column%20in%20power%20query%20in%20%22Base%20Usuario%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20new%20column%20must%20have%20this%20excel%20related%20formula%3A%3C%2FP%3E%3CP%3E%3DCOUNT.IFS('Base%20Integrantes'!AC%3AAC%3B%22ATIVO%22%3B'Base%20Integrantes'!E%3AE%3B%5B%40%5BCod_UA%5D%5D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20column%20will%20count%20a%20column%20of%20another%20table%20with%202%20criterios%20as%20show%20in%20excel%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20would%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1734877%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1735054%22%20slang%3D%22en-US%22%3ERe%3A%20POWER%20QUERY%20-%20NEW%20COLUMN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816486%22%20target%3D%22_blank%22%3E%40AlessandraMacedo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%2C%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20405px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223358i039A5568264A89CA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20add%20custom%20column%20with%20code%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22BaseUsuario%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20AddCount%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%22Count%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20let%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20currentField%3D%5BUA%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20filterTable%3DTable.SelectRows(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20BaseIntegrantes%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20each%20(Record.Field(_%2C%20%22AC%22)%20%3D%20%22ATIVO%22)%20and%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(Record.Field(_%2C%20%22E%22)%20%3D%20currentField)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20countRows%20%3D%20Table.RowCount(filterTable)%0A%20%20%20%20%20%20%20%20%20%20%20%20in%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20countRows%0A%20%20%20%20)%0Ain%0A%20%20%20%20AddCount%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1735206%22%20slang%3D%22en-US%22%3ERe%3A%20POWER%20QUERY%20-%20NEW%20COLUMN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816486%22%20target%3D%22_blank%22%3E%40AlessandraMacedo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20-%20make%20reference%20on%20lookup%20table%2C%20filter%20it%20on%20constant%20(ATIVO)%2C%20add%20custom%20column%20with%20value%201%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20516px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223369i579B4A6AE1294CFA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EMerge%20first%20table%20with%20it%20on%20E%2C%20expand%20ID%20column%20only%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20528px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223372i3C375363F42771FC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20group%20on%20all%20columns%20but%20ID%20with%20SUM%20aggregation%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20518px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223374iB3C8B6E484B639F8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1735318%22%20slang%3D%22en-US%22%3ERe%3A%20POWER%20QUERY%20-%20NEW%20COLUMN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735318%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!!!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

4 Replies

@AlessandraMacedo 

As variant, for such sample

image.png

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
Best Response confirmed by AlessandraMacedo (New Contributor)
Solution

@AlessandraMacedo 

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

image.png

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

image.png

and group on all columns but ID with SUM aggregation

image.png

Thank you!!! @Sergei Baklan 

@AlessandraMacedo 

You are welcome