countrow in power query whitout help table in M language

[Hadi14]

I have a table that contains national codes. I want to add a column to this table without an auxiliary table that displays the number of occurrences of each national code.

[Omid_Motamedise]

Hi Hadi Jan,

TO solve this problem, right click on the code column and select group by by the below setting.

Then exapnd the all row column. below is the full solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSszJVNJRMlKK1YlWKkqtSgRyjMGcrPyMPLhMcWJRIoQTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, code = _t]),
    #"Grouped Rows" = Table.Group(Source, {"code"}, {{"Rows", each _, type table [name=nullable text, code=nullable text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"name"}, {"name"})
in
    #"Expanded Rows"

[dufoq3]

Hi @Hadi14, another solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSszJVNJRMlKK1YlWKkqtSgRyjMGcrPyMPLhMcWJRIoQTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, code = _t]),
    CodeCount = [ codes = List.Buffer(Source[code]),
    occur = List.Transform(List.Distinct(codes), (x)=> {x, List.Count(List.Select(codes, (y)=> y = x))}),
    tbl = Table.FromRows(occur, type table[code=Int64.Type, count=Int64.Type])
  ][tbl],
    Ad_Count = Table.Join(Source, "code", CodeCount, "code")
in
    Ad_Count

[Chewdata]

I am not really sure what your question is. But let's say you have the following data:

You can select both columns, right click and choose group by:

It then standard gives you a count of the rows, counting how often each combination of name and code.

result:

the code:

let
    Source = YOURDATA,
    #"Grouped Rows" = Table.Group(Source, {"name", "code"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

[Hadi14]

yes

name	code
ali	2
reza	3
john	2
sara	2

result:

name	code
ali	2	3
reza	3	1
john	2	3
sara	2	3

[p45cal · Accepted solution]

= Table.AddColumn(Source, "Count", each List.Count(List.Select(Source[code],(x)=>x=[code])))

To paste into Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSszJVNJRMlKK1YlWKkqtSgRyjMGcrPyMPLhMcWJRIoQTCwA=", BinaryEncoding.Base64), Compression.Deflate)),{"name","code"}),
AddCustom = Table.AddColumn(Source, "Count", each List.Count(List.Select(Source[code],(x)=>x=[code])))
in
AddCustom

[Chewdata]

Hey,

Can you provide a workable sample of your data (not a screenshot). Please do not share sensative data.