countif([columnA],[@columnB]) in DAX or M?

[alex1alex]

Title says most of it...
I'd like the DAX or M equivalent of countif([columnA],[@columnB])
(where I'm getting a count of how many times the cell in columnB appears in columnA)

(As an aside, In the end, what I'm really looking to do is filter out any rows where @ColumnB isn't in ColumnA. I'm using the PATH function and it doesn't like for you to include rows where the parent_columnName isn't in ID_columnName)

Thanks,
Alex

 

[miguel.escobar]

Using that same table that ImkeF created (just the tall one), follow the steps found in the following code:

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}, {"ColB", type text}, {"ColC", type text}, {"ColD", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Count( List.Select(#"Changed Type"[ColB], (x) => x =[ColA])))
in
    #"Added Custom"

 

[billszysz]

Hi Miguel
I love the beauty of construction in #"Added Custom" step !!!
But for a larger number of rows, I think better will be the code below.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}, {"ColB", type text}, {"ColC", type text}, {"ColD", type text}}),
    TblColACount = Table.Group(#"Changed Type", {"ColA"}, {{"How many", each Table.RowCount(_), type number}}),
    MerTbls = Table.NestedJoin(#"Changed Type",{"ColB"}, TblColACount, {"ColA"},"New"),
    ExpCol = Table.ExpandTableColumn(MerTbls, "New", {"How many"}, {"How many"})
in
    ExpCol

Regards

 

[miguel.escobar]

hey Bill,

My previous code is not by any means the desired way to do things in Power Query, but its the direct equivalent of adding a COUNTIF column to a table which is what I think the OP requested. I do agree with you that using Table.Group is the way to go as it usually does work with Query folding and there's probably even room for improvement.

Cheers!

 

[alex1alex]

Awesome stuff. I appreciate you all building off of each other's replies as it lets me work through them all
In the end I ended up with the last reply from Bill. It's interesting how you broke up the problem. I was stuck on counting items in column B based on column A...and you started with column A counting and only then associated it to column B.

(To continue on the aside..instead of doing the above recursively, I realized that I could just leave the hanging leafs in the data and clean them up by making them point to themselves since PATH can deal with that.)

Ta!
Alex