Power Query If with countifs multiple conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2463272%22%20slang%3D%22en-US%22%3EPower%20Query%20If%20with%20countifs%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2463272%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20used%20in%20Excel%20VBA%20macro%20that%20works%2C%20that%20I%20would%20like%20to%20convert%20to%20use%20in%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20in%20the%20macro%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(%0A%20%20%20%20COUNTIFS(%5BArticle%5D%2C%5B%40Article%5D%2C%5BSite%5D%2C%5B%40Site%5D)%26gt%3B1%2C%0A%20%20%20%20IF(%0A%20%20%20%20%20%20%20%20COUNTIFS(%5BArticle%5D%2C%5B%40Article%5D%2C%5BSite%5D%2C%5B%40Site%5D%2C%5BSOH%5D%2C%22%26lt%3B%26gt%3B0%22)%26lt%3B%26gt%3B1%2C%0A%20%20%20%20%20%20%20%20IF(%5B%40UOM%5D%26lt%3B%26gt%3B%22CS%22%2C%22Delete%22%2C%22%22)%2C%0A%20%20%20%20%20%20%20%20IF(%5B%40%5BSOH%5D%5D%3D0%2C%22Delete%22%2C%22%22)%0A%20%20%20%20%20%20%20)%0A%20%20%20%20%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20replicating%20it%20in%20Power%20Query%20but%20it's%20not%20working%20as%20expected%20(it's%20definitely%20wrong%2C%20I%20tried%20combining%20versions%20I%20found%20online).%3C%2FP%3E%3CP%3ECurrent%20Power%20Query%20formula%20that%20doesn't%20work%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Eif%20List.Count(List.FindText(%23%22Filtered%20Rows3%22%5BArticle%5D%2C%5BSite%5D))%26gt%3B1%20then%20%0Aif%20List.Count(List.FindText(%23%22Filtered%20Rows3%22%5BArticle%5D%2C%5BSite%5D)%20and%20%5BSOH%5D%26lt%3B%26gt%3B0)%26lt%3B%26gt%3B1%20then%0Aif%20%5BUOM%5D%26lt%3B%26gt%3B%22CS%22%20then%20%22Delete%22%20else%20%22%22%20else%20%0Aif%20%5BSOH%5D%3D0%20then%20%22Delete%22%20else%20%22%22%0Aelse%20%22%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20adding%20a%20custom%20column%20and%20then%20using%20the%20formula%20to%20detect%20which%20rows%20I%20need%20to%20delete%2Ffilter%20out.%3C%2FP%3E%3CP%3EWhat%20the%20formula%20is%20suppose%20to%20do%20(in%20case%20the%20macro%20formula%20is%20a%20bit%20confusing)%2C%20is%20the%20following%3A%3C%2FP%3E%3CUL%3E%3CLI%3Efind%20duplicate%20rows%20where%20%5BArticle%5D%20and%20%5BSite%5D%20are%20the%20same%3C%2FLI%3E%3CLI%3Ethen%20choose%20%5BUOM%5D%20(CS%20or%20SW)%20where%20%5BSOH%5D%26gt%3B0%3C%2FLI%3E%3CLI%3EIf%20both%20%5BUOM%5D%20CS%20or%20SW%20have%20%5BSOH%5D%3D0%20then%20use%20CS%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2463272%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464572%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20If%20with%20countifs%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F42015%22%20target%3D%22_blank%22%3E%40Simone%20Fick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20I%20understood%20the%20logic%2C%20perhaps%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%20%20%20%20columnNames%20%3D%20%7B%22Article%22%2C%20%22Site%22%20%7D%2C%0A%20%20%20%20addCount%20%3D%20Table.Group(prevStep%2C%20columnNames%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Count%22%2C%20Table.RowCount%7D%7D)%2C%0A%20%20%20%20countValues%20%3D%20Table.Join(%0A%20%20%20%20%20%20%20%20Source%2C%20columnNames%2C%0A%20%20%20%20%20%20%20%20addCount%2C%20columnNames%2C%0A%20%20%20%20%20%20%20%20JoinKind.Inner)%2C%0A%20%20%20%20addIsDelete%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20countValues%2C%0A%20%20%20%20%20%20%20%20%22isDelete%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20(if%20%5BSOH%5D%20%26lt%3B%26gt%3B%200%20then%201%20else%200)*%5BCount%5D%20%26lt%3B%26gt%3B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20%5BUOM%5D%20%26lt%3B%26gt%3B%20%22CS%22%20then%20%22Delete%22%20else%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20%5BSOH%5D%20%3D%200%20then%20%22Delete%22%20else%20null%0A%20%20%20%20%2C%20type%20text)%2C%0A%20%20%20%20removeCount%20%3D%20Table.RemoveColumns(addIsDelete%2C%7B%22Count%22%7D)%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2519252%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20If%20with%20countifs%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2519252%22%20slang%3D%22en-US%22%3E%3CP%3EHi%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%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20reply%2C%20sorry%20I%20took%20so%20long%20to%20respond.%3C%2FP%3E%3CP%3EIt%20might%20just%20work%2C%20but%20I'm%20currently%20getting%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20creates%20a%20list%20on%20the%20previous%20step%3A%3C%2FP%3E%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%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293754i9D9033A4CEC4D449%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20then%20I%20get%20an%20error%20on%20code%20line%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EcountValues%20%3D%20Table.Join(%0A%20%20%20%20%20%20%20%20Source%2C%20columnNames%2C%0A%20%20%20%20%20%20%20%20addCount%2C%20columnNames%2C%0A%20%20%20%20%20%20%20%20JoinKind.Inner)%2C%3C%2FCODE%3E%3C%2FPRE%3E%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%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293756iB71C1C443E94B786%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20some%20other%20columns%20that%20I%20will%20have%20to%20keep%20as%20well%20(but%20that%20can%20be%20sorted%20once%20this%20count%2Fdelete%20works).%3C%2FP%3E%3CP%3EIt%20might%20be%20something%20obvious%20I'm%20missing%2C%20any%20suggestions%20what%20to%20change%20on%20the%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ESimone%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

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

Hi @Sergei Baklan,

 

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:

image.png

 

and then I get an error on code line:

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

image.png

 

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