PowerQuery

[Syndicate_Admin]

Dear Sirs,

In Excel we have the countif formula, how do we use this formula in the BI table?

I would like to count the repeated values in the table ascending according to their location: eg:

Countif (A$1:A2; A2), where column "A" = "Name"

Number	Countif
Juan	1
Pedro	1
Ricardo	1
Juan	2
Ricardo	2

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFOK1YlWCkhNKcoHs4IykxOLUiBsuDRcMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    Partition = Table.Group(#"Added Index", {"Number"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Index1"}, {"Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Hope this helps.