Forum Discussion
countrow in power query whitout help table in M language
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.
= 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
6 Replies
- Chewdata
Responsive Resident
Hey,
Can you provide a workable sample of your data (not a screenshot). Please do not share sensative data. 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
Solution Supplier
= 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
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" - dufoq3
Community Champion
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 - Omid_Motamedise
Super User
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"
