Power Query to total duplicated fields

[Leebc]

Hi,

I am using the following Excel calculation to count/total the number of times a membership number is repeated in a column of membership numbers and put total in an adjacent column/row. I'd like to apply a similar calculation as a Power Query.

Countif ($A1:$Axxx, $A1).

I want to be able to select a membership number and identify how many times it occurs in the list of membership numbers.

example:

Member number: CountMember (output)
123 4
444 2
555 2
123 4
123 4
321 1
123 4
444 2
555 1
222 1

I directly access a list from the DB via a query. I want to apply this calculation and then append or merge with another query.

I hope I have explained it clearly enough?

 

[MarcelBeug]

You can use "Group By" as illustrated in this video resulting in the following query code:
(never mind the first step in the code: this is the result of pasting the data into a new table)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorViVYyMTEB06ampmAaJg6jjY0MUfjo6o2MjJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Member number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Member number", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Member number"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Index"}, {"Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AllData",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

[Leebc]

Thanks Marcel.
Works perfectly.

much appreciated.

Colin.