How many times occurs the same number in a column?

[skopcak]

Hello, I have a numeric table, and I need to determine how many times the value occurs in the table and list it on the New Column

Ex.

catalog	result
1	2
8000	3
2	1
3	1
1	2
8000	3
8000	3

can you please advise what the formula in Power Query Editor (M)?

thank you answer

[tringuyenminh92]

 Hi @skopcak,

If you still need creating couting column by power query (M), please check my sample:

• Create clone of your Catalog table as buffer

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglCwMDAyDHCMwxArKMwSxjIMsEzAIpNUVWaobMMVeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Catalog = _t, No = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Catalog", type text}, {"No", Int64.Type}}),
    #"TableBuffer" = Table.Buffer(#"Changed Type")
in
    #"TableBuffer"

• Create CountItem function

(item) =>
let
    #"ListCatalog" = Table.ToList(Table.SelectColumns(BufferOfData,"Catalog")), 
    result = List.Count(List.Select(#"ListCatalog", each _ = item))
in
    result

• Invoke custom function for your Catalog table (could do this by UI)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglCwMDAyDHCMwxArKMwSxjIMsEzAIpNUVWaobMMVeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Catalog = _t, No = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Catalog", type text}, {"No", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Result", each CountItem([Catalog]))
in
    
    #"Invoked Custom Function"

Sample file

[skopcak]

Hello,

my source data comes from

let
Source = Excel.CurrentWorkbook(){[Name="Tabulka1"]}[Content]

You can advise how to change the source
your code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglCwMDAyDHCMwxArKMwSxjIMsEzAIpNUVWaobMMVeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Catalog = _t, No = _t]),

thank you answer

[ImkeF · Accepted solution]

Hi there,

you can use "Earlier" in M as well - this enables you to consolidate @tringuyenminh92 's steps into one line of code like this:

result = Table.AddColumn(Source, "Custom", (Earlier) => List.Count(List.Select(Source[catalog], each _=Earlier[catalog])))

Where "Source" is the reference to your table.

Technical details, if of interest:

It does very much the same like the DAX-function:

Source  refers to the whole table like All(Data)

[catalog], each _ introduces the column "catalog" as the object to work on like Data), Data[Catalog]

=Earlier[catalog] reads almost the same like in DAX: =EARLIER(Data[Catalog])

Just that in M, you can replace the string "Earlier" by anything you want. It doesn't have a meaning, but just makes sure that the inner-context (which you adress by using the underscore "_") has a different adress than the outer one 🙂

[tringuyenminh92]

Hi @skopcak,

As @ImkeF's suggestion, it's clean and in short now, you could refer it for your case. 

[tringuyenminh92]

Hi @skopcak,

Could I achieve it by DAX?  Create calculated column

result = CALCULATE(COUNTA(Data[Catalog]),FILTER(ALL(Data),Data[Catalog]=EARLIER(Data[Catalog])))