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],[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!

excel
Formulas and Functions
Power BI
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 18, 2021

    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