cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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.

catalogresult
12
80003
21
31
12
80003
80003

 

 

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

 

 

thank you answer

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
Highlighted
Memorable Member
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
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"

Screenshot 2017-02-04 23.43.16.png

 

Sample file

 

Highlighted
Helper I
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
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

Highlighted
Super User IV
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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Memorable Member
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. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors