Customized column containing sum of similar columns

Given a table in PowerQuery like this

|A |B |C |D |
|  |b1|x |d1|
|a |b1|y |d2|
|b |b2|  |d3|
|  |b2|x |d4|
|c |b3|z |d5|
|  |b1|x |d4|

I would like to create a new column NEW that

• checks if column A is empty: if so return ‘none’
• if not count all the other rows with same column B entry AND entry ‘x’ in column C
• return result

The new table should look like this:

|A |B |C |D |NEW |
|  |b1|x |d1|none|
|a |b1|y |d2|2   |
|b |b2|  |d3|1   |
|  |b2|x |d4|none|
|c |b3|z |d5|0   |
|  |b1|x |d4|none|

>Solution :

Here you go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlBQ0lFKMgQSFSBWiqFSrE60UiJctBIsagQWTQKLGgEJsK4UY7CoAlwUYoIJWDQZLGoMJKrAoqZIag2R1cYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"A", Text.Trim, type text}, {"B", Text.Trim, type text}, {"C", Text.Trim, type text}, {"D", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", (x)=> if x[A] =  "" then "none" else let a = Table.RowCount( Table.SelectRows(#"Trimmed Text", (y)=> y[C] = "x" and y[B] = x[B])) in a)
in
    #"Added Custom"