cancel
Showing results for
Did you mean:
Highlighted
Helper I

## How many times occurs the same number in a column?

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)?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: How many times occurs the same number in a column?

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 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

5 REPLIES 5
Highlighted
Memorable Member

## Re: How many times occurs the same number in a column?

Hi @skopcak,

Could I achieve it by DAX?  Create calculated column

`result = CALCULATE(COUNTA(Data[Catalog]),FILTER(ALL(Data),Data[Catalog]=EARLIER(Data[Catalog])))`
Highlighted
Memorable Member

## Re: How many times occurs the same number in a column?

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

Highlighted
Helper I

## Re: How many times occurs the same number in a column?

Hello,

my source data comes from

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

You can advise how to change the source

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]),

Highlighted
Super User IV

## Re: How many times occurs the same number in a column?

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 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Highlighted
Memorable Member

## Re: How many times occurs the same number in a column?

Hi @skopcak,

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021