Convert Excel formula to Power Query (if countifs)

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?

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

Quick cheat way - use Group By with Max...

And then expand "Rows".

Or you can do this:

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  grouped = Table.Group(Source, {"Category"}, {{"Grouped", each _, type nullable table[ID = any, Used = any]}}),
  countUsed = Table.AddColumn(grouped, "Count", each List.Count(List.Select([Grouped][Used], each _="Y"))),
  IsUsed = Table.AddColumn(countUsed, "Category Used", each if [Count] > 0 then "Y" else "N"),
  expanded = Table.ExpandTableColumn(IsUsed, "Grouped", {"ID", "Used"}, {"ID", "Used"})

in
  expanded