Power Query | Countif equivalent Fixed Reference

[ovetteabejuela]

Hi,

What is the correct syntax or a similar method to achieve a countif with a fixed reference at the first row

       =COUNTIF(M$1:$M3,M3)

As you can see this formula expands as the row progresses... but how do I do it in M?

[Anonymous · Accepted solution]

First add an index column. Next add a column that pulls the M column from the #"Added Index" step and use List.Select to pull all values matching M (of the current step). Use List.Count to count the number of rows. Now you can remove the INdex column. Query is below. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyEYmEi8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [M = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    AddCountIf = Table.AddColumn(#"Added Index", "CountIf", each List.Count(List.Select(List.FirstN(#"Added Index"[M],[Index]), (el) => el = [M]))),
    #"Removed Columns" = Table.RemoveColumns(AddCountIf,{"Index"})
in
    #"Removed Columns"