# Power Query If with countifs multiple conditions

Occasional Contributor

# 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],[Site],[@Site])>1,
IF(
COUNTIFS([Article],[@Article],[Site],[@Site],[SOH],"<>0")<>1,
IF([@UOM]<>"CS","Delete",""),
IF([@[SOH]]=0,"Delete","")
)
,"")``````

I've tried replicating it in Power Query but it's not working as expected (it's definitely wrong, I tried combining versions I found online).

Current Power Query formula that doesn't work:

``````if List.Count(List.FindText(#"Filtered Rows3"[Article],[Site]))>1 then
if List.Count(List.FindText(#"Filtered Rows3"[Article],[Site]) and [SOH]<>0)<>1 then
if [UOM]<>"CS" then "Delete" else "" else
if [SOH]=0 then "Delete" else ""
else ""``````

I'm adding a custom column and then using the formula to detect which rows I need to delete/filter out.

What the formula is suppose to do (in case the macro formula is a bit confusing), is the following:

• find duplicate rows where [Article] and [Site] are the same
• then choose [UOM] (CS or SW) where [SOH]>0
• If both [UOM] CS or SW have [SOH]=0 then use CS

Thanks in advance!

2 Replies

# Re: Power Query If with countifs multiple conditions

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"})
``````

# Re: Power Query If with countifs multiple conditions

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