Convert Excel formula to Power Query (if countifs)

Question

I have an excel formula in column D below using this formula.

=IF(COUNTIFS($B:$B,$B2,$C:$C,"Y")>0,"Y","N")

How can I do the same thing in power query? I understand that using group by does the job on countifs but how can I bring it all together as a new column in Power query?

Answer 1

Assuming your data is in a table called Table1 you could do this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    grouped = 
    let
        // unnecessary duplication Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"Category", "Used?"}, {{"Count", each _, type table [ID=number, Category=text, #"Used?"=text, Category used=text, Column1=number]}}),
        #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([#"Used?"] = "Y")),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Category Used", each "Y"),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "Used?"})
    in
        #"Removed Columns",
    joined = Table.NestedJoin(Source, {"Category"}, #"grouped", {"Category"}, "grouped", JoinKind.LeftOuter),
    #"Expanded grouped" = Table.ExpandTableColumn(joined, "grouped", {"Category Used"}, {"Category Used"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded grouped",null,"N",Replacer.ReplaceValue,{"Category Used"})

in
    #"Replaced Value"

Source is the original table grouped is that table grouped by Used? and then filtered for just the 'Y' After that I did a left outer join between Source and grouped which returns either 'Y' or null. Then just replace the null with 'N'

Alternatively you could make grouped a function and add a column that returns that value