Count if row value repeats. Count number of times value repeats in column

[Anonymous]

In PowerQuery I want to add a custom column that evaluates how often a value repeats. I want to do this in PowerQuery, not DAX.

For example, if the value in Column 1 repeats twice then the new custom column would show "2" in each row. The below screen shot is what I'm trying to create where Column A exists and Column B is the column that does the countif.

I realize I could do a grouping and count, but I need to maintain the detail. I also don't want to create another group table and then merge it back (which is what I'm currently doing creates other problems)

If I were to do this in excel the formula looks like this: =countif(A;A,A2)

[mahoneypat · Accepted solution]

One way to solve this is with List functions in the query editor.  You can add this formula to a custom column (you can put whole let ... in expression in a custom column too).  If not performant, you could try wrapping #'Changed Type"[Column1] with List.Buffer( ).

= let
thisrow = [Column1],
templist = #"Changed Type"[Column1]
in
List.Count(List.Select(templist, each _ = thisrow))

Please see this whole query for an example with your sample data.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVQdCOTs6YdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Repeats", each let
thisrow = [Column1],
templist = List.Buffer(#"Changed Type"[Column1])
in
List.Count(List.Select(templist, each _ = thisrow)))
in
#"Added Custom"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[HotChilli]

You can maintain the detail by doing two aggregations in the 'Group By' , the first one is the 'Count Rows', the second is  'All Rows'.

Expand the all column (and remove it if required)

[Anonymous]

 Thanks, can you give me a little more detail on how this would work? I did the group by  below

But then PowerBI gave me an error "There was an error deserializing the evaluation results. The operaiton might succeed on a retry. I've retried a few times but still get the error.

[HotChilli]

Well, I've never seen that one before.  Size of the dataset? Something else unusual about the data?