Forum Discussion

Simone Fick's avatar
Simone Fick
Copper Contributor
Jun 18, 2021

Power Query If with countifs multiple conditions

Hi everyone,   I have a formula used in Excel VBA macro that works, that I would like to convert to use in Power Query.   This is the formula in the macro: =IF( COUNTIFS([Article],[@Article...
excel
Formulas and Functions
Power BI
SergeiBaklan's avatar
SergeiBaklan
MVP

Simone Fick 

Not sure I understood the logic, perhaps something like

    columnNames = {"Article", "Site" },
    addCount = Table.Group(prevStep, columnNames,
        {{"Count", Table.RowCount}}),
    countValues = Table.Join(
        Source, columnNames,
        addCount, columnNames,
        JoinKind.Inner),
    addIsDelete = Table.AddColumn(
        countValues,
        "isDelete",
        each
            if (if [SOH] <> 0 then 1 else 0)*[Count] <> 1
            then
                if [UOM] <> "CS" then "Delete" else null
            else
                if [SOH] = 0 then "Delete" else null
    , type text),
    removeCount = Table.RemoveColumns(addIsDelete,{"Count"})
Simone Fick's avatar
Simone Fick
Copper Contributor
Jul 06, 2021

Hi SergeiBaklan,

 

Thank you for the reply, sorry I took so long to respond.

It might just work, but I'm currently getting an error.

 

It creates a list on the previous step:

 

and then I get an error on code line:

countValues = Table.Join(
        Source, columnNames,
        addCount, columnNames,
        JoinKind.Inner),

 

There are some other columns that I will have to keep as well (but that can be sorted once this count/delete works).

It might be something obvious I'm missing, any suggestions what to change on the code?

 

Thanks

Simone

 

Resources